Declaring cursor using contacented strings (user define where clause)

• Takauma

  Say Hey Kid

  Points: 697

  More actions

  June 29, 2007 at 9:51 am

  #179468

   

  We are migrating a db from Sybase SQLAny to SS2K5.  One SQLAny developer used the the following technique integrate a "WhereClause" string parameter into a procedure cursor.   Here's the gist:

  CREATE PROCEDURE DBO.CancelProcess @WhereClause varchar(max)  as.... 

  declare @Select1 varchar(max)     

  set @Select1=' SELECT  Projects.ProjectID, Projects.TargetStartDate,  Projects.TargetFinishDate, Projects.StatusCode

  from dbo.Projects  WHERE ' + @WhereClause

        begin

          declare curCancel insensitive scroll cursor for @Select1

  .....

  The error we get is:

  Incorrect syntax near '@Select1'

  I've tried a few techniques that I know, like declaring the cursor in a Execute statement:

  set @Select1= 'SET @curCancel = CURSOR scroll FOR SELECT Projects.ProjectID, Projects.TargetStartDate  from DBO.Projects WHERE ' + @WhereClause ;

  Execute @Select1

  I'm not getting anywhere.  What am I missing?

  Takauma

• Takauma

  Say Hey Kid

  Points: 697

  More actions

  June 29, 2007 at 12:55 pm

  #716283

  Cone on out there!  Surely there's a way to pass a whereclause into a cursor declaration!

  Takauma