May 10, 2002 at 4:06 am
I am having major problems trying to use Transact-SQL cursors. Things that I can easily code using ADO recordsers I don't think can be done using Transact-SQL cursors.
The problems are:-
1. I can't seem to use variable table names and fields within the select
statement when declaring a Transact-SQL cursor e.g. the following code
errors:-
Declare @TableName varchar(20)
Declare @Field1 varchar(20), @Field2 varchar(20), @Field3 varchar(20)
SET @TableName = 'EMPLOYEES'
SET @Field1 = 'DEPARTMENT'
SET @Field2 = 'NAME'
SET @Field3 = 'SALARY'
DECLARE NewCursor CURSOR FOR
SELECT @Field1, @Field2, @Field3
FROM @TableName
WHERE @Field1 'Marketing'
OPEN CURSOR.......
or even
DECLARE NewCursor CURSOR FOR
EXEC ('SELECT DEPARTMENT, NAME, SALARY FROM EMPLOYEES')
.......
2. On fetching rows from a cursor, I think that every value has to be placed into a
variable. This means you are forced to have a defined number of fields
within your cursor select statement.
e.g.
DECLARE NewCursor CURSOR FOR
SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT = 'Marketing'
......
FETCH NEXT FROM NewCursor INTO @variable1, @variable2, @variable3
......
So if the employee table has more than 3 fields this code will fail.
If anyone has any ideas please let me know.
Many Thanks
James
May 10, 2002 at 9:43 am
1. The appraoch in SQL is in fact the same as ADO except some of the work is being done by the ADO library where SQL is a simpler and purer system.
Meaning there is no code to do this for you there in most case.
However for what you are trying to do you will need to use Dynamic SQL, this is where you store the code as a stringin a variable then call execute to run the string as code.
Ex.
Declare @TableName varchar(20)
Declare @Field1 varchar(20), @Field2 varchar(20), @Field3 varchar(20)
DECLARE @SQLStr varchar(8000)
SET @TableName = 'EMPLOYEES'
SET @Field1 = 'DEPARTMENT'
SET @Field2 = 'NAME'
SET @Field3 = 'SALARY'
SET @SQLSTR = '
DECLARE NewCursor CURSOR FOR
SELECT ' + @Field1 + ',' + @Field2 + ',' + @Field3 + '
FROM ' + @TableName + '
WHERE ' + @Field1 + ' = ''Marketing''
OPEN CURSOR.......
....ALL CODE THRU CLOSE AND DELETE OF CURSOR'
EXEC(@SQLStr)
Note: You have to double all natural single quotes like I did with 'Marketing'. So the string gets built properly.
2. Right SQL does require you define the columns when the number of variables does not match.
However in ADO this is done behind the scenes for you so you do not see this.
So four items automatically generate four columns in the recordset.
In pure SQL you have to do this yourself.
Hope this helps.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 10, 2002 at 10:14 am
Thanks for your sugestions, I've tried them and they work.
Cheers for your help.
James
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply