NUMERIC(18, @variable) in SQL Server stored procedure

Question

I want to do something like this:

DECLARE @number INT = 2

DECLARE @TableResult AS TABLE 
                        (
                            Row1 VARCHAR (150),
                            Row2 VARCHAR (1500),
                            Row3 VARCHAR (150),
                            Row4 NUMERIC(18, @number)
                        )

Because @number is going to change.

I tried it and and got an "incorrect syntax" error.

Is there any way I can do something like that?

No, unfortunately the arguments for Numeric couldn't be provided dynamically or via a variable. — Siavash Rostami
– Siavash Rostami, Commented Apr 28, 2019 at 16:54
Those are COLUMNS - not "rows" - in your table definition! — marc_s
– marc_s, Commented Apr 28, 2019 at 17:24

John Cappelletti · Accepted Answer · 2019-04-28 17:01:45Z

1

SQL Server is declarative by design and does not support macro substitution.

That said, if you are not married to a table variable, perhaps a temp table instead.

Example

Declare @number INT = 3

Create table #TableResult (
 Row1            VARCHAR (150),
 Row2            VARCHAR (1500),
 Row3            VARCHAR (150)
  )

-- Add a new column via dynamic SQL
Declare @SQL varchar(max) = concat('Alter Table #TableResult add Row4 Numeric(18,',@number,')')
Exec(@SQL)


-- See the results
Select column_ordinal,name,system_type_name from sys.dm_exec_describe_first_result_set('Select * from #TableResult',null,null )

New Structure

column_ordinal  name    system_type_name
1               Row1    varchar(150)
2               Row2    varchar(1500)
3               Row3    varchar(150)
4               Row4    numeric(18,3)

answered Apr 28, 2019 at 17:01

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andrés Girón Over a year ago

This is exactly what i needed! Thanks a lot.

John Cappelletti Over a year ago

@AndrésGirón Always happy to help :)

Collectives™ on Stack Overflow

NUMERIC(18, @variable) in SQL Server stored procedure

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related