Stored Proc, Output new id if null

  • Hi All,

    I have written a Stored Procedure below with a simple INSERT IF NULL and SET. I want to OUTPUT the inserted NewID() if the Sales ID was null.

    1. Is it possible to have the @SaleID UNIQUEIDENTIFIER = NULL as a OUTPUT as well? I tried this and couldn't get the EXEC to accept it as a input & output parameter

    2. So I created new parameter ,@NewSaleID UNIQUEIDENTIFIER and have set it to the new record TOP 1. Though the EXEC doesn't like the CAST function, it says error converting data type

    ALTER PROCEDURE AddUpdateSale

    @SaleID UNIQUEIDENTIFIER = NULL

    ,@EmployeeID INT

    ,@ProductID INT

    ,@Quantity SMALLINT

    ,@NewSaleID UNIQUEIDENTIFIER OUTPUT

    AS

    IF @SaleID IS NULL

    BEGIN

    INSERT INTO SALES

    SELECT NEWID(), @ProductID, @EmployeeID, @Quantity, GETDATE()

    SET @NewSaleID = (SELECT TOP 1 SALESID FROM Sales)

    END

    ELSE

    UPDATE

    SALES

    SET

    EmployeeID = @EmployeeID

    ,ProductID = @ProductID

    ,Quantity = @Quantity

    ,SaleDate = GETDATE()

    WHERE

    SaleDate = GETDATE()

    GO

    DECLARE @newSaleID UNIQUEIDENTIFIER

    EXECUTE AddUpdateSale '',1, 1, 1, @newSaleId OUTPUT

    PRINT 'SaleID: ' + CAST(@newSaleID as nvarchar(50))

    GO

    CREATE TABLE [dbo].[Sales](

    [SalesID] [uniqueidentifier] NOT NULL DEFAULT (newid()),

    [ProductID] [int] NOT NULL,

    [EmployeeID] [int] NOT NULL,

    [Quantity] [smallint] NOT NULL,

    [SaleDate] [datetime] NULL CONSTRAINT [DF_SaleDate] DEFAULT (getdate()),

    CONSTRAINT [PK_SaleID] PRIMARY KEY CLUSTERED

    (

    [SalesID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

  • First, there must be a mistake in WHERE clause of UPDATE statement.

    See if my correction is OK.

    Second,

    SELECT TOP 1 with no ORDER BY will return random results.

    Don't use it ever.

    As for the output - change the order of actions: set NewSalesID first, and then insert it to the table.

    ALTER PROCEDURE AddUpdateSale

    @SaleID UNIQUEIDENTIFIER = NULL

    ,@EmployeeID INT

    ,@ProductID INT

    ,@Quantity SMALLINT

    ,@NewSaleID UNIQUEIDENTIFIER OUTPUT

    AS

    IF @SaleID IS NULL

    BEGIN

    SET @NewSaleID = NEWID()

    INSERT INTO SALES

    SELECT @NewSaleID, @ProductID, @EmployeeID, @Quantity, GETDATE()

    END

    ELSE

    UPDATE

    SALES

    SET

    EmployeeID = @EmployeeID

    ,ProductID = @ProductID

    ,Quantity = @Quantity

    ,SaleDate = GETDATE()

    WHERE

    SalesID = @SaleID

    GO

    You actually do not need a separate parameter for @NewSaleID.

    Make @SaleID an OUTPUT parameter - it will do the trick.

    _____________
    Code for TallyGenerator

  • The call for @SaleID as OUTPUT parameter (consider @newSaleId removed):

    DECLARE @SaleID UNIQUEIDENTIFIER

    EXECUTE AddUpdateSale @SaleId OUTPUT,1, 1, 1

    PRINT 'SaleID: ' + CAST(@SaleID as nvarchar(50))

    GO

    _____________
    Code for TallyGenerator

  • Thanks a lot for the replies. Not sure why it didn't work the first time when I had @SaleID as the output.

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

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