Simple Update Query

  • Can someone please tell me what is wrong with this procedure. it keep ssaying i must declare @CustEDP.

    CREATE PROCEDURE InsertEcometryEDP

    (

    @NewOrderNo varchar(20),

    @CustEDP int,

    @customerid int,

    @orderid int

    )

    AS

    update FSOrders set

    EcometryOrderNo = @NewOrderNo

    where

    FSOrders.orderid = @orderid

    go

    update FSCustomers set

    CustEDP = @CustEDP

    where

    FSCustomers.customerid = @customerid

    go

    thanks


    </cm>

  • The GO "command" is actually a batch separator for your client program. When a program like QA sees it, it'll say, "That's the end of one batch." So if you've stuck it in a stored procedure definition, everything above the go will be part of the stored procedure, but anything below will not.

    It used to be when I went to the grocery store there would be this plastic "separator" to mark where one customer's groceries stopped and the next person's groceries started. If there was a guy ahead of me, as the cashier rung up the groceries and bagged them, there was room to start putting mine down. I'd grab that plastic separator and the cashier knew that the separator marked the boundary between my groceries and the other guy's. The GO statement is the same.

    So as far as SQL Server is concerned, the CREATE PROCEDURE is only this:

    
    
    CREATE PROCEDURE InsertEcometryEDP
    (
    @NewOrderNo varchar(20),
    @CustEDP int,
    @customerid int,
    @orderid int
    )
    AS
    update FSOrders set
    EcometryOrderNo = @NewOrderNo
    where
    FSOrders.orderid = @orderid

    If you do an sp_helptext on InsertEcometryEDP, you'll see that to be the case. Remove the go between the update statements and you should be fine.

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

  • thanks for the great reply.


    </cm>

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply