Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Sql Stored Proc, OUTPUT uniqueidentifer

Author  Topic 

richdiaz99
Starting Member

22 Posts

Posted - 2013-02-06 : 18:22:02
My code is below, the last two lines is where I am stuck :-/

The code so far: If no AccountNumber is found it does the insert fine and OUTPUTs the newly inserted uniqueidentifer PK.

But how do I get the StoredProc OUTPUT to return my (found) EXISTING [dbo].[AccountLog].[Pk] uniqueidentifer ?



ALTER PROCEDURE [dbo].[AccountLog_Insert]
@AccountNumber varchar(50)

AS

DECLARE @PkNewID uniqueidentifier
SET @PkNewID = NEWID()

SELECT PK, AccountNumber
FROM [dbo].[AccountLog]
WHERE AccountNumber = @AccountNumber

IF @@ROWCOUNT = 0
BEGIN
INSERT INTO [dbo].[AccountLog]
(
PK,
AccountNumber
)
OUTPUT @PkNewID
VALUES
(
@PkNewID,
@AccountNumber
)
END
--ELSE
--OUTPUT THE EXISTING PK uniqueidentifier

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-06 : 19:40:14
This?
ELSE
--OUTPUT THE EXISTING PK uniqueidentifier
SELECT PK
FROM [dbo].[AccountLog]
WHERE AccountNumber = @AccountNumber
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-06 : 22:52:58
[code]
ALTER PROCEDURE [dbo].[AccountLog_Insert]
@AccountNumber varchar(50)

AS

DECLARE @PkID uniqueidentifier

SELECT @PkID=PK
FROM [dbo].[AccountLog]
WHERE AccountNumber = @AccountNumber

IF @PkID IS NULL
BEGIN
INSERT INTO [dbo].[AccountLog]
(
PK,
AccountNumber
)
OUTPUT INSERTED.PK
VALUES
(
NEWID(),
@AccountNumber
)
END
ELSE
SELECT @PkID
[/code]



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

richdiaz99
Starting Member

22 Posts

Posted - 2013-02-07 : 09:37:11
quote:
Originally posted by James K

This?
ELSE
--OUTPUT THE EXISTING PK uniqueidentifier
SELECT PK
FROM [dbo].[AccountLog]
WHERE AccountNumber = @AccountNumber




Go to Top of Page

richdiaz99
Starting Member

22 Posts

Posted - 2013-02-07 : 10:44:02
So, you CAN NOT use OUTPUT on a SELECT statement.

So, I tried doing an UPDATE (that actually doesn't change any data) with an OUTPUT. This still does not OUTPUT the uniqueidentfier :-(


ALTER PROCEDURE [dbo].[AccountLog_Insert]
@AccountNumber varchar(50)

AS

DECLARE @PkNewID uniqueidentifier
SET @PkNewID = NEWID()

SELECT PK, AccountNumber
FROM [dbo].[AccountLog]
WHERE AccountNumber = @AccountNumber

IF @@ROWCOUNT = 0
BEGIN
INSERT INTO [dbo].[AccountLog]
(
PK,
AccountNumber
)
OUTPUT @PkNewID
VALUES
(
@PkNewID,
@AccountNumber
)
END
ELSE
--OUTPUT THE EXISTING PK uniqueidentifier

SET @PkCurrentID = (SELECT PK
FROM [dbo].[sAccountLog]
WHERE AccountNumber = @AccountNumber)
--NO DATA IS ACTUALLY UPDATING HERE, I'M JUST TRYING TO OUTPUT THE EXISTING PK column
UPDATE [dbo].[AccountLog]
SET
PK = @PkCurrentID,
AccountNumber = @AccountNumber
OUTPUT @PkCurrentID
WHERE AccountNumber = @AccountNumber





Go to Top of Page

richdiaz99
Starting Member

22 Posts

Posted - 2013-02-07 : 14:09:05
ELSE code above had a typo, fixed now.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-07 : 23:14:36
quote:
Originally posted by richdiaz99

So, you CAN NOT use OUTPUT on a SELECT statement.

So, I tried doing an UPDATE (that actually doesn't change any data) with an OUTPUT. This still does not OUTPUT the uniqueidentfier :-(


ALTER PROCEDURE [dbo].[AccountLog_Insert]
@AccountNumber varchar(50)

AS

DECLARE @PkNewID uniqueidentifier
SET @PkNewID = NEWID()

SELECT PK, AccountNumber
FROM [dbo].[AccountLog]
WHERE AccountNumber = @AccountNumber

IF @@ROWCOUNT = 0
BEGIN
INSERT INTO [dbo].[AccountLog]
(
PK,
AccountNumber
)
OUTPUT @PkNewID
VALUES
(
@PkNewID,
@AccountNumber
)
END
ELSE
--OUTPUT THE EXISTING PK uniqueidentifier

SET @PkCurrentID = (SELECT PK
FROM [dbo].[sAccountLog]
WHERE AccountNumber = @AccountNumber)
--NO DATA IS ACTUALLY UPDATING HERE, I'M JUST TRYING TO OUTPUT THE EXISTING PK column
UPDATE [dbo].[AccountLog]
SET
PK = @PkCurrentID,
AccountNumber = @AccountNumber
OUTPUT @PkCurrentID
WHERE AccountNumber = @AccountNumber








you cant use OUTPUT with SELECT

you can use it with UPDATE statement though
like


UPDATE [dbo].[AccountLog]
SET
PK = @PkCurrentID,
AccountNumber = @AccountNumber
OUTPUT inserted.PK
WHERE AccountNumber = @AccountNumber


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-02-08 : 08:59:10
On your stored proc declaration, specify output parameter as

Create Procedure dbo.Your_SP
@ABC datatype,
@my-UID uniqueidentifier OUTPUT
AS
...
Select @my-UID
GO


Then, when you call, assign that returned value to your local variable.

If you call from another stored procedure, it may look like this:

Declare @return_PK as uniqueidentifier
Exec dbo.Your_PS @ABC='input value', @my-UID = @return_PK OUTPUT;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-08 : 09:08:53
see

http://www.sqlteam.com/article/stored-procedures-returning-data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -