Storing Datatype in a variable 1

[tek2002]

There has to be a way to store a datatype in a variable and then use that variable in a conversion/cast.

I tried to do it this way but i keep getting this error:

"Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '@DATATYPE'."


DECLARE @DATATYPE VARCHAR(25)
SELECT @DATATYPE = 'MONEY'

UPDATE Table1
SET AMOUNT = CAST('5000'AS @DATATYPE)
WHERE Table_ID = 100

Does anyone have any suggestions?

Thanks so much.

 

[MDXer]

dynamic sql might work

Declare @strSQL varchar(2000)
DECLARE @DATATYPE VARCHAR(25)
SELECT @DATATYPE = 'MONEY'

Set @strSQL = 'UPDATE Table1'
Set @strSQL = @strSQL + 'SET AMOUNT = CAST(''5000''AS ' + @DATATYPE + ')'
Set @strSQL = @strSQL + 'WHERE Table_ID = 100'
exec(@strSQL)
[code]

However it would be kind of pointless since the datatype stored is dependent on the column datatype.  Meaning if the datatype is a varchar and you cast it to money it will still be saved as a varchar.


"Shoot Me! Shoot Me NOW!!!"
                           - Daffy Duck

 

[ESquared]

Hi tek20002,

Here's why your code isn't working. The statement you really want, in expanded form, is (I've put things in Query Analyzer colors to highlight the differences):

[blue]SET[/blue] AMOUNT [gray]=[/gray] [COLOR=#ff00ff]CAST[/color][gray]([/gray][red]'5000'[/red] [blue]AS money[/blue][gray])[/gray]

But when you use a varchar variable, this is essentially what you're doing:

[blue]SET[/blue] AMOUNT [gray]=[/gray] [COLOR=#ff00ff]CAST[/color][gray]([/gray][red]'5000'[/red] [blue]AS[/blue] [red]'money'[/red][gray])[/gray]

[blue]money[/blue] in the first case is a keyword the sql parser understands as a data type. [red]'money'[/red] in the second case is a varchar string, which is improper syntax.

As MDXer said, there's always dynamic SQL.

For what it's worth, you might be able to get away with just doing the SET statement as is... implicit conversion can sometimes work depending on the data you are attempting to pass in.

 

[donutman]

Try this for the UPDATE:

SET Amount=CASE @DataType 
   WHEN 'money' then Cast('5000' as money)
   WHEN 'int' then Cast('5000' as int) 
END

But I seriously question why this is required. There's probably a better way to do the "big picture". Dynamic SQL is over-the-top for this.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

Just be aware that the cast statements seem to be evaluated even when the WHEN clause is not true, so if your inputs and parameters are varied enough that this would cause a syntax error, the CASE method won't work.

(I was trying something like this in a mixed inline/table UDF that would give different datatypes in its output column...)

 

[MDXer]

The issue is still that unless the 'AMOUNT' Column is a varchar then dynamically casting to a diffrent data type could be pointless.


5000 cast to Money will produce 5000.0000 but inserted ro updated in a column type of int will result in 5000 in the table. A column type of Float will result in 5000.0 a numeric(x,0) will produce the same as an int. Without really knowing the table structure or data that is being updated it is hard to understand what the end goal is.


"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[ESquared]

Yes, MDXer, your original point about the column still holds. It's possible that the questioner gave a simplified example, though.

 

[tek2002]

Yes, i did give a simplified version

Thank you to everyone for thier input - of the lot, the suggestion made by MDXer (dynamic sql)will do the job for me at this time.

Thanks Again.

In case any one has any better suggestions, here is a bit more detail though still simplified.....

Creating Intranet - storing attributes(form name,control name,datatype etc.)in db table1. When user hits save button, values in controls need to be saved --> values stored in table2 as string --> save routine grabs values (table2) and datatype (table1) and must first convert the value to its appropriate datatype --> all values stored in table2 as string but if value in database table to update is of type 'money' or 'int', will need to convert that value then update the appropriate table/tables....

input param:
@TableColName
@WebFormName
@WebControlID

/*********************************/
--GET DATATYPE
/*********************************/
SELECT @DATATYPE = DATATYPE
FROM table1
WHERE WEB_CONTROL_ID = @WEBCONTROLID

/***********************************/
--GET VALUE TO BE SAVED IN DATABASE
/***********************************/
SELECT @VALUE = VALIDATE_VALUE
FROM table2
WHERE TableColName = @TableColName
AND WEBFORM_NAME = @WEBFORMNAME
AND WEB_CONTROL_ID = @WEBCONTROLID


/*********************************/
--SAVE VALUE IN DATABASE TABLES:
/*********************************/

SELECT @STRSQL = 'UPDATE table3'
SELECT @STRSQL = @STRSQL + 'SET table3.AMOUNT = CAST(' + @VALUE + ' AS ' + @DATATYPE + ') '
SELECT @STRSQL = @STRSQL + 'WHERE table3.TableColName= ' + '''' + @TableColName+ ''''

EXECUTE(@STRSQL)