Professional Web Applications Themes

Stored Procedure - ASP.NET

EXEC master..xp_cmdshell 'cscript c:\path\file.vbs' EXEC master..xp_cmdshell 'c:\path\file.exe' "Kannan" <gk_i> wrote in message news:0bf201c346e9$666b05c0$a501280aphx.gbl... > Hi, > Is there a way to invoke a vb script or vb.exe from a > stored procedure. > > Kannan > >...

  1. #1

    Default Re: Stored Procedure

    EXEC master..xp_cmdshell 'cscript c:\path\file.vbs'
    EXEC master..xp_cmdshell 'c:\path\file.exe'


    "Kannan" <gk_i> wrote in message
    news:0bf201c346e9$666b05c0$a501280aphx.gbl...
    > Hi,
    > Is there a way to invoke a vb script or vb.exe from a
    > stored procedure.
    >
    > Kannan
    >
    >

    Aaron Bertrand - MVP Guest

  2. #2

    Default Re: Stored Procedure

    Thank a lot.




    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Kannan Govindarajan Guest

  3. #3

    Default Stored Procedure

    How do I implement a stored procedure to insert a new member in a database
    then return the primary key of that member back to the application to be use
    in another table?


    Leon Shaw Guest

  4. #4

    Default Re: Stored Procedure

    You could use this SQL code:

    SET NOCOUNT ON;
    BEGIN TRAN;
    INSERT...;
    SELECT identity AS newID;
    COMMIT TRAN;
    SET NOCOUNT OFF;

    /john

    "Leon Shaw" <vnalitymsn.com> wrote in message
    news:OavG7QZTDHA.1868TK2MSFTNGP11.phx.gbl...
    > How do I implement a stored procedure to insert a new member in a database
    > then return the primary key of that member back to the application to be
    use
    > in another table?
    >
    >

    John Knoop Guest

  5. #5

    Default Re: Stored Procedure

    Have your SQL Server query Select Identity after it does the insert.
    Then you can get the value back like this:
    newId = (int)MyCommand.ExecuteScalar();

    --
    I hope this helps,
    Steve C. Orr, MCSD
    [url]http://Steve.Orr.net[/url]


    "Leon Shaw" <vnalitymsn.com> wrote in message
    news:OavG7QZTDHA.1868TK2MSFTNGP11.phx.gbl...
    > How do I implement a stored procedure to insert a new member in a database
    > then return the primary key of that member back to the application to be
    use
    > in another table?
    >
    >

    Steve C. Orr, MCSD Guest

  6. #6

    Default Re: Stored Procedure

    Will This Work? And how do I get this MemberId in a variable in code and
    pass it to another table?
    CREATE PROCEDURE Add_Member
    (
    FirstName varchar(50)
    LastName varchar(50)
    etc varchar(50)
    )
    AS
    INSERT INTO Member
    (
    FirstName,
    LastName,
    etc
    )
    VALUES
    (
    FirstName
    LastName
    etc
    )
    SELECT MemberID = IDENTITY

    "Steve C. Orr, MCSD" <SteveOrr.net> wrote in message
    news:%23K%23vwhZTDHA.2280TK2MSFTNGP12.phx.gbl...
    > Have your SQL Server query Select Identity after it does the insert.
    > Then you can get the value back like this:
    > newId = (int)MyCommand.ExecuteScalar();
    >
    > --
    > I hope this helps,
    > Steve C. Orr, MCSD
    > [url]http://Steve.Orr.net[/url]
    >
    >
    > "Leon Shaw" <vnalitymsn.com> wrote in message
    > news:OavG7QZTDHA.1868TK2MSFTNGP11.phx.gbl...
    > > How do I implement a stored procedure to insert a new member in a
    database
    > > then return the primary key of that member back to the application to be
    > use
    > > in another table?
    > >
    > >
    >
    >

    Leon Shaw Guest

  7. #7

    Default Re: Stored Procedure

    No, don't select the identity value into a private stored procedure variable
    or you'll never be able to get to it.
    Instead the final line should look like this:
    SELECT IDENTITY

    Alternately you could define MemberID as an output parameter for your
    sproc. Then use ADO.NET parameter objects. After you execute the query,
    check that parameter and it should be filled with the identity value.
    Here's more info:
    [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlparameterclasstopic.asp[/url]
    [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vbcode/html/vbtskcodecallingparameterizedstoredprocedurevisual basic.asp[/url]

    --
    I hope this helps,
    Steve C. Orr, MCSD
    [url]http://Steve.Orr.net[/url]



    "Leon Shaw" <vnalitymsn.com> wrote in message
    news:etnX0pZTDHA.1664TK2MSFTNGP11.phx.gbl...
    > Will This Work? And how do I get this MemberId in a variable in code and
    > pass it to another table?
    > CREATE PROCEDURE Add_Member
    > (
    > FirstName varchar(50)
    > LastName varchar(50)
    > etc varchar(50)
    > )
    > AS
    > INSERT INTO Member
    > (
    > FirstName,
    > LastName,
    > etc
    > )
    > VALUES
    > (
    > FirstName
    > LastName
    > etc
    > )
    > SELECT MemberID = IDENTITY
    >
    > "Steve C. Orr, MCSD" <SteveOrr.net> wrote in message
    > news:%23K%23vwhZTDHA.2280TK2MSFTNGP12.phx.gbl...
    > > Have your SQL Server query Select Identity after it does the insert.
    > > Then you can get the value back like this:
    > > newId = (int)MyCommand.ExecuteScalar();
    > >
    > > --
    > > I hope this helps,
    > > Steve C. Orr, MCSD
    > > [url]http://Steve.Orr.net[/url]
    > >
    > >
    > > "Leon Shaw" <vnalitymsn.com> wrote in message
    > > news:OavG7QZTDHA.1868TK2MSFTNGP11.phx.gbl...
    > > > How do I implement a stored procedure to insert a new member in a
    > database
    > > > then return the primary key of that member back to the application to
    be
    > > use
    > > > in another table?
    > > >
    > > >
    > >
    > >
    >
    >

    Steve C. Orr, MCSD Guest

  8. #8

    Default Re: Stored Procedure

    So how do I get that identity column inside a variable to be pass to another
    table that reference that member?
    "Steve C. Orr, MCSD" <SteveOrr.net> wrote in message
    news:%23D6%23g3ZTDHA.2148TK2MSFTNGP12.phx.gbl...
    > No, don't select the identity value into a private stored procedure
    variable
    > or you'll never be able to get to it.
    > Instead the final line should look like this:
    > SELECT IDENTITY
    >
    > Alternately you could define MemberID as an output parameter for your
    > sproc. Then use ADO.NET parameter objects. After you execute the query,
    > check that parameter and it should be filled with the identity value.
    > Here's more info:
    >
    [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlparameterclasstopic.asp[/url]
    >
    [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vbcode/html/vbtskcodecallingparameterizedstoredprocedurevisual basic.asp[/url]
    >
    > --
    > I hope this helps,
    > Steve C. Orr, MCSD
    > [url]http://Steve.Orr.net[/url]
    >
    >
    >
    > "Leon Shaw" <vnalitymsn.com> wrote in message
    > news:etnX0pZTDHA.1664TK2MSFTNGP11.phx.gbl...
    > > Will This Work? And how do I get this MemberId in a variable in code and
    > > pass it to another table?
    > > CREATE PROCEDURE Add_Member
    > > (
    > > FirstName varchar(50)
    > > LastName varchar(50)
    > > etc varchar(50)
    > > )
    > > AS
    > > INSERT INTO Member
    > > (
    > > FirstName,
    > > LastName,
    > > etc
    > > )
    > > VALUES
    > > (
    > > FirstName
    > > LastName
    > > etc
    > > )
    > > SELECT MemberID = IDENTITY
    > >
    > > "Steve C. Orr, MCSD" <SteveOrr.net> wrote in message
    > > news:%23K%23vwhZTDHA.2280TK2MSFTNGP12.phx.gbl...
    > > > Have your SQL Server query Select Identity after it does the insert.
    > > > Then you can get the value back like this:
    > > > newId = (int)MyCommand.ExecuteScalar();
    > > >
    > > > --
    > > > I hope this helps,
    > > > Steve C. Orr, MCSD
    > > > [url]http://Steve.Orr.net[/url]
    > > >
    > > >
    > > > "Leon Shaw" <vnalitymsn.com> wrote in message
    > > > news:OavG7QZTDHA.1868TK2MSFTNGP11.phx.gbl...
    > > > > How do I implement a stored procedure to insert a new member in a
    > > database
    > > > > then return the primary key of that member back to the application
    to
    > be
    > > > use
    > > > > in another table?
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Leon Shaw Guest

  9. #9

    Default Re: Stored Procedure

    In the example I gave the identity of the record you just inserted will be
    in the newId variable. Then you can pass it wherever you want, perhaps
    putting it into a SQLParameter object to call some other query.

    --
    I hope this helps,
    Steve C. Orr, MCSD
    [url]http://Steve.Orr.net[/url]


    "Leon Shaw" <vnalitymsn.com> wrote in message
    news:eiC7fTaTDHA.2144TK2MSFTNGP11.phx.gbl...
    > So how do I get that identity column inside a variable to be pass to
    another
    > table that reference that member?
    > "Steve C. Orr, MCSD" <SteveOrr.net> wrote in message
    > news:%23D6%23g3ZTDHA.2148TK2MSFTNGP12.phx.gbl...
    > > No, don't select the identity value into a private stored procedure
    > variable
    > > or you'll never be able to get to it.
    > > Instead the final line should look like this:
    > > SELECT IDENTITY
    > >
    > > Alternately you could define MemberID as an output parameter for your
    > > sproc. Then use ADO.NET parameter objects. After you execute the
    query,
    > > check that parameter and it should be filled with the identity value.
    > > Here's more info:
    > >
    >
    [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlparameterclasstopic.asp[/url]
    > >
    >
    [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vbcode/html/vbtskcodecallingparameterizedstoredprocedurevisual basic.asp[/url]
    > >
    > > --
    > > I hope this helps,
    > > Steve C. Orr, MCSD
    > > [url]http://Steve.Orr.net[/url]
    > >
    > >
    > >
    > > "Leon Shaw" <vnalitymsn.com> wrote in message
    > > news:etnX0pZTDHA.1664TK2MSFTNGP11.phx.gbl...
    > > > Will This Work? And how do I get this MemberId in a variable in code
    and
    > > > pass it to another table?
    > > > CREATE PROCEDURE Add_Member
    > > > (
    > > > FirstName varchar(50)
    > > > LastName varchar(50)
    > > > etc varchar(50)
    > > > )
    > > > AS
    > > > INSERT INTO Member
    > > > (
    > > > FirstName,
    > > > LastName,
    > > > etc
    > > > )
    > > > VALUES
    > > > (
    > > > FirstName
    > > > LastName
    > > > etc
    > > > )
    > > > SELECT MemberID = IDENTITY
    > > >
    > > > "Steve C. Orr, MCSD" <SteveOrr.net> wrote in message
    > > > news:%23K%23vwhZTDHA.2280TK2MSFTNGP12.phx.gbl...
    > > > > Have your SQL Server query Select Identity after it does the
    insert.
    > > > > Then you can get the value back like this:
    > > > > newId = (int)MyCommand.ExecuteScalar();
    > > > >
    > > > > --
    > > > > I hope this helps,
    > > > > Steve C. Orr, MCSD
    > > > > [url]http://Steve.Orr.net[/url]
    > > > >
    > > > >
    > > > > "Leon Shaw" <vnalitymsn.com> wrote in message
    > > > > news:OavG7QZTDHA.1868TK2MSFTNGP11.phx.gbl...
    > > > > > How do I implement a stored procedure to insert a new member in a
    > > > database
    > > > > > then return the primary key of that member back to the application
    > to
    > > be
    > > > > use
    > > > > > in another table?
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Steve C. Orr, MCSD Guest

  10. #10

    Default stored procedure

    Hi everyone,

    I am a complete noob with stored procedures. I was asked to modify the
    following stored procedure so that it includes the following
    conditions:

    IF InvWarehouse.DateLastSale is null then use
    InvWarehouse.DateLastStockMove

    else

    IF InvWarehouse.DateLastStockMove is null then use
    InvWarehouse.DateLastSale

    else

    IF InvWarehouse.DateLastStockMove is null and
    InvWarehouse.DateLastSale is null then need to compare to see which
    one is the most current and use that one.


    This logic needs to be incorporated into the following stored
    procedure:

    CREATE procedure dbo.sp_SlowMovingInventory_PopulateAdmFormData
    as
    Begin
    Declare StockCode Varchar(30),
    Count Integer

    Declare SlowMoving_Cursor Cursor for Select Distinct StockCode from
    InvWarehouse where
    Warehouse in ('M1','M2','M4','MS') AND
    QtyOnHand <> 0 AND
    (DateLastStockMove is Null or (datediff(dd,DateLastStockMove,
    getdate()) > 728) )


    Open SlowMoving_Cursor
    Fetch Next from SlowMoving_Cursor into StockCode
    While Fetch_Status = 0
    Begin

    Select Count = count(*) from AdmFormData where
    FormType = 'STK' and
    KeyField = StockCode and
    FieldName = 'DATE'
    If Count = 0
    Begin
    Insert into AdmFormData (FormType,KeyField,FieldName,DateValue)
    Values ('STK',StockCode,'DATE',getdate())
    End
    Else if Count = 1
    Begin
    Update AdmFormData Set DateValue = getdate() Where
    FormType = 'STK' and
    KeyField = StockCode and
    FieldName = 'DATE'
    End

    Fetch Next from SlowMoving_Cursor into StockCode
    End
    Close SlowMoving_Cursor
    Deallocate SlowMoving_Cursor
    End
    GO


    Any help will be much appreciated
    Andrei Guest

  11. #11

    Default Re: stored procedure

    [posted and mailed, please reply in news]

    Andrei (slesarka) writes:
    > I am a complete noob with stored procedures. I was asked to modify the
    > following stored procedure so that it includes the following
    > conditions:
    >
    > IF InvWarehouse.DateLastSale is null then use
    > InvWarehouse.DateLastStockMove
    >
    > else
    >
    > IF InvWarehouse.DateLastStockMove is null then use
    > InvWarehouse.DateLastSale
    >
    > else
    >
    > IF InvWarehouse.DateLastStockMove is null and
    > InvWarehouse.DateLastSale is null then need to compare to see which
    > one is the most current and use that one.
    (This was posted to comp.databases.ibm-db2, but I would expect your
    syntax work on DB2. This answer applies to MS SQL Server anyway.)

    If both are NULL, then you cannot use any of them. I suppose that you
    mean both are non-NULL?

    So replace:
    > (DateLastStockMove is Null or (datediff(dd,DateLastStockMove,
    > getdate()) > 728) )
    With:

    datediff(dd, CASE WHEN coalesce(DateLastStockMove, '19000101') >
    coalesce(DateLastSale, '19000101')
    THEN DateLastStockMove
    ELSE DateLastSale
    END, getdate()) > 728

    The coalesce() function returns the first non-NULL value in the list.
    > CREATE procedure dbo.sp_SlowMovingInventory_PopulateAdmFormData
    The sp_ prefix is reserved for system procedures, and SQL Server first
    looks for such a procedure in the master database, so you should use
    this for your own procedures.

    --
    Erland Sommarskog, SQL Server MVP, [email]sommaralgonet.se[/email]

    Books Online for SQL Server SP3 at
    [url]http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/url]
    Erland Sommarskog Guest

  12. #12

    Default stored procedure

    Hello,

    I'm french and I need some help.

    In a stored procedure, I want to create a database.
    The name of database is a parametre, but it don't work...

    ex :

    create procedure test(IN dbname varchar(20))
    begin
    create database dbname;
    end

    call test('toto');

    This stored procedure create a database named dbname but not toto, how I
    can do for create a database named toto (parametre).

    I hope you understand me...

    Thanks,

    Matt...
    matt Guest

  13. #13

    Default Re: stored procedure

    matt wrote:
    > Hello,
    >
    > I'm french and I need some help.
    >
    > In a stored procedure, I want to create a database.
    > The name of database is a parametre, but it don't work...
    >
    > ex :
    >
    > create procedure test(IN dbname varchar(20))
    > begin
    > create database dbname;
    > end
    >
    > call test('toto');
    >
    > This stored procedure create a database named dbname but not toto, how I
    > can do for create a database named toto (parametre).
    >
    > I hope you understand me...
    >
    > Thanks,
    >
    > Matt...

    Unfortunately, with today's implementation of stored procedures, you can't do that.
    The only way to create a database dynamically would be with a prepared statement,
    which however does not support the "CREATE DATABASE" statement yet. There is a plan
    from MySQL to implement in prepared statements all the instructions that are valid
    in stored procedures, but it has not been completed yet.

    Therefore, the only official way to do it dynamically is to use an external language.
    There is an unofficial way as well (see the abstract at
    [url]http://conferences.oreillynet.com/cs/mysqluc2006/view/e_sess/8015[/url] and the source code
    here: [url]http://sourceforge.net/projects/mysql-sr-lib/[/url]), but it's a undoented hack.

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|>< The Data Charmer
    _|
    [url]http://datacharmer.org/[/url]
    Giuseppe Maxia Guest

  14. #14

    Default Re: stored procedure

    Giuseppe Maxia a écrit :
    > matt wrote:
    >> Hello,
    >>
    >> I'm french and I need some help.
    >>
    >> In a stored procedure, I want to create a database.
    >> The name of database is a parametre, but it don't work...
    >>
    >> ex :
    >>
    >> create procedure test(IN dbname varchar(20))
    >> begin
    >> create database dbname;
    >> end
    >>
    >> call test('toto');
    >>
    >> This stored procedure create a database named dbname but not toto, how I
    >> can do for create a database named toto (parametre).
    >>
    >> I hope you understand me...
    >>
    >> Thanks,
    >>
    >> Matt...
    >
    >
    > Unfortunately, with today's implementation of stored procedures, you can't do that.
    > The only way to create a database dynamically would be with a prepared statement,
    > which however does not support the "CREATE DATABASE" statement yet. There is a plan
    > from MySQL to implement in prepared statements all the instructions that are valid
    > in stored procedures, but it has not been completed yet.
    >
    > Therefore, the only official way to do it dynamically is to use an external language.
    > There is an unofficial way as well (see the abstract at
    > [url]http://conferences.oreillynet.com/cs/mysqluc2006/view/e_sess/8015[/url] and the source code
    > here: [url]http://sourceforge.net/projects/mysql-sr-lib/[/url]), but it's a undoented hack.
    >
    > ciao
    > gmax
    >
    Hello,

    Ok, thanks for you answer.
    I do it, in c language :
    retour = system("mysql -e script.sql")
    and if retour != 0, it's ok for me.
    I don't know if it's the best way, but it work.

    Matt...
    matt Guest

Similar Threads

  1. stored procedure help
    By Maria in forum Dreamweaver AppDev
    Replies: 3
    Last Post: April 20th, 06:55 PM
  2. Using a stored procedure
    By MarkWright in forum Coldfusion Database Access
    Replies: 13
    Last Post: April 15th, 05:53 PM
  3. stored procedure value
    By -D- in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 28th, 07:48 PM
  4. Stored procedure?
    By SG via DotNetMonster.com in forum ASP.NET Web Services
    Replies: 0
    Last Post: February 23rd, 01:06 PM
  5. help with a stored procedure
    By Jason Tesser in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 3rd, 05:35 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139