-1
Id        Name    Name1

1         John    Bob

2         Steve   John

TableName - AllColumnUpdate

This Script is not working.

declare @col_name varchar(max)

,@sql nvarchar(max)

DECLARE Curupdate CURSOR FOR 

SELECT column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 

'AllColumnUpdate' and ordinal_position > 1

OPEN Curupdate  

FETCH NEXT FROM Curupdate INTO @col_name

WHILE @@FETCH_STATUS = 0  

BEGIN  

select case when @col_name = 'John' then 1 else 0 end from AllColumnUpdate

FETCH NEXT FROM Curupdate INTO @col_name 

END 

CLOSE Curupdate  

DEALLOCATE Curupdate

But This is working

declare @col_name varchar(max)

,@sql nvarchar(max)

DECLARE Curupdate CURSOR FOR 

SELECT column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 

'AllColumnUpdate' and ordinal_position > 1

OPEN Curupdate  

FETCH NEXT FROM Curupdate INTO @col_name

WHILE @@FETCH_STATUS = 0  

BEGIN  

set @sql = 'select case when '+@col_name+' = ''John'' then 1 else 0 end from 

AllColumnUpdate'

EXEC (@sql)

FETCH NEXT FROM Curupdate INTO @col_name 

END 

CLOSE Curupdate  

DEALLOCATE Curupdate

Simple case statement is not working in Cursor but case with dynamic query is working fine in cursor...What could be the possible reason?

Kashif Qureshi
  • 1,460
  • 2
  • 13
  • 20
Ranjay Kumar
  • 61
  • 5
  • Format your code properly. – Eric Feb 14 '19 at 17:22
  • 2
    please explain what "is not working means". are you getting syntax errors? or is an error generated when it the code is executed? or do you get incorrect results when it runs? – Richard II Feb 14 '19 at 17:25
  • 1
    No, Not any type of error.....Not working means, select statement doesn't give me proper or expected result. – Ranjay Kumar Feb 14 '19 at 17:26
  • 1
    Well....we can't read your mind, see your screen or have any idea what your table or data is like. We also have no idea what you want as output. [Here](https://stackoverflow.com/help/how-to-ask) is a good place to start. – Sean Lange Feb 14 '19 at 17:33
  • 1
    what is the expected result? do you want the output of the first script to be identical to the output of the second script? – Richard II Feb 14 '19 at 17:33
  • And what do you get when SELECT column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'AllColumnUpdate' and ordinal_position > 1 ? – P.Salmon Feb 14 '19 at 17:35
  • I have a table named as AllColumnUpdate that contains 3 columns (Id, Name, Name1) having some data (Mentioned above)...Now look into my both Cursor script...First script is not giving me proper result but second one does....I need to know why so? – Ranjay Kumar Feb 14 '19 at 17:40
  • This query has to be dynamic. you cannot just set the column name to the variable and use that variable as column in your case statement.In SQL you can't use a variable for an object of column name, you have explicit to address the names – Kashif Qureshi Feb 14 '19 at 17:40
  • Thank you Kashif Qureshi. You got my problem and I got you. – Ranjay Kumar Feb 14 '19 at 17:49
  • Possible duplicate of [Can I pass variable to select statement as column name in SQL Server](https://stackoverflow.com/questions/12896147/can-i-pass-variable-to-select-statement-as-column-name-in-sql-server) – Richard II Feb 15 '19 at 17:52

1 Answers1

0

In the working (dynamic) script, you are executing a query against the value of a column.

In the non-working script, you are executing a query against the name of a column.

More concretely:

In the working script, the first iteration's generated query is:

select case when Name = 'John' then 1 else 0 end

In the non-working script, the first iteration's query is:

select case when 'Name' = 'John' then 1 else 0 end

The second SELECT statement has a condition which is always false, hence will always return 0, which is the result you are getting.

Richard II
  • 853
  • 9
  • 31
  • I subsequently found this answer. (https://stackoverflow.com/a/12896225/1633949) That question and this one--once stripped of all the surroundings--are essentially the same. – Richard II Feb 14 '19 at 18:04