sql data type conversion

Question

I'm trying to use the return value of multiple variables in an insert statement to no avail. any help would be greatly appreciated...

--declare vars
DECLARE @strSpiff as VARCHAR(50), @strResult as VARCHAR(50), @strCMD as VARCHAR(100), @strInsert as VARCHAR(500)

--set value for first var. returns text similar to "TOY"
SELECT @strSpiff = strDescription FROM tblNames WHERE username='name'

--get return val of select query and add some text to it. i.e. "TOY_PO"
SET @strResult = @strSpiff+'_PO'

--another query using modified var. This query returns 10.00
SET @strCMD = 'SELECT ' + @strResult + ' FROM tblproducts WHERE  product_name=''something'''

--column "Amount" is of data type money
INSERT INTO tblStuff (Name,Amount)
values('name',CAST(@strCMD as money))

error received is "Cannot convert a char value to money. The char value has incorrect syntax."

I've tried many different cast and convert scenarios with no luck.

Answer 1

You can do direct select and insert as below.

INSERT tblStuff (Name,Amount)
SELECT name, Amt FROM tblproducts WHERE  product_name='something'

Answer 2

You are casting a string "SELECT TOY_PO FROM tblproducts..." to money, not the result of that execution of the query. Your current code isn't executing it

There is no cleaner way of doing this because you shouldn't rely on dynamic column names like this. Quite simply, SQL isn't designed to work that way

...
SET @strCMD = 'SELECT ''name'', ' + @strResult + ' FROM tblproducts WHERE  product_name=''something'''

INSERT INTO tblStuff (Name,Amount)
EXEC (@strCMD)

or

...
SET @strCMD = '
INSERT INTO tblStuff (Name,Amount)
SELECT ''name'', ' + @strResult + ' FROM tblproducts WHERE  product_name=''something'''

EXEC (@strCMD)