question

SMAC avatar image
SMAC 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
decimalcast
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
GPO avatar image
GPO answered
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 ;
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.