Using A recordset as an input parameter for a stored procedure
-
I have created a stored procedure that takes one input parameter. I am trying to pass a recordset, which contains over 300 values, as the input parameter. Therefore saving me executing the Sproc 300 times.
I have tried using a global variable within a DTS package to populate the sproc, but keep getting the error EXECTION_ACCESS_VIOLATION.
Can anyone help?
-
I'll be interested to see what others have to say on this one. To the best of my knowledge (which has holes that I find dismaying at times), you cannot pass an actual record set or a table of any type into a T-SQL procedure. As usual, there are certainly decent enough work-arounds.
If your record set is not too large, you could pass the values in a long VARCHAR string (up to 8000 in SQL Server 2000), and use a delimiter to separate the "rows". Then, inside the procedure, parse them out into a temp table for easy accessibility.
You could also populate a temp table of the pervasive persuasion (i.e., '##'), or even a permanent table, and pass in the name; then you could access the table using dynamic T-SQL and once again populate a local temp table with the results. Or just build a permanent table that serves as perennial record source to your application.
There are certainly ways to get the data into a procedure, but they may not be the way you envision.
-
Can you concatenate this parameter in a varchar(8000) like this?
'1,3,5,86,234'
-
Another option is to have an output parameter in you stored procedure that is data type cursor and then pass this to a second cursor in the calling stored procedure that does the fetch. Not something I have done in a live environment but it does work when I played with the code below.
CREATE PROC dbo.Test1
(
@crsResultSet cursor VARYING OUTPUT
)
AS
SET @crsResultSet = CURSOR FAST_FORWARD
FOR
SELECT StatusDesc FROM dbo.TABLE
OPEN @crsResultSet
GO
CREATE Proc dbo.Test2
AS
DECLARE @crs CURSOR
DECLARE @name varchar(100)
EXEC dbo.Test1 @crs OUTPUT
FETCH NEXT FROM @crs INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
PRINT @name
FETCH NEXT FROM @crs INTO @name
END
CLOSE @crs
DEALLOCATE @crs
GO
EXEC dbo.test2
Hope this helps
regards
-
almost had an heart attack...
please AVOID cursors if possible!!!!!!!!
-
From where are you calling the stored proc? If you are calling it from a client app written in VB, for example, can you create a disconnected recordset and then update a database table in your server database?
There is no "i" in team, but idiot has two. -
This approach will allow to parse through each value within your existing SP. I personally would suggest using the pipe [ | ] as that is a very uncommon delimiter and if any text is passed into your SP, comma's may already exist within that recordset.
I wasn't born stupid - I had to study.
-
Many thanks for all your help, I eventually adapted the cursor method, which works really well!!
Does anyone know why the Global variable option doesnt work...after researching on the internet, gloabal variables seemed the most logical solution!!?
-
I was assuming an id list. Another way would be a #temp table where you'd insert the data to and use that table to do the rest of the job.
-
When you "assume"...
What problem are you having with the @@GlobalVariable?
I wasn't born stupid - I had to study.
-
I have to assume sometimes... now and then I feel like I'm interrogating a POW to find out the info I really need to solve a problem
. -
The following sets a loop which changes the input values according to a record in an input table. The example uses integers but the same loop can be applied to operations on other data types.
Tony Glover
Healthcare Data Analyst
WINhealth Partners
--adds a column starting with 1 numbered to the last input parameter
select input1, identity(int,1,1) as inputorder
into input_table
from input_basefile
declare @i int
declare @l int
--sets 1st and last value of loop
set @i = (select min(inputorder) from input_table)
set @l = (select max(inputorder) from input_table)
--creates output table in which to place results
create table output_table(input1 int, inputorder int, output1 int)
--defines loop
while @i <= @l
begin
--performs calculation and inserts a record into the output table
insert into output_table
select input1, inputorder, ((input1*inputorder)+999) as output1 --or the function you want to perform
from input_table
where @i = inputorder
--adds 1 to i
set @i = @i + 1
end
-
In the Newbies section, that is a bit more acceptable. But I've done that myself in the TSQL section and that is "clobber" time.... TGIF!!!
I wasn't born stupid - I had to study.
-
We shouldn't accept that... the more info we get the better, and the more often we'll get it.
-
Agreed! But with Newbies, it sometimes takes a little longer. And sometimes, (more than I would want) "senior moments" happen to me too...
But over all, you are right and I am happy for that web page you set up to show people what we need! You borg, you...
I wasn't born stupid - I had to study.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply