February 3, 2008 at 11:22 pm
Wondering what is recommended method to take data from a table and pass values into stored proc vars?
Say I have a talbe with 10 columns and 500 rows. Do I use cursor or assign variables in a loop and call proc 500 times, or is there something new in Sql 2005 where I could do something like:
Select @var 1 = col1, @var2 = col2, etc...
FROM Table
Into MyStoredProc
Any help is appreciated
February 4, 2008 at 4:51 am
If you can pass the data in as XML, you can use the XQUERY language in 2005 to pull the data out of it, exactly like a table. It looks a bit like this:
--@test is an XML data type, P is an element, the rest are attributes, or a row & columns if you will
SELECT x.P.value('@EffDt[1]','smalldatetime') --AS PolicyEffDt
,x.policy.value('@NumTxt[1]','NVARCHAR(25)') --AS NumTxt
,GETDATE()
,GETDATE()
,x.P.value('@Id[1]','int')
,x.P.value('@TypeId[1]','smallint')
,x.P.value('@SystemId[1]','smallint')
FROM @test.nodes('//P') x(P)
You can do something similar in 2000 using OPENXML (which also works in 2005), but it uses lots more memory to the same processing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 4, 2008 at 4:58 am
Yes... If u can pass XML its the best method.
"Keep Trying"
February 4, 2008 at 8:10 am
I think I may not have been clear on this. I am not trying to pass a table into a procedure as a single variable. I am trying to take each rows worth of data and pass each column into a procedure as its own variable. Normally I would set up a loop, assign each column in a row to its own variable, and then execute a procedure. I'm wondering if there is a better way to do this in 2005?
This is how I've always done it in the past:
while @count < 10
begin
select @count = @count + 1
select @var1 = col1, @var2 = col2, @var3 = col3
from mytable where id = @count
exec myproc @var1, @var2, @var3
end
I'm wondering if they have come up with a better way in sql 2005, especially in the case of text data where the above example will not work?
February 5, 2008 at 2:44 am
You may be able to write something like a function which you could then call in the CLR (now available in SQL 2005) for each datarow
February 5, 2008 at 5:05 am
If the procedure actions can be specified in a (table returning) function, you may find that a cross/outer apply will do the trick. In this instance, the function is called once for each row in the calling query, and the function return columns can be included in the output as if a table join was used.
February 5, 2008 at 5:46 am
You're asking for improved methods of Row-By-Agonizing-Row (RBAR) processing. That type of processing is inherently slow because TSQL is set up & optimized for set based processing. You're almost guaranteed to have to set up a cursor in order to process single rows out of a dataset. Better to reset your approach and think about a method of operating on the data based on the set. Otherwise, you'd be better off using client tools. VB, C#, whatever, are much better suited to that type of processing than TSQL is.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 5, 2008 at 5:56 am
FWIW, I have not seen performance problems using either CROSS or OUTER APPLY, nothing like would be seen from other RBAR approaches. Maybe I have just been lucky.
February 5, 2008 at 6:32 am
I've stayed away from using functions, but the CROSS & OUTER apply statements work incredibly well with set based SELECT statements applied to the rows returned by the rest of the select statement. So, yes, I agree.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 5, 2008 at 12:02 pm
CROSS or OUTER APPLY with TVF's is basically a correlated sub-query in disguise. So - not incredibly bad if the sets are small, but still not as good as a derived table approach ni most cases.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 5, 2008 at 12:32 pm
Tom Leykis (2/4/2008)
This is how I've always done it in the past:while @count < 10
begin
select @count = @count + 1
select @var1 = col1, @var2 = col2, @var3 = col3
from mytable where id = @count
exec myproc @var1, @var2, @var3
end
Assuming that ID is sequential without gaps. That is still the approach.
2005 gives you some new ways to create the table with the parameters.
select identity(INT,1,1) AS ID, col1,col2,col3
into #myTable
from col1,col2,col3
from mytable
-- Then use #myTable
You can also do
declare @myTable table as (ID INT IDENTITY(1,1) PRIMARY KEY, col1 ...)
insert into @myTable(col1,col2,col3)
select col1,col2,col3
...
February 5, 2008 at 12:47 pm
Tom Leykis (2/4/2008)
This is how I've always done it in the past:while @count < 10
begin
select @count = @count + 1
select @var1 = col1, @var2 = col2, @var3 = col3
from mytable where id = @count
exec myproc @var1, @var2, @var3
end
I'm wondering if they have come up with a better way in sql 2005, especially in the case of text data where the above example will not work?
That is typically the way I handle such things, as well, but only on small sets of data. Without knowing anything about the application this question may be meaningless, but would it be possible to rewrite the stored procedure to read the records directly from mytable and process the whole thing as a set? That would speed things up considerably.
EDIT: To answer your question, SQL 2005 does not allow you to pass a table as a param but I believe that feature is in SQL 2008. I read an article on it not too long ago, don't recall if it was on this site or not. If I find it I'll post a link.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply