Set based alternative of cursors?

Question

Set based alternative of cursors?

aushami

Grasshopper

Points: 11
More actions
June 13, 2008 at 10:06 am

#192680

Hey all,
I am spinning my wheels to get this thing resolved unable to do that. How can i convert this code into set based or without cursors code.
declare cursor2 cursor for
select File_Name, File_Title_Txt, Upd_Mtch_tx, UpdateInd, AddInd, DeleteInd, AnyUpdateInd from File_Object_Table
where TableName = ''' + @Table + '''
and Criteria = ''' + @Criteria + '''
and File_Title_Txt = ''' + @Title + '''
declare @File_Name varchar(50)
declare @File_Var1 varchar(50)
declare @File_Var2 varchar(500)
declare @File_Var3 bit
declare @File_Var4 bit
declare @File_Var5e bit
declare @File_Var6 bit
open cursor2
fetch next from cursor2 into @File_Name, @File_Var1, @ExtraUpdateMatch, @PullUpdate, @File_Var4, @File_Var5e, @File_Var6
WHILE (@@fetch_status = 0)
begin
if @File_Var6 = 1
begin
exec (''INSERT ConfirmTable SELECT ''''' + @fileId + ''''', A.UnitNbr, ''''' + @Title + ''''', '' + @File_Var1 + '', A.'' + @File_Name + '', B.'' + @File_Name + '', ''''U''''
from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.ActivityStatusCd = ''''U'''' '' + @File_Var2+ ''
where A.ActivityStatusCd = ''''O'''' and B.ActivityStatusCd = ''''U'''''')
end
fetch next from mtcursor2 into @File_Name, @File_Var1, @File_Var2, @File_Var3, @File_Var4, @File_Var5e, @File_Var6
end
close cursor2
deallocate cursor2

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

I'm getting bug-eyed just trying to interpret the statement with the huge number of quote marks. Is there a reason to have 5 quote marks in a row or more? I can't see why that's needed here, but I may not have all the info on which to base that statement. I need a clearer picture of what this code is supposed to accomplish.

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

noeld SSC Guru Points: 96590 More actions · Answer 2

The number of quotes does not makes sense. In addition you are using Temptables that do not seem to be declare elsewhere in the script ?

* Noel

doosra.lamhaa Grasshopper Points: 19 More actions · Answer 3

Sorry for that many qoutes.

Resending it with more clear picture and less number of qoutes:)

Create table #tempTable (ASC varchar, AMRN int, UnitNumber varchar(20))

declare cursor2 cursor for

select File_Name, File_Title_Txt, Upd_Mtch_tx, UpdateInd, AddInd, DeleteInd, AnyUpdateInd from File_Object_Table1

where TableName = ''' + @Table + '''

and Criteria = ''' + @Criteria + '''

and File_Title_Txt = ''' + @Title + '''

order by File_Number

declare @File_Name varchar(50)

declare @File_Var1 varchar(50)

declare @File_Var2 varchar(500)

declare @File_Var3 bit

declare @File_Var4 bit

declare @File_Var5 bit

declare @File_Var6 bit

open cursor2

fetch next from cursor2 into @File_Name, @File_Var1, @File_Var2, @File_Var3, @File_Var4, @File_Var5, @File_Var6

WHILE (@@fetch_status = 0)

begin

if substring(@File_Var1,1,1) = ''#''

set @File_Var1 = substring(@File_Var1,2,len(@File_Var1) - 1)

else

set @File_Var1 = ' + @File_Var1 + '

if @File_Var6 = 1

begin

exec ('INSERT File_Object_Table2 SELECT ' + @FileId + ', A.UnitNumber, ' + @holdTitle + ', ' + @File_Var1 + ', A.' + @File_Name + ', B.' + @File_Name + ', 'U'

from #tempTable A left join #tempTable B on B.UnitNumber = A.UnitNumber and B.ASC = 'U' '' + @File_Var2 + ''

where A.ASC = 'O' and B.ASC = 'U''')

end

fetch next from cursor2 into @File_Name, @File_Var1, @File_Var2, @File_Var3, @File_Var4, @File_Var5, @File_Var6

end

close cursor2

deallocate cursor2

Please help its gotta be urgent.

Thanks for help tho.

John.Sansom SSCertifiable Points: 7335 More actions · Answer 4

Hi,

Rather than using a cursor, you can insert your data into an ordered table by using a identity column. Then just create a typical loop data structure to sequence through each record in the set.

John Sansom (@sqlBrit) | www.johnsansom.com

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

Basically, you cannot convert it into true setbased code. Sure, you can probably get rid of all the looping by concatenating all of the commands into one huge variable and executing it, but they will still be individual commands. Each command might be setbased, but you're already doing that correctly. And, as I just showed in the following discussion, turning this code into a While Loop is a total waste of time...

http://www.sqlservercentral.com/Forums/Topic247716-272-4.aspx#BM517117

You can come close to set based using a technique like the following. It works especially well in 2k5 because of the wonderful VARCHAR(MAX) data type... you can do the same thing in SQL Server 7 or 2000 if you're careful about not going over the 8k limit for each variable you may care to declare...

--===== Identify the database to use

USE AdventureWorks

--===== Declare a storeage variable for all of our SQL Commands

DECLARE @sql VARCHAR(MAX)

--===== Build a SELECT * for every table in the database

SELECT @sql = COALESCE(@SQL+CHAR(13),'')

+ 'SELECT * FROM '+Table_Schema+'.'+Table_Name

FROM Information_Schema.Tables

WHERE Table_Type = 'Base Table'

ORDER BY Table_Schema, Table_Name

--===== Execute all of the commands

PRINT @sql

EXEC (@SQL)

I will admit... the equivalent cursor would make for a lot more code...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)