Solved

how to return resultset from stored procedure?

Posted on 2004-10-18
9
23,238 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 7

Author Comment

by:Daniel Stanley
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
-- 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now