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 SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-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 SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||Thanks, Dan.
This helped clarify the issue for me, and your suggestions worked perfectly.
Appreciate your help...
Dan Guzman wrote:
>> 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
>)
>> Hi all,
>[quoted text clipped - 180 lines]
>> Thanks.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||I'm glad I was able to help you out.
--
Dan Guzman
SQL Server MVP
"thegekkster via SQLMonster.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...
>