?
Solved

how to return resultset from stored procedure?

Posted on 2004-10-18
9
Medium Priority
?
23,275 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
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 150 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 150 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

752 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