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

Stored Procedure OUTPUT param VS RETURN VS SELECT Expand / Collapse
Author
Message
Posted Tuesday, December 31, 2013 2:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 4:47 AM
Points: 171, Visits: 600
Hi All,

I have been trying to get a Store Procedure to return a value for success or failure back to a PHP web application that's calling it.
But i'm having absolutely no joy as PHP is not getting anything back, output param works fine in SQL though.
So I have decided to use a Select instead. Are there any pitafalls in using a Select for returning something over an output parameter?

Many Thanks

B
Post #1526690
Posted Tuesday, December 31, 2013 2:29 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:52 AM
Points: 470, Visits: 823
this would not hurt only if this store procedure is executed independently. if you are using this store procedure in side a store procedure then it could hurt u.

its a very regular thing to have an output parameter calling in a PHP. i would suggest u should do some Google on this related issue. i might not be the 1st one to have this issue.



Post #1526693
Posted Tuesday, December 31, 2013 3:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 4:47 AM
Points: 171, Visits: 600
twin.devil (12/31/2013)
this would not hurt only if this store procedure is executed independently. if you are using this store procedure in side a store procedure then it could hurt u.

its a very regular thing to have an output parameter calling in a PHP. i would suggest u should do some Google on this related issue. i might not be the 1st one to have this issue.





I did get the output parameter working using the mssql library but it does not work on Linux server where you have to use dblib for PDO, PHP -> MSQL connections. That is why I had to resort to using the select.
Post #1526705
Posted Tuesday, December 31, 2013 4:34 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:52 AM
Points: 470, Visits: 823
you just need to make sure that this procedure is not in use in any other procedure beside that no harm using this approach.
Post #1526718
Posted Tuesday, December 31, 2013 5:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
The return value of a stored procedure is traditionally used for this:

RETURN 0 if the procedure ran successfully

RETURN -n if the procedure failed.

ALTER PROCEDURE TestOutput @Outcome CHAR (4)
AS IF UPPER(@Outcome) = 'FAIL' RETURN -1
RETURN 0
GO

DECLARE @Returnvalue INT

EXEC @Returnvalue = TestOutput 'PASS'
PRINT @Returnvalue
--prints 0

EXEC @Returnvalue = TestOutput 'FAIL'
PRINT @Returnvalue
--prints -1



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1526724
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse