September 20, 2016 at 9:55 pm
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
September 20, 2016 at 10:22 pm
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
September 20, 2016 at 10:26 pm
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
September 20, 2016 at 11:05 pm
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