SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Stored Procedure Help

    I'm trying to make a stored procedure for MS SQL that inserts some information and then returns the unique ID of the row...but I can't work out how...

    Anyone got an example?


  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,204
    Mentioned
    106 Post(s)
    Tagged
    1 Thread(s)
    Doing a SELECT @@IDENTITY after the insert should give you what you want...
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  3. #3
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What would an example look like...?

    [dumbass]


  4. #4
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    CREATE PROCEDURE dbo.AddARow
    (
    	@var1 int,
    	@var2 varchar(50)
    )
    AS
    
    	INSERT INTO TableName (Field1, Field2) VALUES (@var1, @var2)
    	
    	SELECT @@IDENTITY AS ID
    -- JIM BOLLA
    Wanna play Halo 2? My XBOX Live gamertag: crowdozer

  5. #5
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This ok...?
    CREATE PROCEDURE AddNewMember
    (
    @userName char(20),
    @userPassword char(20),
    @eMailAddress char(30),
    @verified bit,
    @dateJoined int,
    @secretQuestion char(255),
    @secretAnswer char(255),
    @gNum int
    ) AS

    INSERT INTO tblMembers (userName, userPassword, eMailAddress, dateJoined, secretQuestion, secretAnswer, gNum) SELECT @username, @userPassword, @eMailAddress, @dateJoined, @secretQuestion, @secretAnswer, @gNum

    SELECT @@IDENTITY AS ID

    GO

  6. #6
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try the following D.

    Code:
    CREATE PROCEDURE usp_InsertCompany
    	@CompanyID	INT OUTPUT,
    	@CompanyName	VARCHAR(100)
    AS
    
    SET NOCOUNT ON
    
    -- Insert
    INSERT INTO Company(CompanyName)
    VALUES(@CompanyName)
    
    -- Get the value of last inserted record
    --(check to see if record was actullay inserted by last statement)
    IF @@ROWCOUNT > 1
    	SELECT @CompanyID = @@IDENTITY
    
    
    SET NOCOUNT OFF
    I tend to use a Set stored prodecure that will either INSERT or UPDATE a record as it saves writing/maintaing 2 SP's which have the same parameters.

    I just do a check to see if a record exists (using the primary key value) and update/insert it accordingly.

  7. #7
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I get an error

    Procedure 'AddNewMember' expects parameter '@memberID', which was not supplied.

  8. #8
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by shane
    I tend to use a Set stored prodecure that will either INSERT or UPDATE a record as it saves writing/maintaing 2 SP's which have the same parameters.

    I just do a check to see if a record exists (using the primary key value) and update/insert it accordingly.
    I've used this approach before also. It works quite well. Although instead of checking for existence of the ID, I check whether it is equal to zero to determine whether to insert or update.

    An optional enhancement would be to have it check a last updated field compared to the one passed into the stored procedure (that was orginally retrieved with that record) this would serve the purpose of protecting against overwriting someone else's update.
    -- JIM BOLLA
    Wanna play Halo 2? My XBOX Live gamertag: crowdozer

  9. #9
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, what am I doing wrong?!

    This is the SP

    CREATE PROCEDURE AddNewMember
    @memberID int output,
    @userName char(20),
    @userPassword char(20),
    @eMailAddress char(30),
    @verified bit,
    @dateJoined int,
    @secretQuestion char(255),
    @secretAnswer char(255),
    @gNum int
    AS

    SET NOCOUNT ON

    INSERT INTO tblMembers (userName, userPassword, eMailAddress, dateJoined, secretQuestion, secretAnswer, gNum) SELECT @username, @userPassword, @eMailAddress, @dateJoined, @secretQuestion, @secretAnswer, @gNum

    IF @@ROWCOUNT > 1
    SELECT @memberID = @@IDENTITY

    SET NOCOUNT OFF
    GO
    This is the code (not the whole sub, just a little of it)
    [vbs]
    Dim secretQuestionStr As String = secretQuestion.Text
    Dim secretAnswerStr As String = secretAnswer.Text
    Dim eMailAddressStr As String = eMailAddress.Text
    Dim dateJoinedInt As Integer = pf.gDate(now())
    Dim gNum As Integer = pf.gDate(now()) + second(now()) & hour(now())
    Dim verified As Boolean = False

    If ( application("verifyOnSignUp") ) Then
    verified = True
    End If

    objCmd = New SQLCommand("addNewMember", strConnect)

    objParam = objCmd.Parameters.Add("@userName", SQLDBType.Char)
    objParam.Direction = ParameterDirection.Input
    objParam.Value = pf.chkMess(userNameStr)

    objParam = objCmd.Parameters.Add("@userPassword ", SQLDBType.Char)
    objParam.Direction = ParameterDirection.Input
    objParam.Value = pf.chkMess(userPassStr)

    objParam = objCmd.Parameters.Add("@eMailAddress ", SQLDBType.Char)
    objParam.Direction = ParameterDirection.Input
    objParam.Value = pf.chkMess(eMailAddressStr)

    objParam = objCmd.Parameters.Add("@verified ", SQLDBType.Bit)
    objParam.Direction = ParameterDirection.Input
    objParam.Value = pf.chkMess(verified)

    objParam = objCmd.Parameters.Add("@dateJoined", SQLDBType.Char)
    objParam.Direction = ParameterDirection.Input
    objParam.Value = pf.chkMess(dateJoinedInt)

    objParam = objCmd.Parameters.Add("@secretQuestion", SQLDBType.Char)
    objParam.Direction = ParameterDirection.Input
    objParam.Value = pf.chkMess(secretQuestionStr)

    objParam = objCmd.Parameters.Add("@secretAnswer", SQLDBType.Char)
    objParam.Direction = ParameterDirection.Input
    objParam.Value = pf.chkMess(secretAnswerStr)

    objParam = objCmd.Parameters.Add("@gNum", SQLDBType.Int)
    objParam.Direction = ParameterDirection.Input
    objParam.Value = pf.chkMess(gNum)

    Dim memberID As Integer

    ' Try
    objCmd.Connection.Open()
    objCmd.CommandType = CommandType.StoredProcedure
    objReader = objCmd.ExecuteReader
    memberID = objReader.GetInt32(0).ToString()
    response.write(memberID)
    objCmd.Connection.Close()
    ' Catch ex As Exception
    response.write("Error adding new member")
    response.end
    ' End Try
    [/vbs]

    And this is the error:
    Procedure 'AddNewMember' expects parameter '@memberID', which was not supplied.
    Description:
    An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: Procedure 'AddNewMember' expects parameter '@memberID', which was not supplied.

  10. #10
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @memberid is defined as an output parameter and not a return value. you should add another parameter "@memberid" and set its direction to output. after executing the query, getting the value of that parameter should contain your desired id value
    -- JIM BOLLA
    Wanna play Halo 2? My XBOX Live gamertag: crowdozer

  11. #11
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Duh, that was kida obvious thanks



    ...but its not returning anything...

  12. #12
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I took out the "IF @@ROWCOUNT > 1" and it works:
    CREATE PROCEDURE AddNewMember
    @memberID int output,
    @userName char(20),
    @userPassword char(20),
    @eMailAddress char(30),
    @verified bit,
    @dateJoined int,
    @secretQuestion char(255),
    @secretAnswer char(255),
    @gNum int
    AS

    SET NOCOUNT ON

    INSERT INTO tblMembers (userName, userPassword, eMailAddress, dateJoined, secretQuestion, secretAnswer, gNum) SELECT @username, @userPassword, @eMailAddress, @dateJoined, @secretQuestion, @secretAnswer, @gNum

    SELECT @memberID = @@IDENTITY

    SET NOCOUNT OFF
    GO
    Won't cause any problems will it?

    (As you can tell, complete newbie with MS SQL Server)

  13. #13
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    looks ok. i'd reccommend using varchar instead of char but that's about it.
    -- JIM BOLLA
    Wanna play Halo 2? My XBOX Live gamertag: crowdozer

  14. #14
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by shane
    I tend to use a Set stored prodecure that will either INSERT or UPDATE a record as it saves writing/maintaing 2 SP's which have the same parameters.

    I just do a check to see if a record exists (using the primary key value) and update/insert it accordingly.
    Another alternative/modification to your method would be to have the procedure attempt the update statement and then afterwards check the rows affected value to see if its greater than zero, if not then do the insert. this should have almost identical performance to your method for inserts and slightly improved performance for updates.
    -- JIM BOLLA
    Wanna play Halo 2? My XBOX Live gamertag: crowdozer

  15. #15
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by crowdozer

    I've used this approach before also. It works quite well. Although instead of checking for existence of the ID, I check whether it is equal to zero to determine whether to insert or update.

    An optional enhancement would be to have it check a last updated field compared to the one passed into the stored procedure (that was orginally retrieved with that record) this would serve the purpose of protecting against overwriting someone else's update.
    I use a few different variants of the Set method, one of which is checking for the existence of the record, another being simliar to what you've suggested, which is to set the primary key parameter default to null, in which case I do an INSERT.


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
  •