Transact-SQL cursors

• HS2

  SSC Journeyman

  Points: 80

  More actions

  May 10, 2002 at 4:06 am

  #78407

  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

• Antares686

  SSC Guru

  Points: 125444

  More actions

  May 10, 2002 at 9:43 am

  #428891

  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)

• HS2

  SSC Journeyman

  Points: 80

  More actions

  May 10, 2002 at 10:14 am

  #428893

  Thanks for your sugestions, I've tried them and they work.

  Cheers for your help.

  James