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) ASDECLARE @PkNewID uniqueidentifier SET @PkNewID = NEWID()SELECT PK, AccountNumber FROM [dbo].[AccountLog]WHERE AccountNumber = @AccountNumberIF @@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 uniqueidentifierSELECT PKFROM [dbo].[AccountLog]WHERE AccountNumber = @AccountNumber |
|
|
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)ASDECLARE @PkID uniqueidentifier SELECT @PkID=PKFROM [dbo].[AccountLog]WHERE AccountNumber = @AccountNumberIF @PkID IS NULLBEGININSERT INTO [dbo].[AccountLog]( PK,AccountNumber)OUTPUT INSERTED.PKVALUES(NEWID(),@AccountNumber)ENDELSESELECT @PkID[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
richdiaz99
Starting Member
22 Posts |
Posted - 2013-02-07 : 09:37:11
|
quote: Originally posted by James K This?ELSE--OUTPUT THE EXISTING PK uniqueidentifierSELECT PKFROM [dbo].[AccountLog]WHERE AccountNumber = @AccountNumber
|
|
|
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) ASDECLARE @PkNewID uniqueidentifier SET @PkNewID = NEWID()SELECT PK, AccountNumber FROM [dbo].[AccountLog]WHERE AccountNumber = @AccountNumberIF @@ROWCOUNT = 0 BEGIN INSERT INTO [dbo].[AccountLog] ( PK, AccountNumber ) OUTPUT @PkNewID VALUES ( @PkNewID, @AccountNumber ) ENDELSE--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 |
|
|
richdiaz99
Starting Member
22 Posts |
Posted - 2013-02-07 : 14:09:05
|
ELSE code above had a typo, fixed now. |
|
|
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) ASDECLARE @PkNewID uniqueidentifier SET @PkNewID = NEWID()SELECT PK, AccountNumber FROM [dbo].[AccountLog]WHERE AccountNumber = @AccountNumberIF @@ROWCOUNT = 0 BEGIN INSERT INTO [dbo].[AccountLog] ( PK, AccountNumber ) OUTPUT @PkNewID VALUES ( @PkNewID, @AccountNumber ) ENDELSE--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 SELECTyou can use it with UPDATE statement thoughlikeUPDATE [dbo].[AccountLog] SET PK = @PkCurrentID, AccountNumber = @AccountNumber OUTPUT inserted.PK WHERE AccountNumber = @AccountNumber ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2013-02-08 : 08:59:10
|
On your stored proc declaration, specify output parameter asCreate Procedure dbo.Your_SP @ABC datatype, @my-UID uniqueidentifier OUTPUTAS...Select @my-UIDGOThen, 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 uniqueidentifierExec dbo.Your_PS @ABC='input value', @my-UID = @return_PK OUTPUT; |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|