Filter Cursor With Dynamic SQL

I should know this, but having a brain cramp.

I am trying to filter the data a cursor has to go through based on dynamic sql, but can't seem to get the syntax correctly.

Basically

'tmp is filter clause brought into the stored proc.

Declare @.tmp varchar(2000)

Declare my CURSOR FAST_FORWARD

FOR

Select * from table where + @.tmp

OPEN my

...

...

...

Any ideas?

declare a table variable to hold the data
declare @.SqlStr varchar(8000)

set @.SQLStr = 'Select * from table where '+ @.tmp

insert into @.tableVariable (Col1, Col2,...)
exec (@.SqlStr)

declare my CURSOR FAST_FORWARD
FOR
select * from @.tableVariable

rest of your should work...

|||

Also, be sure to declare the cursor as LOCAL unless you need it to be global...

declare my CURSOR LOCAL FAST_FORWARD

Better yet, consider not using a cursor if possible Tell us more about what you were doing and perhaps we can help.

|||

Here's the skinny.

I created a view to consolidate a bunch of data across tables. Once this table is consolidated I need to perform calculations on a row by row basis as a previous row's calculation is used in future rows based upon business rules.

I have written this logic using an inline view, TSQL Stored Proc (utilizing cursors), CLR Stored Proc, and traditional .NET business logic. I found that of the four options TSQL Stored Proc and CLR Stored Proc perform the best when running remotely. I now am trying to filter down the result set of the TSQL Stored Proc based upon parameters a user will provide. After following Bashan's advice I am able to filter it down and the speed is great; however, if I run it without filtering down the results then the logic doubled the processing time of the stored proc.

I would love to create this logic without using cursors but not sure how to perform row by row complex logic inside the database without using them.

Thanks for your help. I look forward to receiving any advice.

|||

The question is always "what is the complex logic"? Usually, the complex logic can be turned into where clauses, subqueries, and occasionally a function or two. It is also possible that the cursor is the best way do things if you need ordered data: http://blogs.sqlservercentral.com/blogs/amachanic/archive/2006/02/28/508.aspx

Using row_number() you can often do the kind of rollups based on ordering and previous values, but it can also be hard to do. So without much more work/knowledge of your system, if the cursor works I would go with that. But if you want some more advice/ideas, give us the gist of the problem and we can take a look and see what you might could change.