Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2002
    Posts
    17

    Question Unanswered: variable in CURSOR sql statement (was "Please help me")

    Hi All,

    What i am trying to do is concatenate variable "@Where" with CURSOR sql statement,inside a procedure . But, it doesn't seem to get the value for
    the @Where. (I tried with DEBUGGING option of Query Analyzer also).

    =============================================
    SET @Where = ''
    if IsNull(@Input1,NULL) <> NULL
    Set @Where = @Where + " AND studentid='" + @input1 +"'"

    if isnull(@Input2,NULL) <> NULL
    Set @Where = @Where + " AND teacherid =' " + @Input2 +"'"

    DECLARE curs1 CURSOR SCROLL
    FOR SELECT
    firstname
    FROM
    school
    WHERE
    school ='ABC' + @where
    =============================================
    Please check my SQL Above and Could somebody tell me how can I attach the VARIABLE with CURSOR sql statement ?

    Thanks !

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    I don't think @where is set; (from BOL) "To determine if an expression is NULL, use IS NULL or IS NOT NULL rather than comparison operators (such as = or !=). Comparison operators return UNKNOWN if either or both arguments are NULL.".

    For debugging it might help printing or selecting @where before the actual cursor:
    select '@where:', @where

  3. #3
    Join Date
    Feb 2002
    Posts
    17
    Hi Kaiowas,

    As u said, i changed to use "IS NOT NULL" & added "select '@where:', @where" to display the @where values. it is displaying like this :

    @where : AND studentid='A01' AND teacherid='T12'

    which is correct for the below code.
    =============================================
    DECLARE @where nvarchar(100)
    SET @where = ''
    if @Input1 IS NOT NULL
    Set @where = @where + " AND studentid='" + @input1 +"'"

    if @Input2 IS NOT NULL
    Set @where = @where + " AND teacherid =' " + @Input2 +"'"

    DECLARE curs1 CURSOR SCROLL
    FOR SELECT
    firstname
    FROM
    school
    WHERE
    school ='ABC' + @where
    =============================================

    But, when it come to DECLARE CURSOR part somehow, its not attaching "@where" value to it..? how to solve it ...

    please help me..(:

  4. #4
    Join Date
    Feb 2004
    Posts
    492
    Your @where does not contain a single value but an expression which needs to be handled differently. The sql-statement now selects all rows where the value of school equals "ABC AND studentid='A01' AND teacherid='T12'".

    So it looks like you're actually trying to do some dynamic sql. There's two ways to deal with it: think of another way or do it the dynamic-way. Since dynamic queries are less predicatable and harder to debug it's preferred to avoid dynamic sql.

    I think in your case dynamic sql can be avoided by thinking up another sql-statement that works in either case, unless there's more to it that I do not know.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •