May 29, 2007 at 1:30 am
Hi,
If I have a SP that return some integer value via output parmeter
as it retrieve a record and update that record (hence I can't use a function)
Example: exec get_max_id 'test_id', @w_return_value output
I am trying to tune a cursor script like below
declare cur_result for
select xx, yy, zzz from testing
while(xxx)
begin
exec get_max_id 'test_id', @w_return_value output
insert test_tbl(@w_return_value, xxx, yyy)
fetch nextf rom cur_result into xxx, yyyy
end
into
insert into test_tbl
select get_max_id 'test_id',@w_result_value output, xxx, yyyy
from testing
Is there any optimize way to improve the above statement so that
I can eliminate the cursor/loop and use a single select statement
alone.
Thank you
May 31, 2007 at 10:05 am
If I read correctly what you didn't write, I think you're trying to add rows with ids N+1, N+2, etc to a table with N records in it.
You can do this automatically by adding Identity(1,1) to the definition of column test_id.
Your insert statement then becomes:
Insert into test_tbl(xxx, yyy) -- adjust for your column names
Select xx, yy
From testing
If you don't want to use the Identity clause,
You could try
-- adjust for your column names
-- assumes testing.xx and testing.yy are the alphanumeric keys; if not, adjust to your needs.
-- assumes the value combinations of xx and yy are unique
Declare @n int
Select @n = count(*) from test_tbl
Insert Into test_tbl(test_id, xxx, yyy)
Select @n + rank, xx, yy
-- select source data, adding a rownumber
From (
Select count(*) as rank, xx, yy
from testing t1, testing t2
where t1.xx + t1.yy >= t2.xx + t2.yy
group by t1.xx, t1.yy
order by rank) newrecs
I don't have access to a SQL/server at the moment, so I wasn't able to test the code
look at http://support.microsoft.com/kb/186133/en-us for hints on how to number a resultset in SQL/Server 2005.
Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke)
June 1, 2007 at 1:15 am
Hi Otto,
First, thank alot for your advise, Yes, you get what I wanted.
However, I am using SQL Server 2000 and can't
change my table structure to use identity type column.
For my test_id, I need to retrieve it from a table
using a SP to retrieve the max ID from that table.
I am hopping to find a way to implement this
SP(retrieving ID) into a simple SELECT statement
instead of retrieving a new ID, then insert.
Anyway, you give me some insight on how to
improve my sql statement.
June 1, 2007 at 2:02 am
Two things I don't understand.
1. "However, I am using SQL Server 2000 and can't change my table structure to use identity type column."
SQL/Server 2000 supports the identity clause. The easiest way to change the definition on an existing table is to use Enterprise manager which will preserve any data in the table.
2. Why do you need to use a SP to obtain the maximum id from your table? Is deriving the max id more complicated than a select max(test_id) from xxx?
I was trying to avoid using a SP, as this will simplify the SQL you need.
Can you post the code for the SP?
Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke)
June 1, 2007 at 5:57 am
Hi,
Thank for your help.
First,any changes to the SP now or table structure, lot of existing SP have to
be change. We don't have the manpower to do it and test it.
Plus, it is on some production system, I am only doing some tunning.
June 1, 2007 at 7:33 am
One possible way to do this would be to add / modify your insert or instead of insert triggers to check for a null value for that column and then call your SP from the trigger to get your new id value.
June 1, 2007 at 7:41 am
Hi,
Thank for your advise.
But my SP is in a transaction, any rollback, trigger will rollback/stop too.
hence it might not achieve what I want.
June 1, 2007 at 8:07 am
Your SP should be wrapped in a transaction if it's updating data in a table. Why would this be a bad thing?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply