SQL Server Performance

STORED PROCEDURE create new record and retrieve KeyFIELD data return to calling application

Discussion in 'Getting Started' started by Lennie, Mar 1, 2010.

  1. Lennie New Member

    Hi Good Guys, [:D]
    VBNET2008, SQL SERVER 2000., NORTHWIND DATABASE, ORDERS TABLE,
    Within the same STORED PROCEDURE after INSERT statement to create a new record I don't know how to retrieve the KeyField, ORDERID data from the ORDERS table to return it to the calling VB.NET 2008 application.
    Here is the column properties of the SQL SERVER Northwind Table ORDERS
    ColumnName Data Type Length
    OrderID Int 4

    Column Properties
    Identity Yes
    Indentity Seed 1
    Identity Increment 1
    Please Help me, I need your help.
    Please share with me the sample STORED PROCEDURE coding to create and retrieve the keyfield data and return it to the calling application.
    Lennie, Mar 1, 2010
    #2

  2. FrankKalis Moderator

    Welcome to the forum!
    You could retrieve the last generated IDENTITY value via SCOPE_IDENTITY and return this via an OUTPUT parameter to the calling application.
    FrankKalis, Mar 1, 2010
    #3

  3. Lennie New Member

    Hi Frank,
    Thank you for the information but I am new to using STORED PROCEDURE. Regarding your information can you please share with me sample STORED PROCEDURE CODING .
    I desperately need your help .
    Lennie, Mar 2, 2010
    #4

  4. FrankKalis Moderator

    Here you go...
    ALTER PROCEDURE dbo.your_procedure
    ....
    @OrderID int = NULL OUTPUT
    AS

    ...do stuff....
    INSERT INTO dbo.yourtable
    VALUES...

    SET @OrderID = SCOPE_IDENTITY();
    ...probably do more stuff

    GO
    FrankKalis, Mar 2, 2010
    #5

  5. FrankKalis Moderator

    ...and you would call this procedure like
    EXEC dbo.yourprocedure .... @OrderID OUTPUT
    After executing the call the value for the last inserted IDENTITY is in @OrderID. However, I can't tell you how the .Net code looks like, but I'm sure someone else here can.
    FrankKalis, Mar 2, 2010
    #6

  6. Adriaan New Member

    If the insert can cover more than one record, you might consider expanding your INSERT query with an OUTPUT clause.
    Adriaan, Mar 2, 2010
    #7

  7. FrankKalis Moderator

    Good idea, but Lennie says that he is still on SQL Server 2000.
    FrankKalis, Mar 2, 2010
    #8

  8. Adriaan New Member

    Good catch, Frank!
    Adriaan, Mar 2, 2010
    #9

  9. Lennie New Member

    Hi Good Guys, [:p]
    Thanks for your generous share of information to help me. Finally my application and SQL SERVER 2000 Stored Procedure is working now. For testing purposes I am using NORTHWIND database.
    I would like to share the VB.NE 2008 coding and STORED PROCEDURE Scripts to Newbies who may be having the same problems.
    Here are the coding
    VBNET2008 CODING
    Private Sub FUpdateCreateRecTestOrders()
    'either update or create new record depends on Radiobutton Edit / New
    sqlconn = New SqlConnection(connstr)
    sqlcmd = New SqlCommand
    sqlcmd.Connection = sqlconn

    With sqlcmd
    .CommandText = "SPMaintTestOrdersTbl"
    .CommandType = CommandType.StoredProcedure
    End With

    Try
    'create parameter
    With sqlcmd

    ' --- Input parameter direction ---
    .Parameters.Add("@OrderId", SqlDbType.Int, 4).Direction = ParameterDirection.Input
    .Parameters.Add("@OrderDate", SqlDbType.DateTime, 8).Direction = ParameterDirection.Input
    .Parameters.Add("@NewOrderID", SqlDbType.Int, 4).Direction = ParameterDirection.Output
    '--- parameter value ---
    .Parameters("@OrderID").Value = Convert.ToInt32(intPOrdId)
    .Parameters("@OrderDate").Value = IIf(Me.txtDteOrder.TextLength = 0, "", DateTime.Parse(Me.txtDteOrder.Text))
    .Parameters("@NewOrderID").Value =IIF (me.txtOrderId.text.length <> 0, me.txtOrderId.text )
    sqlconn.Open()
    .ExecuteNonQuery()
    if RadiobuttonNew then
    Me.txtOrderID.Text = Convert.ToInt32(sqlcmd.Parameters("@NewOrderId").Value)
    End if
    sqlconn.Close()
    MessageBox.Show("Order Update/ Created")
    End With
    Catch ex As Exception
    MessageBox.Show(ex.Message)
    End Try
    End Sub
    ----------------------------------------------------
    SQL SERVER 2000 STORED PROCEDURE
    CREATE PROCEDURE SPMaintTestOrdersTbl
    @OrderId int,
    @OrderDate datetime,
    @NewOrderId int Output
    AS
    Declare @count as int
    Select @count = count(*) from Orders where OrderId = @OrderId
    If @count > 0
    Update TestOrders
    SET OrderDate = @OrderDate
    Where OrderId = @OrderId
    Else
    Insert into TestOrders
    ( OrderDate )
    Values
    ( @OrderDate )

    set @NewOrderId = Scope_Identity()
    GO
    Cheers,
    Tee
    Lennie, Mar 2, 2010
    #10

  10. FrankKalis Moderator

    Thank you for sharing your code! This will surely help others as well.
    May I say that you don't need to use the @Count variable and your COUNT(*) statement at all?
    Because all you want to do is to check for the existence of an Order with @OrderID you can just as well use an IF EXISTS (SELECT 1 FROM Orders WHERE OrderID = @OrderID)....do stuff...
    In your case this is no real big deal, because you are searching on the primary key which can at most return 1 row as result. However in other cases such a COUNT(*) construct for checking existence can be a real performance killer.
    The reason for that is that COUNT(*) doesn't stop processing once it has found a matching row, while EXISTS() stops as soon as a matching row is found. this is especially true for SQL Server 2000. SQL Server 2005 and above handle things a bit smarter, but I'm tempted to say that you should always use EXISTS() to check for existence of data. EXISTS() is much cleaner and more "natural"as it would be similar to how you would express such a request in anatural language, IMHO
    Have a look here: http://sqlblog.com/blogs/andrew_kelly/archive/2007/12/15/exists-vs-count-the-battle-never-ends.aspx for a good discussion of this issue.
    FrankKalis, Mar 3, 2010
    #11

  11. SimbaM New Member

    Great Stuff Lennie

    I had been roving around on forums looking for something closer to what you have provided and am happy I got this one. I have a further question though. I am developing a simple module to process invoices for an application to be used by a small organisation. The application I am working on has an N-Tier design. How do I get to use the same approach i.e. using the code you supplied within a class in the DAL of the application and be able to have the Client submit the basic input data then views the generated number?

    I am using datasets, but I can work with a solution that provides results. Thanks
    SimbaM, Jun 11, 2011
    #11

Share This Page