May 9, 2008 at 7:52 am
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
May 10, 2008 at 9:22 am
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
May 12, 2008 at 9:35 am
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
FROMticketEmail
WHEREemailID = @emailID
IF @BadTries IS NULL
RETURN -1
IF @BadTries > 5
RETURN-2
IF @pass < 1
BEGIN
UPDATEticketEmail
SETBadAttempts = BadAttempts + 1
WHEREemailID = @emailID
RETURN -3
END
SELECT@TicketData,
@emailID
RETURN 1
N 56°04'39.16"
E 12°55'05.25"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply