Cursor issues | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Cursor issues

I can’t get this one to loop through the ziptrendindex table and replace the variable name. I was able to create a table using the exact syntax, but can’t seem to use it to create indexes or procs. any ideas
declare @countyname varchar (200)
declare @sql varchar(8000) declare county_name cursor for
select distinct county from ZipTrendIndex open county_name
fetch next from county_name
into @countyname WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = ‘CREATE PROCEDURE dbo.getUpdRecords AS SET NOCOUNT ON
Declare @PropertyID bigint Declare @PropertyIDBuffer bigint Declare @AVMValue float Declare @Confidence float Declare @a bigint Declare @b smallint Set @a = 1 Set @b = 0 While (@a) > 0 Begin Select top 1 @PropertyIDBuffer = sa_property_id from county_name (NOLOCK) Where replyavm is null If @PropertyIDBuffer is not null Or @PropertyIDBuffer > 0 Begin Set @b = 0 DECLARE Property_Details CURSOR FOR Select top 50 sa_property_id From county_name (NOLOCK) Where replyavm is null OPEN Property_Details FETCH NEXT FROM Property_Details INTO @PropertyID WHILE @@FETCH_STATUS = 0 BEGIN Set @AVMValue = 0 Set @Confidence = 0 exec usp_ReplyAVMCalculationcounty_name @PropertyID, @AVMValue output, @Confidence output If @AVMValue is Null Begin Set @AVMValue = 0 Set @Confidence = 0 End Update county_name Set ReplyAVM = @AVMValue, ReplyScore = @Confidence Where sa_property_id = @PropertyID FETCH NEXT FROM Property_Details INTO @PropertyID END CLOSE Property_Details DEALLOCATE Property_Details End Else Set @a = 0 End SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

exec (@sql)
end
fetch next from county_name into @countyname
end
CLOSE county_name
DEALLOCATE county_name
Check out the chapter about Dynamic SQL and Cursors here:http://www.sommarskog.se/dynamic_sql.html
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
Don’t quite understand what are trying to achieve. – You declare a cursor to fetch county_name into @county_name and it is never used at all. – You are dynamically create a stored procedure getUpdRecords and it is also never used. KH
]]>