Click here to Skip to main content
15,563,248 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I have encounter a tricky problem today. I am trying to do the following....

1..Depending on a variable depends on what SQL statement I run
2..Where the problem is that I want to change the Select statement within the cursor
3..I am running a function

An example is as follows

SQL
DECLARE dupCursor CURSOR FAST_FORWARD

FOR SELECT IdNum, Name, Address, AgentName FROM vwEmployee where IdNum = @AidNum 

OPEN dupCursor
.
.
.
How can you put something like this in

SQL
DECLARE @sql VARCHAR(MAX)

IF IdNum = 0
SET @SQL = 'SELECT IdNum, IdNum2, Name, Address, AgentName FROM vwPRINTValuationNotice where IdNum = @AidNum
 
ELSE SET @SQL = 'SELECT IdNum, Name, Address, AgentName FROM vwPRINTValuationNotice where IdNum2 = @AidNum 


DECLARE dupCursor CURSOR FAST_FORWARD

FOR @sql

OPEN dupCursor
.
.
.
Posted
Updated 19-Feb-13 4:19am
v2
Comments
Central_IT 19-Feb-13 10:56am    
When I run the followiing I get the following error message

Incorrect syntax near '@SQL'. Expecting SELECT, or ')'

1 solution

You will need to include the cursor declaration in the @SQL dynamic variable e.g.
SQL
DECLARE @SQL NVARCHAR(1024) -- or what ever the size
SET @SQL = 'DECLARE dupCursor CURSOR FOR SELECT IdNum, IdNum2, Name, Address, AgentName FROM vwPRINTValuationNotice where '

IF IdNum = 0
     SET @SQL = @SQL + 'IdNum = ' + @AidNum
ELSE 
     SET @SQL = SQL + 'IdNum2 = ' + @AidNum


Then use EXEC to execute the SQL
SQL
EXEC sp_executesql @SQL
OPEN dupCursor
FETCH NEXT FROM dupCursor INTO @NEWREF
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @REF + ' ' + @NEWREF
	FETCH NEXT FROM dupCursor INTO @NEWREF
END
CLOSE dupCursor
DEALLOCATE dupCursor		
FETCH NEXT FROM CUR1 INTO @REF
 
Share this answer
 
v3
Comments
Central_IT 19-Feb-13 10:57am    
When I run the followiing I get the following error message

Incorrect syntax near '@SQL'. Expecting SELECT, or ')'
CHill60 19-Feb-13 11:27am    
It's a SQL version problem (amongst other things) - bear with me
CHill60 19-Feb-13 11:40am    
Ok - sorry about that. I've updated my solution with more detail and corrected a couple of points - the @SQL needs to be an Nvarchar, the brackets() on the EXEC shouldn't be there.
I've got this working fine now in SQL2005

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900