SQL using a string executed as cursor

Question

I want to use a variable I pass to a stored procedure to be used as table name in my query to be used in cursor to be used to send email. My snippet sample is below:

--DECLARE @getemail CURSOR
--SET @getemail = CURSOR FOR
DECLARE @getemail CURSOR FOR 
SET @recipientquery =  'SELECT * FROM '+@tableA+''
EXEC sys.sp_executesql @recipientquery

Questions is why I am getting error in the for part saying:

Incorrect syntax

What is the correct syntax to use when using a string query after for in cursor?

Roxana Sh · Accepted Answer · 2019-07-31 05:31:36Z

1

It would be :

set @recipientquery = 'Declare  getemail CURSOR FOR SELECT * FROM'+@tableA

exec sp_executesql @recipientquery


OPEN getemail

answered Jul 31, 2019 at 5:31

Roxana Sh

2921 gold badge3 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

guradio Over a year ago

i see let me confirm ill be back here asap

DarkRob · Accepted Answer · 2019-07-31 12:01:21Z

0

You have incorrect syntax near after for statement.

Please find this link for syntax and more info link.

DECLARE vend_cursor CURSOR  
    FOR SELECT * FROM Purchasing.Vendor  
OPEN vend_cursor  
FETCH NEXT FROM vend_cursor;

Or may be this is the issue you forgot comment your declare cursor which is not used over there

--DECLARE @getemail CURSOR FOR    --- (here cursor for what...? )
SET @recipientquery =  'SELECT * FROM '+@tableA+''
EXEC sys.sp_executesql @recipientquery

Or you may use dynamic sql to create your query

Create table demotable  ( id int, name nvarchar(max) )

declare @tableA nvarchar(max)
set @tableA = 'demotable'

declare @recipientquery nvarchar(max)   
SET @recipientquery =  'DECLARE getemail CURSOR FOR  
                        SELECT * FROM '+@tableA+''
EXEC sys.sp_executesql @recipientquery
open getemail


close getemail
deallocate getemail

If this didn't work then please post your whole query, to get the exact scenario.

edited Jul 31, 2019 at 12:01

answered Jul 31, 2019 at 5:12

DarkRob

3,8231 gold badge12 silver badges28 bronze badges

8 Comments

guradio Over a year ago

that is the whole query also you sample is different from my sample just by looking. I want to use a query string not a direct select query SET @recipientquery = 'SELECT * FROM '+@tableA+'' EXEC sys.sp_executesql @recipientquery

DarkRob Over a year ago

how you are getting this value of @tableA, cause if you are not selecting any row from table then there is no need to use cursor.cursor is use to iterate loop and there is no loop seen in your query.

guradio Over a year ago

I want to use a variable I pass to SP to be used as table name in my query to be used in cursor to be used to send email.

guradio Over a year ago

i need the cursor..your update does not solve my issue

DarkRob Over a year ago

then you need to apply cursor on calling this stored procedure, here you are passing just one @tableA directly, so no need to call cursor for that.

|

Collectives™ on Stack Overflow

SQL using a string executed as cursor

2 Answers 2

1 Comment

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related