CURSOR trouble (:

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
*
FROM
school
WHERE
school ='ABC' + @.where
=============================================

It gives me all the Records inside the SCHOOL='ABC' ...

Please check my SQL Above and Could somebody tell me , how can I attach the VARIABLE with CURSOR sql statement ?

Please advice me..(:

Thanks !Well, looks like another day is over, so it is time to repeat the (same old) basic thing about SQL again.

You can NOT just put up part of a SQL Statement into a variable and then think the SQL compiler acutlly magically produces dynamic SQL out of it.

That simple.

In your example:

::DECLARE curs1 CURSOR SCROLL
::FOR SELECT
::*
::FROM
::school
::WHERE
::school ='ABC' + @.where

@.where will NOT be appened to the SQL statement. It will be part of the right part of the comparison.

So, if @.where consists of (example)

::AND studentid='22'

it will check whether schoon equals "ABC AND studentid='22'".

That simple.

If you want to execute a dynamic SQL thing, then do so. There are means for this. But otherwise, variables are variables, not some "post compiler magic thing that happens when you dance around the computer at night singing magic chants".

Interesting enough you are not alone. Lots of people think like this - as I said in the beginning, some typical (beginner) error like this pops up every second day. Most, though, stumble opver it before they get into using / abusing cursors.|||

SELECT *
FROM
 school
WHERE
 school ='ABC'
 AND
 ( @.Input1 IS NULL OR @.Input1 = StudentID )
 AND
 ( @.Input2 IS NULL OR @.Input2 = teacherID )

>>using / abusing cursors

So true.... many developers often try to solve T-SQL problems by using cursors, similar to the way they might program in other environments—working with one record at a time in sequential order. But this approach doesn't exploit the power of SQL Servers "engine", which is optimized for set-based processing. Cursors usually require considerably more code than their equivalent set-based solutions and are much less efficient.

If a cursor is the first thing that you think of when you face a T-SQL problem, you have some unlearning to do—you need to start thinking "set-based.

http://www.windowsitpro.com/SQLServer/Article/ArticleID/22431/22431.html|||Christmas is supposed to be about giving so here we go. Some good advice already provided (though not entirely correct). First of all cursor or no cursor and trying to concatenate a where clause or not why are you doing this: if IsNull(@.Input1,NULL) <> NULL ?

For one thing when ever evaluating NULL always use: IF @.Input1 IS NULL or in your specific example IF @.Input1 IS NOT NULL

As for using the ISNULL function I suggest that you use this only when you suspect that the value is null or perhaps in your case an empty string in which case thew correct syntax would be: IF ISNULL(@.Input1, '') <> ''

Also, regardless of if you really need a cursor or not from the example you have provided you can generate the select as follows:

SELECT * FROM school
WHERE school = 'ABC' AND studentid LIKE ISNULL(@.Input1, '') + '%' AND teacherid LIKE ISNULL(@.Input2, '') + '%' ...

Now if you still really feel that you have to concatenate a where clause it can in fact be done but it is ugly! It works like this (but is only viable for returning a result set, in other words you cannot use it for a CURSOR!):

EXEC('SELECT * FROM school WHERE ' + @.Where)

Last but not least, as one of the other respondents suggested CURSOR's should be avoided at all costs and as such there are a number of other ways to apply updates to a group of records with some pretty flexible conditional logic.

Step 1: select the target records into a temporary table i.e.

SELECT [primary_key_field] INTO #target
FROM school WHERE (school = 'ABC')
AND (studentid LIKE ISNULL(@.Input1, '') + '%')
AND (teacherid LIKE ISNULL(@.Input2, '') + '%') ...

Now that you have the keyset available you can do a simple update to all the records in the resultset as follows:

UDPATE school
SET [whatever_field] = [whatever_value]
FROM school, #target
WHERE school.[primary_key_field] = #target.[primary_key_field]

For more sophisitcated updates you can use a flavour of the CASE in sql such as

SET [whatever_field] = CASE WHEN [condition1] = True THEN
value1
WHEN [condition2] = True THEN
value2
...
END

for as many fields as it takes.

Hope this helps.

Cheers

Gary|||Thanks to you all people up there...it solved my problem...Have a wonderful Holidays...:)sql