Solved

how to return resultset from stored procedure?

Posted on 2004-10-18
9
23,251 Views
Last Modified: 2012-06-27
not familiar with sql server stored procedures. i am only knowledgable in oracle and informix.

i need to know if it is possible to return a loaded resultset from a sql server stored procedure. i need to be able to do this from a single call to the stored procedure because these procedures will be called through odbc from linked query objects in microsoft access.  using ADO an OLE to bind client cursor objects is not an option.

looking for an example of a simple stored proc that returns a first_name and last_name from an employee table with all employees with a last name started with var1; var1 will be passed in to the procedure at runtime.  please demonstrate the call to the procedure and what the resultset should look like to the client. temp tables are not an option so please do not use that route.

thank you in advance.

daniels@asix.com
0
Comment
Question by:Daniel Stanley
9 Comments
 
LVL 19

Expert Comment

by:grant300
ID: 12338878
This is easy.  SQL Server, by way of its Sybase genisis, can not only return a result set from a stored procedure, it can return multiple result sets as well as compute clause result sets. and output arguments.

Your example would look like:
CREATE PROCEDURE example_proc    @var1 varchar(32)
AS
BEGIN

 -- Append the wildcard on the input string.  Could be done in the app instead.
  SELECT @var1 = @var1 + '%'  

  SELECT first_name, last_name
    FROM empolyee
  WHERE last_name LIKE @var1

  RETURN
END

That's all there is to it.  Any SELECT statement that does not direct its result set to a table, cursor, or variables returns the results to the client.

Bill
0
 
LVL 13

Expert Comment

by:BlackTigerX
ID: 12338897
yes it is possible to return a resultset as well as any variables that you specify, plus the actual result from the Stored Procedure

in your case it would be something like
--use ALTER PROCEDURE yourProcedureName if you have already created it
create procedure YourProcedureName(
var1 varchar(20)
)
as
select first_name, last_name from employee (nolock)
where left(last_name, len(var1))=var1

the call to the stored procedure just depends where you are calling it from, if is from SQL it self, is just something like

YourProcedureName 's'

would return all the names whose last name begins with "s"

the resultset is just a standard dataset that can be handled by anything that uses ADO, ODBC, etc
0
 
LVL 19

Expert Comment

by:grant300
ID: 12338953
BlackTigerX's WHERE clause syntax is very "3GL" and not the preferred way to do it in SQL

Using the LIKE predicate in the WHERE clause allows the optimizer to do its thing which can include using an INDEX that may include the last_name column.

>>> where left(last_name, len(var1))=var1  <<<  forces a table scan with no possibility of index use.

If there is no index, the LIKE predicate, with a trailing wildcard, will do the same type of operation as BlackTigerX suggests but will do so in lower level (and faster) code in the engine.  You also do not have to worry about rewriting the code to take advantage of an index addition or change down the road someplace.

As a rule, let the database engine do what it does best.  You generally wind up with a better all around result that way.

Bill
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 7

Author Comment

by:Daniel Stanley
ID: 12339293
if you have multiple cursors being opened and fetched out of from within a stored proceudure, how do you specify the definition of your final resultset.  i see the RETURN clause but i do not see how it knows what exactly to return the the client.
 
please explain.

thx,
daniels@asix.com
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 50 total points
ID: 12339921
You "don't" return your cursor in SQL Server

you do a
Select * from ....

and the output is returned as the resultset...

the return clause specifies an exit point from within the procedure
and allow for a specific Return code value to be returned

eg

Declare @RC int
...
set @rc = -99
...
 RETURN @RC

 
the stored procedure being invoked as

EXECUTE @MyRC = TheStoredProcedureName Parm1,Parm2,Parm3 OUTPUT, ....

which allows for a ReturnCode from the procedure into your @MyRC parameter
several Input/Output Parameters , of which you can recieve modifications  from within the stored procedure
for Parm3 (if Parm3 is defined as being of an OUTPUT type within the SP also)
plus any result sets the procedure may return


hth


0
 
LVL 19

Assisted Solution

by:grant300
grant300 earned 50 total points
ID: 12340264
Again,
> Any SELECT statement that does not direct its result set to a table, cursor, or variables returns the results to the client.

That means you can use SELECT/INTO, INSERT/SELECT, SELECT @variable = ..........., Open CURSOR/FETCH INTO @var1, @var2.... as much as you want.

Everything else goes to the client.

BIll
0
 
LVL 12

Expert Comment

by:ill
ID: 12345317
-- if you don't need to modify database data , it's better to use function instead of procedure.
create function dbo.userDet( @var1 char(1)) returns table as
return(
select top 1 first_name, last_name  from employee where left( last_name, 1)= @var1
)
GO

--you can work with then same way as with tables. the select passed to openquery in access will be like:

select * from dbo.userDet('e')
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question