?
Solved

declaring cursor from a variable sql statement

Posted on 2004-11-29
4
Medium Priority
?
223 Views
Last Modified: 2012-06-27
I need to know if there is a way to declare a cursor from a variable sql statement, such as:

DECLARE MainCursor CURSOR FOR
SELECT *
FROM < a table name that can be variable >

I was trying something like this:

DECLARE @TableName AS nvarchar(50)

SELECT @TableName = Name
FROM dbo.MyTables
WHERE ID = @TableID

EXEC('INSERT INTO #temp (fields1 integer, fields2 nvarchar(50), ...)
         SELECT *
         FROM ' + @TableName)

DECLARE MainCursor CURSOR FOR
SELECT *
FROM #temp

But the problems is that I have to create the #temp table with the proper fields from a... variable table!!!
0
Comment
Question by:fbenve
1 Comment
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 12698166
You could use dynamic SQL, EXEC(...), as shown above, to create the table and declare the cursor.  However, AFAIK you cannot use dynamic SQL for a FETCH.  For the FETCH you will need to know specifically what columns are to be fetched.  You could use sp_executeSQL to get a variable set of columns from the table one row at a time, but that would be very slow.
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question