Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem with SET of return Params Expand / Collapse
Author
Message
Posted Friday, May 9, 2008 7:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 10, 2008 9:20 AM
Points: 3, Visits: 13
This is my sproc - the gist of is to return a PDF ticket from the DB

It is not a complex sproc and I have worked with return values many times before - though I don't think I have ever had more than 1 return like I do here.

Problem : my @return_value does not get set except in the first condition "SET @return_value = '-1'" (if condition is met of course) - Anywhere else I either get the original or default value (tried many things)

Can anyone spot a potential problem? BTW - This is getting called from an ASP script

Thanks for your input:D

--------------code
-- =============================================
-- Author: CF
-- COULD NOT GET THE RETURN PARAMS TO WORK
-- =============================================
CREATE PROCEDURE usp_emailTicket_get_ABANDONED
(
@emailID uniqueidentifier,
@pdfPass varchar(50),
@return_value varchar(10) OUTPUT -- RETURN -1 if GUID not found, -2 if too many bad tries, -3 for bad pass, 1 = ok
)
AS

set nocount on

-----------------------------------------------------------------------------------------------------------------------------------
--Check if the ticket is in the DB

SELECT emailID FROM ticketEmail WHERE emailID = @emailID

IF @@ROWCOUNT = 0 -- the Ticket does not exist return -1
BEGIN
--SET @Status = '-1'
SET @return_value = '-1'
return @return_value
END

--There are 5 max made attempts to get the PDF
DECLARE @BadTries INT
SET @badTries = (SELECT badAttempts FROM ticketEmail WHERE emailID = @emailID) -- pull up # failed attempts

IF @badTries <= 5
BEGIN
--get the record based on guid
SELECT ticketData, emailID
FROM ticketEmail
where emailID = @emailID AND pdfPassword = @pdfPass

IF @@ROWCOUNT = 0 -- there was no match above so increment bad attempts
BEGIN
UPDATE ticketEmail
SET badAttempts = badAttempts + 1
WHERE emailID = @emailID

SET @return_value = '-3' --BAD PASSWORD
return @return_value

END
ELSE -- matched guid + password meaning PDF was found
BEGIN
SET @return_value = '1'

SELECT ticketData, emailID
FROM ticketEmail
where emailID = @emailID AND pdfPassword = @pdfPass

return
END

END
ELSE -- MORE THAN 5 TRIES FOR THE RECORD
BEGIN
SET @return_value = '-2'
return @return_value
END


Return
GO


Post #497894
Posted Saturday, May 10, 2008 9:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 10, 2008 9:20 AM
Points: 3, Visits: 13
I think this is some sort of ado bug - not sql - running a sql statement ahead of any set @param causes that param to not get assigned - strange
Post #498375
Posted Monday, May 12, 2008 9:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, September 28, 2014 12:23 AM
Points: 2,397, Visits: 3,411
The @return_value is actually the stored procedures return value

CREATE PROCEDURE usp_emailTicket_get_ABANDONED
(
@emailID UNIQUEIDENTIFIER,
@pdfPass VARCHAR(50)
)
AS

SET NOCOUNT ON

DECLARE @BadTries TINYINT,
@Pass TINYINT,
@TicketData [red]{your datatype here}[/red]

SELECT @BadTries = badAttempts,
@Pass = CASE WHEN pdfPassword = @pdfPass THEN 1 ELSE 0 END,
@TicketData = ticketData
FROM ticketEmail
WHERE emailID = @emailID

IF @BadTries IS NULL
RETURN -1

IF @BadTries > 5
RETURN -2

IF @Pass < 1
BEGIN
UPDATE ticketEmail
SET BadAttempts = BadAttempts + 1
WHERE emailID = @emailID

RETURN -3
END

SELECT @TicketData,
@emailID

RETURN 1




N 56°04'39.16"
E 12°55'05.25"
Post #498920
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse