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.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Convert String to DataType in a Select

Author  Topic 

DeveloperIQ
Yak Posting Veteran

71 Posts

Posted - 2014-07-17 : 17:01:08
I have two columns in a Table, One Contains the Name of the Column and the other contains the Datatype of the column in a String Format. I would like to Select all rows from the Column but cast them as the Datatype as defined in the DataType column.
For Example:
SELECT ID ,
Cast(Col1 as Col2) ,
FROM Table1

Col2 just contains the datatype. Please let me know if you would like me to clarify further.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-17 : 17:10:23
You will need dynamic SQL for that, but your table design needs to be reconsidered. Why do you have a table like this? What is the business need?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

DeveloperIQ
Yak Posting Veteran

71 Posts

Posted - 2014-07-17 : 17:15:38
We are dynamically creating flat tables based on whether or not certain types of forms are used by the business in the main application. They can turn off any forms at any time, so we dont need to create any tables for those. This is the only bit left to do. Everything else is ready. I would hate to rewrite this with Dynamic SQL...
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-17 : 17:21:09
There's nothing to rewrite with dynamic SQL. I'm saying that given your table design, the type of SQL you'll have to write to achieve what you want is called dynamic SQL.

Please read this fully: http://www.sommarskog.se/dynamic_sql.html

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

DeveloperIQ
Yak Posting Veteran

71 Posts

Posted - 2014-07-17 : 17:54:55
Thanks Tara. I ended up doing some CASE statements in my actual query instead and did my CAST based on that.
Go to Top of Page
   

- Advertisement -