?
Solved

Stored procedures with recordset as parameter:

Posted on 2003-11-08
15
Medium Priority
?
2,192 Views
Last Modified: 2010-05-18
I have a stored procedure that is calculating prices on products.

I would like to call that stored procedure from other stored procedures,
with a recordset of products as parameter.

I know that you can't do that in sql server.

Therefore I'm thinking of letting the first stored procedure
insert the productid's together with an unique id into a table
and then call the price procedure with that unique id as parameter.

I would like to have comments on this approach.
0
Comment
Question by:ola
[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
  • 5
  • 4
  • 3
  • +2
15 Comments
 
LVL 6

Expert Comment

by:robertjbarker
ID: 9708520
You could modify the procedure that needs the recordset to call the procedure providing the record set and put the results in a temporary table.  Something like:

create procedure userecordset as

begin
create table #recordset
(col1 int, col2 int, col3 int)

insert #recordset
exec providerecorset

-- do something with #recordset

drop table #recordset
return
end
go
0
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 2000 total points
ID: 9708964
You cannot pass table or cursor as input parameter.

You can create and populate a temp table in one proc (Proc1) to calculate prices and access the the same temp table in other procs if called with the same transaction. To make  the proc compile just create the temptable outside the proc

create table #temptable
(
col1 int,
col2 int
)

go

Create Proc1 as
BEGIN
Insert #temptable
select col1, col2 from MyTable
End
go

Create Proc2
as
BEGIN
  select * from #temptable
END

go
/*temptable is populated in this proc. In you case you will populate  temptabel with prices*/
exec proc1
/* you access the same temptable in proc2 */
exec proc2
go


HTH

Namasi

0
 
LVL 3

Author Comment

by:ola
ID: 9709450
namasi_navaretnam, could you explain
"To make  the proc compile just create the temptable outside the proc"

0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 3

Author Comment

by:ola
ID: 9709458
Would it be possible to use a table variable instead of a temporary table?
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9709779
>namasi_navaretnam, could you explain
> "To make  the proc compile just create the temptable outside the proc"

I said that because the second proc may complain about not finding the object #temptable when compiling as #temptable is created in Proc1.

Here is a complete example that I ran in Query Analyzer

STEP 1) Create Proc 1
drop Procedure Proc1
go

Create Procedure Proc1
as
BEGIN

create table #temptable
( col1 int,
  col2 int
)

Insert into #TempTable
select 1, 2

End
go

2) Step 2 - Create Proc2 to acccess #temptable. Notice that #temptable is create outside Proc2. This is just done to make Proc2 compile.

create table #temptable
( col1 int,
  col2 int
)
go

drop procedure Proc2
go

create procedure  Proc2
AS
BEGIN
select * from #temptable
END
go

drop table #temptable
go


Step 3) Execute Proc1 and Proc2. You can see that #temptable can be accessed within Proc2

exec Proc1
exec Proc2
go

>Would it be possible to use a table variable instead of a temporary table?

No it not possible to use table variable.

Namasi.

0
 
LVL 6

Expert Comment

by:robertjbarker
ID: 9710505
Basically all these solutions avoid passing the recordset in and out of the server.  It is easier to process everything in one place - the server.

So all this is pointed at selecting the records and then processing them without leaving the server.  And it may be even better if you go one step further; just have a single procedure that does everything, so you don't even have to pass things from procedure to procedure.
0
 
LVL 3

Author Comment

by:ola
ID: 9710682
robertjbarker, I'm considering that as well.

The downside with that is that I have about 10 stored procedures where I would like use the price logic. Then I would have to place this logic in 10 procedures.





0
 
LVL 6

Expert Comment

by:robertjbarker
ID: 9711905
I guess I should take a different point of view than my first suggestion.  I think what you want to do is, within each of your 10 procedures, create a temporary table, populate the table, then call the pricing procedure to process the records in the temporary table.

drop procedure putout
go
create procedure putout as
declare @count int
declare @col1 int
declare @col2 int
declare @col3 int
declare @str varchar(50)
select @count = count(*), @col1=max(col1), @col2=max(col2), @col3=max(col3) from #recordset
set @str = cast(@count as varchar(10)) + ' ' +
           cast(@col1 as varchar(10)) + ' ' +
           cast(@col2 as varchar(10)) + ' ' +
           cast(@col3 as varchar(10)) + ' '
print @str
return
go

drop procedure userecordset
go
create procedure userecordset (@val as int)  as

begin
create table #recordset
(col1 int, col2 int, col3 int)

insert #recordset (col1,col2,col3) values (@val,@val,@val)
exec putout

--drop table #recordset -- this is commented out for testing.
return
end
go

exec userecordset 1
exec userecordset 2
exec userecordset 3
exec putout  -- expect an error here.
exec userecordset 4
0
 
LVL 22

Expert Comment

by:CJ_S
ID: 9714250
I'm not quite sure if you can pass a table (variable). You can however pass a cursor.
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9714532
That is interesting CJ_S. Would you post a cursor example if you have one?
0
 
LVL 22

Expert Comment

by:CJ_S
ID: 9714579
Haven't done any sp's with CURSORs but this is what SQL Server help says:


All data types, including text, ntext and image, can be used as a parameter for a stored procedure. However, the cursor data type can be used only on OUTPUT parameters. When you specify a data type of cursor, the VARYING and OUTPUT keywords must also be specified. For more information about SQL Server - supplied data types and their syntax, see Data Types.
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9714632
I was able to get this example from help file. Thanks CJ_S. Ola can look into using cursor option as well.

USE pubs
GO
/* Create a procedure with a cursor output parameter. */
CREATE PROCEDURE OpenCrsr @OutCrsr CURSOR VARYING OUTPUT AS

SET @OutCrsr = CURSOR FOR
SELECT au_lname
FROM authors
WHERE au_lname LIKE 'S%'

OPEN @OutCrsr
GO

/* Allocate a cursor variable. */
DECLARE @CrsrVar CURSOR

/* Execute the procedure created earlier to fill
  the variable. */
EXEC OpenCrsr @OutCrsr = @CrsrVar OUTPUT

/* Use the variable to fetch the rows from the cursor. */
FETCH NEXT FROM @CrsrVar
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   FETCH NEXT FROM @CrsrVar
END

CLOSE @CrsrVar

DEALLOCATE @CrsrVar
GO

0
 
LVL 6

Expert Comment

by:robertjbarker
ID: 9715617
I believe you can pass a cursor as an OUTPUT only.  I think Ola wants to pass something TO a pricing procedure.  So the cursor idea would have the same problem as my original suggestion.
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9715725
I agree robertbaker.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9723357
Consider as an alternative to pass your resultset as an XML document.  But I suspect you need to rethink your approach.

Anthony

0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.

765 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