Help!

[alex12]

Hello, guys.

I have been trying to execute the following and just keep getting the same error:
“Syntax error converting the nvarchar value ‘one_a’ to a column of data int.

declare @a int
declare @b int
declare abc cursor for select TABLE_Name, data_type
from information_schema.columns where table_name = ‘com_tv’
open abc
fetch abc into @a, @b
while @@fetch_status = 0
begin
fetch abc into @a, @b
print space (10) + @a + space(10) + @b
end
close abc
deallocate abc

Thanks for help in advance.

 

[SQLSister]

Looks like your data set has character data and you are trying to put the values into integer variables.

Questions about posting. See faq183-874

 

[vongrunt]

Go to BOL and see structure for information_schema.columns view. Columns table_name and data_type are nvarchar(128), so should be @a and @b.

 

[NoCoolHandle]

I can see why...
you will need to run some kind of case statement in your original select to get things to work, or change the variable to accept varchar data. There is no way sqlserver has any clue as to how a value like one_a could become a number. one would still be a great reach

a select case might look something like

select case SomCol when One_A then 1 when Two_b then 2 end as 'SomCol_asNum'
from some table

 

[NoCoolHandle]

typo (needs single quotes around the character values - in red)

select case SomCol when [red]'[/red]One_A[red]'[/red] then 1 when [red]'[/red]Two_b[red]'[/red] then 2 end as 'SomCol_asNum'
from some table