SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Sql Stored Proc, OUTPUT uniqueidentifer
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

richdiaz99
Starting Member

22 Posts

Posted - 02/06/2013 :  18:22:02  Show Profile  Reply with Quote
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

Edited by - richdiaz99 on 02/06/2013 18:22:45

James K
Flowing Fount of Yak Knowledge

3636 Posts

Posted - 02/06/2013 :  19:40:14  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/06/2013 :  22:52:58  Show Profile  Reply with Quote

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




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

Go to Top of Page

richdiaz99
Starting Member

22 Posts

Posted - 02/07/2013 :  09:37:11  Show Profile  Reply with Quote
quote:
Originally posted by James K

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





Edited by - richdiaz99 on 02/07/2013 10:44:28
Go to Top of Page

richdiaz99
Starting Member

22 Posts

Posted - 02/07/2013 :  10:44:02  Show Profile  Reply with Quote
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






Edited by - richdiaz99 on 02/07/2013 14:08:25
Go to Top of Page

richdiaz99
Starting Member

22 Posts

Posted - 02/07/2013 :  14:09:05  Show Profile  Reply with Quote
ELSE code above had a typo, fixed now.

Edited by - richdiaz99 on 02/07/2013 14:09:44
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/07/2013 :  23:14:36  Show Profile  Reply with Quote
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

786 Posts

Posted - 02/08/2013 :  08:59:10  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/08/2013 :  09:08:53  Show Profile  Reply with Quote
see

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

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000