Ending a stored proc
in [Programming SQL]
|
Prev: I assume Row_Number doesn’t act only onrows of the window frame
Next: US-MD-Silver Spring: SQL VMWare TV Network DBA
From: DavidC on 30 Apr 2010 18:06 I am trying to end a stored procedure and return a value of -1 to tell the application that a check number already exists. Below is the code in my sp where I am checking but it goes right past it and continues as though the result is false. When I run this as a separate query it returns the -1 fine. Can anyone see what might be wrong? Thanks. DECLARE @Return int; IF @Duplicate = 1 AND EXISTS (SELECT dbo.PayChecks.CheckNumber FROM dbo.PayChecks INNER JOIN dbo.PeopleLink ON dbo.PayChecks.PeopleLinkID = dbo.PeopleLink.PeopleLinkID WHERE (dbo.PayChecks.CheckNumber = @NewCheckNumber) AND (dbo.PeopleLink.Branch = @Branch)) BEGIN SET @Return = -1 RETURN @Return END -- David
From: Charles Hyman on 30 Apr 2010 22:49 Hi david... The client application that is receiving this return status has to use it as a explicit value. The return status value can be included in subsequent Transact-SQL statements in the batch or procedure that executed the current procedure, but it must be entered in the following form: EXECUTE @return_status = <procedure_name>. not sure if this helps Chas Hyman "DavidC" <dlchase(a)lifetimeinc.com> wrote in message news:6FEE6771-C74D-4DA9-B2B0-6A338943DF5E(a)microsoft.com... > I am trying to end a stored procedure and return a value of -1 to tell the > application that a check number already exists. Below is the code in my > sp > where I am checking but it goes right past it and continues as though the > result is false. When I run this as a separate query it returns the -1 > fine. > Can anyone see what might be wrong? Thanks. > > DECLARE @Return int; > > IF @Duplicate = 1 AND EXISTS (SELECT dbo.PayChecks.CheckNumber > FROM dbo.PayChecks INNER JOIN > dbo.PeopleLink ON dbo.PayChecks.PeopleLinkID = > dbo.PeopleLink.PeopleLinkID > WHERE (dbo.PayChecks.CheckNumber = @NewCheckNumber) > AND (dbo.PeopleLink.Branch = @Branch)) > BEGIN > SET @Return = -1 > RETURN @Return > END > > > -- > David
From: Patrice on 1 May 2010 04:07 Hi, > Below is the code in my sp > where I am checking but it goes right past it and continues as though the > result is false. If you meant it continues the code that follows inside the SP then it' just that the condition is false perhaps because @Duplicate, @NewCheckNumber or @Branch doesn't have the right value. Seeing alos the parameter declaration could perhaps help (is @Duplicate a BIT , do you pass a boolean client side ?) If you meant client side this is because you don't get the return value correctly. -- Patrice
From: Erland Sommarskog on 1 May 2010 11:53 DavidC (dlchase(a)lifetimeinc.com) writes: > I am trying to end a stored procedure and return a value of -1 to tell > the application that a check number already exists. Below is the code > in my sp where I am checking but it goes right past it and continues as > though the result is false. When I run this as a separate query it > returns the -1 fine. Can anyone see what might be wrong? Thanks. It is not clear what you mean with "goes right past", or you have concluded that. What happens if you do: DECLARE @ret int SELECT @ret = EXEC my_proc ... SELECT @ret I would guess that your have an error elsewhere. Possible suspects: 1) The parameter list. Check no parameter is declare as "varchar" without length. (That's the same as varchar(1).) 2) You are not passing the parameters correctly from your client code. 3) You are not retrieving the return value correctly in your client code (there are some gotchas in this area). In any case, the return value from a stored procedure is mainly used for indicating success/failure. While this could apply in this case, I think would still prefer an output parameter. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Uri Dimant on 2 May 2010 04:25
David You can also use an OUTPUT clause withing a stored procedure to retrun the value "DavidC" <dlchase(a)lifetimeinc.com> wrote in message news:6FEE6771-C74D-4DA9-B2B0-6A338943DF5E(a)microsoft.com... >I am trying to end a stored procedure and return a value of -1 to tell the > application that a check number already exists. Below is the code in my > sp > where I am checking but it goes right past it and continues as though the > result is false. When I run this as a separate query it returns the -1 > fine. > Can anyone see what might be wrong? Thanks. > > DECLARE @Return int; > > IF @Duplicate = 1 AND EXISTS (SELECT dbo.PayChecks.CheckNumber > FROM dbo.PayChecks INNER JOIN > dbo.PeopleLink ON dbo.PayChecks.PeopleLinkID = > dbo.PeopleLink.PeopleLinkID > WHERE (dbo.PayChecks.CheckNumber = @NewCheckNumber) > AND (dbo.PeopleLink.Branch = @Branch)) > BEGIN > SET @Return = -1 > RETURN @Return > END > > > -- > David |