Professional Web Applications Themes

help with sp please! - Microsoft SQL / MS SQL Server

copied in below is my sp (MSSQL 2000), I'm trying to return the two variables {newFeedbackID, newRequestID} as if they were part of a recordset to an ADO recordset object, which I access as follows: requestID = (rsFeedback.Fields.Item("newRequestID").Value) Am I doing the sp right?? I was hoping to avoid using OUTPUT params in the sp... CREATE PROCEDURE dbo.procInsertFeedback feedback NVARCHAR(2000), enterByID NVARCHAR(255) AS BEGIN SET NOCOUNT ON DECLARE newFBID INT, newRequestID INT INSERT tblFeedback ( feedback, enterByID, enteredDT ) VALUES ( feedback, enterByID, getdate() ) SELECT newFBID = SCOPE_IDENTITY() -- put new feedback ID into tblRequests, and return request ID ...

  1. #1

    Default help with sp please!

    copied in below is my sp (MSSQL 2000), I'm trying to return the two
    variables {newFeedbackID, newRequestID} as if they were part of a recordset
    to an ADO recordset object, which I access as follows:

    requestID = (rsFeedback.Fields.Item("newRequestID").Value)

    Am I doing the sp right?? I was hoping to avoid using OUTPUT params in the
    sp...

    CREATE PROCEDURE dbo.procInsertFeedback
    feedback NVARCHAR(2000),
    enterByID NVARCHAR(255)

    AS
    BEGIN
    SET NOCOUNT ON
    DECLARE newFBID INT,
    newRequestID INT
    INSERT tblFeedback
    (
    feedback,
    enterByID,
    enteredDT
    )
    VALUES
    (
    feedback,
    enterByID,
    getdate()
    )
    SELECT newFBID = SCOPE_IDENTITY()

    -- put new feedback ID into tblRequests, and return request ID
    EXEC newRequestID = dbo.procInsertRequest 'events', newFBID

    SELECT newFeedbackID = newFBID
    SELECT newRequestID = newRequestID
    END
    GO


    ben Guest

  2. #2

    Default Re: help with sp please!

    Hi Ben,

    With the code you have posted you are returning the two variables as two
    separate recordsets. If you want to return them as one recordset you can
    simply change the code to:

    SELECT newFBID AS newFeedbackID, newRequestID AS newRequestID.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "ben h" <intrisk.com> wrote in message
    news:#phx.gbl... 
    recordset 


    Jacco Guest

  3. #3

    Default Re: help with sp please!

    Use

    SELECT newFeedbackID = newFBID, newRequestID = newRequestID

    instead


    "ben h" <intrisk.com> wrote in message
    news:#phx.gbl... 
    recordset 


    Alex Guest

  4. #4

    Default Re: help with sp please!

    Thanks Jacco and Alex,

    one question remains: does this mean that earlier in the script when I use
    this:

    SELECT newFBID = SCOPE_IDENTITY()

    that this line is also creating a recordset?
    If so, how can I set my variable 'newFBID' and not return it as a
    recordset?

    Ben

    "Jacco Schalkwijk" <co.uk> wrote in message
    news:phx.gbl... 
    > recordset [/ref]
    the 
    >
    >[/ref]


    ben Guest

  5. #5

    Default Re: help with sp please!

    Hi Ben,

    SELECT newFBID = SCOPE_IDENTITY() assigns a value to a variable and does
    not return a resultset. You can use SET newFBID = SCOPE_IDENTITY() instead
    if you want to make things a bit clearer.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "ben h" <intrisk.com> wrote in message
    news:u22#phx.gbl... 
    > > recordset [/ref]
    > the 
    > >
    > >[/ref]
    >
    >[/ref]


    Jacco Guest

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