asked
Cast as Decimal; Can I Pass fields from a table to Length and Decimal
I would like to pass the length and decimal for a field from a table, to my cast as decimal script. But as soon as I pass the field rather than a hard coded number I get an error; Incorrect syntax near , (or similar always pointing to variable passed) Is it possible to do this and/or is there a work around to make length and decimal dynamic for my cast? SELECT CAST(A.VALUE AS DECIMAL (5,V.FIELD_DEC)) FROM dbo.CQ_ANALYSIS A INNER JOIN dbo.CQL_VARIABLES V ON A.VARIABLE = V.VARIABLE
As always, there may be far better solutions than this for the underlying problem you're really trying to solve, but without knowing what that problem is, this is just a suggestion to hopefully get you started. --======= Step 1: read this:
http://www.sommarskog.se/dynamic_sql.html. If you still wish to proceed, go to Step 2. --======= Step 2: Just me trying to reproduce your problem using temp tables. IF OBJECT_ID('tempdb..#CQL_VARIABLES') IS NOT NULL DROP TABLE #CQL_VARIABLES ; SELECT cast('2' as int) as FIELD_DEC ,cast('1' as int) as VARIABLE INTO #CQL_VARIABLES UNION ALL SELECT cast('3' as int) as FIELD_DEC ,cast('2' as int) as VARIABLE ; IF OBJECT_ID('tempdb..#CQ_ANALYSIS') IS NOT NULL DROP TABLE #CQ_ANALYSIS ; SELECT cast('1' as int) as VARIABLE ,cast('44' as int) as [VALUE] INTO #CQ_ANALYSIS UNION ALL SELECT cast('2' as int) as VARIABLE ,cast('45' as int) as [VALUE] ; --======= Step 3: Generate some dynamic SQL DECLARE @sql as nvarchar(max) = ( SELECT TOP 1 ' SELECT CAST(A.VALUE AS DECIMAL (5,' + cast(V.FIELD_DEC as varchar(3)) + ')) FROM #CQ_ANALYSIS A ' FROM #CQ_ANALYSIS A JOIN #CQL_VARIABLES V ON A.VARIABLE = V.VARIABLE ORDER BY V.VARIABLE --Only because I don't yet know how you want to select the row of interest. ) PRINT @sql ; EXEC sp_executesql @sql ;
18 People are following this question.
