August 7, 2010 at 5:23 am
Hey All,
I am trying to convert cursor based stored proc in to set based simple statements stored proc. As this stored proc has created alot of performance issues. I am confuse now as I spent my most of time creating this stored proc. Please advise how can I convert this stored proc into set base simple statment. plzz it's very urgent......
Thanks in advance.
--
Declare cur1 cursor for
Select Attribute_Name,Attribute_Id
From dbo.Member_Attribute_Id
Where Table_Name ='Member' and Logging_Ind =1
declare @Attribute_Name varchar(200),
@Attribute_Id bigint,
@sqlstring nvarchar(4000),
@sqlInsertStr nvarchar(1000),
@sqlSelectStr nvarchar(3000)
open cur1
Fetch next from cur1 into @Attribute_Name, @Attribute_Id
while @@fetch_status = 0
begin
set @Attribute_Name = ltrim(rtrim(@attribute_name))
set @sqlInsertStr = 'Insert Into dbo.Member_Attribute_Update_Log(Attribute_Id, Subscriber_Id,Patient_Id, Prev_Value, Curr_Value, Created_Datetime, Created_Date)'
-- set @sqlSelectStr = 'Select ' + cast(@Attribute_Id as nvarchar(10)) + ', A.Subscriber_Id, A.Patient_Id, B.' + @Attribute_Name + ' as Prev_Value, A.' + @Attribute_Name + ' as Curr_Value, ' + '''' + cast(getdate() as nvarchar(50)) + '''' + ', ' + '''' + cast(getdate() as nvarchar(50))+ '''' + ' From Member A Left Join Stage_Member B on A.Subscriber_Id = B.Subscriber_Id Where A.'+ @Attribute_Name + '<> B.' + @Attribute_Name + ' or B.'+ @Attribute_Name + ' is null '
set @sqlSelectStr = 'Select ' + cast(@Attribute_Id as nvarchar(10)) + ', A.Subscriber_Id, A.Patient_Id, B.' + @Attribute_Name + ' as Prev_Value, A.' + @Attribute_Name + ' as Curr_Value, ' + '''' + cast(getdate() as nvarchar(50)) + '''' + ', ' + '''' + cast(getdate() as nvarchar(50))+ '''' + ' From Member A Left Join Stage_Member B on A.Subscriber_Id = B.Subscriber_Id
Where case (SELECT distinct(data_type) from information_schema.columns WHERE table_name = ''Stage_Member'' AND column_name = ' + '''' + @Attribute_Name + '''' + ')
when ''float'' then CONVERT(varchar(100), CAST(isnull(A.' + @Attribute_Name + ',0) AS decimal(38,8)))
when ''char'' then cast(isnull(A.' + @Attribute_Name + ','''') as varchar)
when ''nchar'' then cast(isnull(A.' + @Attribute_Name + ','''') as varchar)
when ''varchar'' then cast(isnull(A.' + @Attribute_Name + ','''') as varchar)
when ''nvarchar'' then cast(isnull(B.' + @Attribute_Name + ','''') as varchar)
when ''datetime''then convert(varchar,isnull(A.' + @Attribute_Name + ',''1900-01-01''),121)
when ''smalldatetime''then convert(varchar,isnull(A.' + @Attribute_Name + ',''1900-01-01''),121)
when ''bit'' then cast(isnull(A.' + @Attribute_Name + ',0 )as varchar)
when ''money'' then convert(varchar,isnull(A.' + @Attribute_Name + ',0),2)
when ''decimal'' then convert(varchar,isnull(A.' + @Attribute_Name + ',0),2)
else cast(isnull(A.' + @Attribute_Name + ',0)as varchar)
end
<>
case (SELECT distinct(data_type) from information_schema.columns WHERE table_name = ''Stage_Member'' AND column_name = ' + '''' + @Attribute_Name + '''' + ')
when ''float'' then CONVERT(varchar(100), CAST(isnull(B.' + @Attribute_Name + ',0) AS decimal(38,8)))
when ''char'' then cast(isnull(B.' + @Attribute_Name + ','''') as varchar)
when ''nchar'' then cast(isnull(B.' + @Attribute_Name + ','''') as varchar)
when ''varchar'' then cast(isnull(B.' + @Attribute_Name + ','''') as varchar)
when ''nvarchar'' then cast(isnull(B.' + @Attribute_Name + ','''') as varchar)
when ''datetime''then convert(varchar,isnull(B.' + @Attribute_Name + ',''1900-01-01''),121)
when ''smalldatetime''then convert(varchar,isnull(B.' + @Attribute_Name + ',''1900-01-01''),121)
when ''bit'' then cast(isnull(B.' + @Attribute_Name + ',0 )as varchar)
when ''money'' then convert(varchar,isnull(B.' + @Attribute_Name + ',0),2)
when ''decimal'' then convert(varchar,isnull(B.' + @Attribute_Name + ',0),2)
else cast(isnull(B.' + @Attribute_Name + ',0)as varchar)
end'
set @sqlstring = @sqlInsertStr + @sqlSelectStr
exec sp_executeSQL @sqlstring;
Print @sqlstring
Fetch next from cur1 into @Attribute_Name, @Attribute_Id
end
close cur1
deallocate cur1
==
Thanks in Advance
Anil Inampudi
August 7, 2010 at 7:04 pm
Okay, this time I'm going to insist.
You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply