Assign the Result from one Stored Proc into a variable in another Proc

Question

Post reply

Assign the Result from one Stored Proc into a variable in another Proc

Darren-370250

Newbie

Points: 3
More actions
September 26, 2006 at 6:46 am

#192386

Hi
Could anyone explain why this is not working and how I might get the result i want
SQL Analyser
DECLARE @Pin varchar(12)
Exec @Pin=GenerateSecurityNo
SELECT @Pin AS PinNumber

Stored Proc
CREATE PROCEDURE GenerateSecurityNo
AS
DECLARE @MaxInt int
DECLARE @SecurityNo varchar(12)
DECLARE @Pin int
INSERT INTO UniqueRef (FormRef)
SELECT MAX(FormReF)+1 AS MaxNo FROM UniqueRef
SELECT @MaxInt=(SELECT MAX(FormRef) FROM UniqueRef)
SET @SecurityNo=(SELECT SUBSTRING(CAST(LeisurePassCode AS Varchar(50)),1,12) FROM UniqueRef WHERE FormRef=@Maxint)
SET @Pin=@Maxint
DELETE FROM UniqueRef WHERE FormRef=@Maxint
SELECT @SecurityNo AS Code
GO
Result:
Code: BF00B50E-D90
PinNumber:0
The Pin Number is not being assigned to the variable?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ram Ram Ten Centuries Points: 1079 More actions · Answer 1

Change the proc as under

and then you could do something like

DECLARE @XPin varchar(12)

Exec GenerateSecurityNo @Pin=@XPin

SELECT @XPin AS PinNumber

CREATE PROCEDURE GenerateSecurityNo

(@Pin VARCHAR(12) = NULL OUTPUT)

AS

DECLARE @MaxInt int

DECLARE @SecurityNo varchar(12)

DECLARE @Pin int

INSERT INTO UniqueRef (FormRef)

SELECT MAX(FormReF)+1 AS MaxNo FROM UniqueRef

SELECT @MaxInt=(SELECT MAX(FormRef) FROM UniqueRef)

SET @SecurityNo=(SELECT SUBSTRING(CAST(LeisurePassCode AS Varchar(50)),1,12) FROM UniqueRef WHERE FormRef=@Maxint)

SET @Pin=@Maxint

DELETE FROM UniqueRef WHERE FormRef=@Maxint

SELECT @Pin=@SecurityNo

GO

Ram Ram Ten Centuries Points: 1079 More actions · Answer 2

DECLARE @Pin VARCHAR(12)

EXEC @Ret=GenerateSecurityNo @Pin OUTPUT

SELECT @Pin

Darren-370250 Newbie Points: 3 More actions · Answer 3

Thanks Sriram

That solved the problem but I ended up needing to change some applications logic to support the parameter.

Really anoying thing is the original code exec @PinNo=GenerateSecurityNo, is the documented method with SQL Server Help on how a result could be assigned to the variable.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Not sure if you know this...the return value of a stored proc must be an integer. Any other datatypes have to be returned via output params.

Darren-370250 Newbie Points: 3 More actions · Answer 5

Darren-370250

Newbie

Points: 3

September 27, 2006 at 7:07 am

#662697

Thanks

That explains it!