Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-04-28 : 14:10:49
|
| Mark writes "Hi,I am using--SQL Server 2000--Windows 2000 SP4I am trying to read back data from a table that has an sql_variant column. Each row could have a different base datatype depending on the data entered by the user. The problem I am having is to read back multiple rows at once and CAST them to their appropriate type. I know you can do it one row at a time but this is not nearly efficient enough, I need to get all the data back in one recordset.I am trying the following, which gives an error message. Seems that SQL doesn't like Casting values to a variable/result of a function.SELECT [column_list...], CAST(sql_variant_column AS SQL_VARIANT_PROPERTY(sql_variant_column,'BaseType'))FROM TableWHERE ......Any ideas??Help much appreciated.Thanks in advance." |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-04-28 : 14:22:04
|
| A result set has to have a single datatype for a column so you won't be able to acheive what you want.You can put the different datattypes in different columnstryselect[char] = case SQL_VARIANT_PROPERTY(sql_variant_column,'BaseType') when 'char' then convert(varchar(1000),sql_variant_column) else null end,[int] = case SQL_VARIANT_PROPERTY(sql_variant_column,'BaseType') when 'int' then convert(int,sql_variant_column) else null end,==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
CAST sql_variant column to it's type in full table read