return type of a stored Proc

Question

return type of a stored Proc

pradeepguna

SSC Journeyman

Points: 89
More actions
December 19, 2003 at 3:38 am

#83728

Gurus pls help me out..
how can i find out the return type of a Stored procedure..like whether it returns a Recordset/a string/an integer/nothing
very urgent guys..
hoping for a solution
bye
pradeep

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 1

Really the only way is by looking at the parameters and by examining the code itself. If you're just wondering about parameters, the object browser within Query Analyzer will show you what parameters are for OUTPUT. These are the ones that return values. However, a lot of folks simply return a recordset (and that recordset could be a single value, mind you) by using a SELECT within the stored procedure, hence the reason you'll have to examine the code. Some others use the RETURN() statement, but this is supposed to be used to return a status such as 0, everything is OK, 1, an error occurred, etc. You get the idea.

K. Brian Kelley, GSEC

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/

K. Brian Kelley
@kbriankelley

pradeepguna SSC Journeyman Points: 89 More actions · Answer 2

Thanks Kelly

As far as I dug out SQL server,I think that we will not be able to Get the return Type for a SP.i tried out the following and got the conclusion..pls correct if im wrong

The stored procedures are listed in the command tab of Data environment(VB).i used that in conjunction with the SQL Profiler..

after the SP's list are populated(SQl server uses an SP "sp_stored_procedures".

1.select a stored procedure from Dataenvironments property window for a "command"

2.Start the Profiler.Start a trace with "TSQL and SP" selected

3.move to the Dataenvironment window and select the Paramaters Tab

4.Inspect the Profilers Trace

(it actually calls the selected SP and gets the resultset and displays the fields returned)

The code for the SP is below



CREATE PROCEDURE spReturnTypsTest 

@in int

AS

if @in<>1  insert into rettypetest values(@in) 

else SELECT * FROM CUSTOMER

GO

the idea is to check out what happens when different Types(recordset,Integer etc)are returned!!!(i know it cannot be like that)