SELECT query stmt inside a Stored Procedure

Question

Post reply

SELECT query stmt inside a Stored Procedure

SQL Pgmr

SSC Veteran

Points: 297
More actions
November 21, 2005 at 6:28 am

#95739

Friends,
What are the possible usuages of a SELECT query stmt inside a stored procedure ??
How can we process the results of the SELECT query other than for documentation/Reporting purposes(Correct me if i'm wrong in this) ??
can any one throw some lite on this ..
Thanks,
SqlPgmr

Viewing 7 posts - 1 through 6 (of 6 total)

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

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 1

http://msdn.microsoft.com/library/en-us/tsqlref/ts_sa-ses_9sfo.asp?frame=true

You can use the results whatever way you want for any purpose.

Regards,
gova

Ian Yates SSCoach Points: 19738 More actions · Answer 2

You can spool the data to a temp table (of the # or @ variety) - this lets you do whatever you wish with the output of the select statement.

You can return one or more datasets to the client.

If you have complex business requirements that require summarising some data from several tables, performing some other operations, etc and then putting this data into yet another table, rather than pushing & pulling all of that data to a client app, you can do it all within one stored proc and some temporary tables.

Also, sometimes it is more efficient to use temporary tables (which you would populate via

INSERT INTO xxx SELECT yyy statements) than using many tables in complex joins - you can specify more precisely (by explicitly using the temp tables) how SQL should join the data.

SQL Pgmr SSC Veteran Points: 297 More actions · Answer 3

Thanks friends for the quick response.

If i've a simple proc like this

CREATE PROCEDURE sp_test

AS

BEGIN

SELECT * FROM EMPLOYEE

END

and when i called the PROC within a procedure , is there any possibility of using this result set in the calling procedure???

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 4

CREATE TABLE #Temp(Col1 datatype, col2 datatype, ...)

INSERT #Temp Exec sp_test

or

DECLARE @myTemp TABLE (Col1 datatype, col2 datatype, ...)

INSERT @myTemp Exec sp_test

Will get the result set into the temp table of current stored procedure.

Regards,
gova

SQL Pgmr SSC Veteran Points: 297 More actions · Answer 5

Thanks again..

If the proc (sp_test) has multiple select queries..then what happens to the result set handling as by above scenario?? ( Also is multiple SELECT query case is used in Real world applications )

Regards,

SqlPgmr

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 6

When you use ADO or ADO.net it would be possible to use the resultset in fron end processing. It is used in real world applications.

Within another procedure or with SQL query analyser it is not possible to use the resultsets of multiple select stored procedures.

Regards,
gova