Help with using OUTPUT parameter

Hi all,
I have a procedure (sp1) that needs to execute another procedure (sp2); need
to set a variable (@.pass) in sp1 with a value generated within sp2. SP1 is
called, executed from within ASP. As it is currently I get a value of 0 [
;zero]
in the table.
Code for SP1:
--
CREATE PROCEDURE dbo.usp_AddAffiliateApplication
(
@.web varchar(200), @.url varchar(200), @.cat int, @.first varchar(100), @.last
varchar(100),
@.email varchar(100), @.area char(3), @.phone char(7), @.structure int, @.pay
varchar(200),
@.add1 varchar(200), @.add2 varchar(200), @.city varchar(100), @.state int,
@.zip char(5),
@.country int, @.pass varchar(20), @.ip varchar(15), @.send char(1) OUTPUT
)
AS
SET NOCOUNT ON
SET @.send = 'N'
IF @.url NOT IN ( SELECT a.applicantURL
FROM dbo.affiliateApplications a )
BEGIN
SET @.send = 'Y'
EXEC @.pass = dbo.usp_GeneratePassword
--
DECLARE @.TRAN1 varchar(50)
SELECT @.TRAN1 = 'AddAffiliateApplication'
BEGIN TRAN @.TRAN1
WITH MARK 'Insert-AAA'
INSERT INTO dbo.affiliateApplications (applicantWebsiteName, applicantURL,
affiliateCategoryID,
applicantFirstName,
applicantLastName, applicantEmailAddress,
applicantAreaCode,
applicantPhoneNumber, businessStructureID,
applicantPayName,
applicantAddress1, applicantAddress2,
applicantCity, stateID,
applicantZipCode, countryID,
applicantPassword, applicantIP,
applicantDateApplied)
VALUES (@.web, @.url, @.cat, @.first, @.last, @.email, @.area, @.phone,
@.structure, @.pay, @.add1,
@.add2, @.city, @.state, @.zip, @.country, @.pass, @.ip, GETDATE())
IF @.@.ERROR = 0
BEGIN
COMMIT TRAN AddAffiliateApplication
END
ELSE
BEGIN
ROLLBACK TRAN AddAffiliateApplication
END
END
ELSE
BEGIN
DECLARE @.days int
SELECT @.days = DATEDIFF(d, aa.applicantDateApplied, GETDATE())
FROM dbo.affiliateApplications aa
WHERE (aa.applicantURL = @.url) AND ((aa.
applicantApprovalStatus = 'N')
OR (aa.
applicantApprovalStatus = 'P'))
IF @.days <= 90
BEGIN
SET @.send = 'X'
END
ELSE
BEGIN
SET @.send = 'Y'
DECLARE @.TRAN2 varchar(50)
SELECT @.TRAN2 = 'AddAffiliateApplication'
BEGIN TRAN @.TRAN2
WITH MARK 'Update-AAA'
UPDATE dbo.affiliateApplications
SET applicantDateApplied = GETDATE(), applicantApprovalStatus = 'P'
WHERE (applicantURL = @.url)
IF @.@.ERROR = 0
BEGIN
COMMIT TRAN AddAffiliateApplication
END
ELSE
BEGIN
ROLLBACK TRAN AddAffiliateApplication
END
END
END
GO
Code for SP2:
--
CREATE PROCEDURE dbo.usp_GeneratePassword
(
@.passLength int = 8,
@.passType char(7) = 'complex'
)
AS
BEGIN
DECLARE @.password varchar(20)
DECLARE @.type tinyint
DECLARE @.bitmap char(12)
SET @.password = ''
SET @.bitmap = 'aeiouy!#$-_+'
WHILE @.passLength > 0
BEGIN
IF @.passType = 'simple'
BEGIN
IF (@.passLength%2) = 0
SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
(RAND() * (11)),0)),1)
ELSE
SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
END
ELSE
BEGIN
SET @.type = ROUND(1 + (RAND() * (3)),0)
IF @.type = 1
SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
ELSE IF @.type = 2
SET @.password = @.password + CHAR(ROUND(65 + (RAND() * (25)),0))
ELSE IF @.type = 3
SET @.password = @.password + CHAR(ROUND(48 + (RAND() * (9)),0))
ELSE IF @.type = 4
SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
(RAND() * (11)),0)),1)
END
SET @.passLength = @.passLength - 1
END
SELECT @.password OUTPUT
END
GO
Other than this issue, in which I guess I'm not going about setting or
getting the output parameter in the right way, each of the SPs work properly
by themselves.
Any help or suggestions appreciated.
Thanks.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1> EXEC @.pass = dbo.usp_GeneratePassword
This statement will assign the stored procedure return code to the @.pass
variable. However you are returning the password generated by
usp_GeneratePassword as a result set (SELECT statement).
A stored procedure return code (returned via a RETURN statement) is an
integer usually used to indicate success or failure. Data can be returned
via OUTPUT parameters or in a result set. It's easier to process OUTPUT
parameters in Transact-SQL rather than a result set.
To return the password value as an output parameter, change the
usp_AddAffiliateApplication code to:
EXEC dbo.usp_GeneratePassword @.password = @.pass OUTPUT
Remove the SELECT from usp_GeneratePassword and change the header as
follows:
CREATE PROCEDURE dbo.usp_GeneratePassword
(
@.passLength int = 8,
@.passType char(7) = 'complex',
@.password varchar(20) OUTPUT
)
Hope this helps.
Dan Guzman
SQL Server MVP
"thegekkster" <u6631@.uwe> wrote in message news:613dacf27c8fd@.uwe...
> Hi all,
> I have a procedure (sp1) that needs to execute another procedure (sp2);
> need
> to set a variable (@.pass) in sp1 with a value generated within sp2. SP1 is
> called, executed from within ASP. As it is currently I get a value of 0
> [zero]
> in the table.
> --
> Code for SP1:
> --
> CREATE PROCEDURE dbo.usp_AddAffiliateApplication
> (
> @.web varchar(200), @.url varchar(200), @.cat int, @.first varchar(100),
> @.last
> varchar(100),
> @.email varchar(100), @.area char(3), @.phone char(7), @.structure int, @.pay
> varchar(200),
> @.add1 varchar(200), @.add2 varchar(200), @.city varchar(100), @.state int,
> @.zip char(5),
> @.country int, @.pass varchar(20), @.ip varchar(15), @.send char(1) OUTPUT
> )
> AS
> SET NOCOUNT ON
> SET @.send = 'N'
> IF @.url NOT IN ( SELECT a.applicantURL
> FROM dbo.affiliateApplications a )
> BEGIN
> SET @.send = 'Y'
> --
> EXEC @.pass = dbo.usp_GeneratePassword
> --
> DECLARE @.TRAN1 varchar(50)
> SELECT @.TRAN1 = 'AddAffiliateApplication'
> BEGIN TRAN @.TRAN1
> WITH MARK 'Insert-AAA'
> --
> INSERT INTO dbo.affiliateApplications (applicantWebsiteName,
> applicantURL,
> affiliateCategoryID,
> applicantFirstName,
> applicantLastName, applicantEmailAddress,
> applicantAreaCode,
> applicantPhoneNumber, businessStructureID,
> applicantPayName,
> applicantAddress1, applicantAddress2,
> applicantCity, stateID,
> applicantZipCode, countryID,
> applicantPassword, applicantIP,
> applicantDateApplied)
> VALUES (@.web, @.url, @.cat, @.first, @.last, @.email, @.area, @.phone,
> @.structure, @.pay, @.add1,
> @.add2, @.city, @.state, @.zip, @.country, @.pass, @.ip, GETDATE())
> --
> IF @.@.ERROR = 0
> BEGIN
> COMMIT TRAN AddAffiliateApplication
> END
> ELSE
> BEGIN
> ROLLBACK TRAN AddAffiliateApplication
> END
> END
> ELSE
> BEGIN
> DECLARE @.days int
> SELECT @.days = DATEDIFF(d, aa.applicantDateApplied, GETDATE())
> FROM dbo.affiliateApplications aa
> WHERE (aa.applicantURL = @.url) AND ((aa.
> applicantApprovalStatus = 'N')
> OR (aa.
> applicantApprovalStatus = 'P'))
> IF @.days <= 90
> BEGIN
> SET @.send = 'X'
> END
> ELSE
> BEGIN
> SET @.send = 'Y'
> DECLARE @.TRAN2 varchar(50)
> SELECT @.TRAN2 = 'AddAffiliateApplication'
> BEGIN TRAN @.TRAN2
> WITH MARK 'Update-AAA'
> --
> UPDATE dbo.affiliateApplications
> SET applicantDateApplied = GETDATE(), applicantApprovalStatus = 'P'
> WHERE (applicantURL = @.url)
> --
> IF @.@.ERROR = 0
> BEGIN
> COMMIT TRAN AddAffiliateApplication
> END
> ELSE
> BEGIN
> ROLLBACK TRAN AddAffiliateApplication
> END
> END
> END
> GO
> --
> Code for SP2:
> --
> CREATE PROCEDURE dbo.usp_GeneratePassword
> (
> @.passLength int = 8,
> @.passType char(7) = 'complex'
> )
> AS
> BEGIN
> DECLARE @.password varchar(20)
> DECLARE @.type tinyint
> DECLARE @.bitmap char(12)
> SET @.password = ''
> SET @.bitmap = 'aeiouy!#$-_+'
> WHILE @.passLength > 0
> BEGIN
> IF @.passType = 'simple'
> BEGIN
> IF (@.passLength%2) = 0
> SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
> (RAND() * (11)),0)),1)
> ELSE
> SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
> END
> ELSE
> BEGIN
> SET @.type = ROUND(1 + (RAND() * (3)),0)
> IF @.type = 1
> SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
> ELSE IF @.type = 2
> SET @.password = @.password + CHAR(ROUND(65 + (RAND() * (25)),0))
> ELSE IF @.type = 3
> SET @.password = @.password + CHAR(ROUND(48 + (RAND() * (9)),0))
> ELSE IF @.type = 4
> SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
> (RAND() * (11)),0)),1)
> END
> SET @.passLength = @.passLength - 1
> END
> SELECT @.password OUTPUT
> END
> GO
> Other than this issue, in which I guess I'm not going about setting or
> getting the output parameter in the right way, each of the SPs work
> properly
> by themselves.
> Any help or suggestions appreciated.
> Thanks.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1|||Thanks, Dan.
This helped clarify the issue for me, and your suggestions worked perfectly.
Appreciate your help...
Dan Guzman wrote:[vbcol=seagreen]
>This statement will assign the stored procedure return code to the @.pass
>variable. However you are returning the password generated by
>usp_GeneratePassword as a result set (SELECT statement).
>A stored procedure return code (returned via a RETURN statement) is an
>integer usually used to indicate success or failure. Data can be returned
>via OUTPUT parameters or in a result set. It's easier to process OUTPUT
>parameters in Transact-SQL rather than a result set.
>To return the password value as an output parameter, change the
>usp_AddAffiliateApplication code to:
>EXEC dbo.usp_GeneratePassword @.password = @.pass OUTPUT
>Remove the SELECT from usp_GeneratePassword and change the header as
>follows:
>CREATE PROCEDURE dbo.usp_GeneratePassword
>(
> @.passLength int = 8,
> @.passType char(7) = 'complex',
> @.password varchar(20) OUTPUT
> )
>
>[quoted text clipped - 180 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1|||I'm glad I was able to help you out.
Dan Guzman
SQL Server MVP
"thegekkster via droptable.com" <u6631@.uwe> wrote in message
news:61403d2080344@.uwe...
> Thanks, Dan.
> This helped clarify the issue for me, and your suggestions worked
> perfectly.
> Appreciate your help...
>