| Author |
 Topic  |
|
|
richdiaz99
Starting Member
22 Posts |
 Posted - 02/06/2013 : 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 |
Edited by - richdiaz99 on 02/06/2013 18:22:45
|
|
|
James K
Flowing Fount of Yak Knowledge
1515 Posts |
Posted - 02/06/2013 : 19:40:14
|
This?ELSE
--OUTPUT THE EXISTING PK uniqueidentifier
SELECT PK
FROM [dbo].[AccountLog]
WHERE AccountNumber = @AccountNumber |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 02/06/2013 : 22:52:58
|
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/
|
|
|
|
richdiaz99
Starting Member
22 Posts |
Posted - 02/07/2013 : 09:37:11
|
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 |
|
|
|
richdiaz99
Starting Member
22 Posts |
Posted - 02/07/2013 : 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
|
Edited by - richdiaz99 on 02/07/2013 14:08:25 |
|
|
|
richdiaz99
Starting Member
22 Posts |
Posted - 02/07/2013 : 14:09:05
|
| ELSE code above had a typo, fixed now. |
Edited by - richdiaz99 on 02/07/2013 14:09:44 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 02/07/2013 : 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/
|
|
|
|
Hommer
Aged Yak Warrior
693 Posts |
Posted - 02/08/2013 : 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; |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
|
| |
Topic |
|
Sql Stored Proc, OUTPUT uniqueidentifer
