CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Andrés Girón
Andrés Girón

Reputation: 97

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

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?

Upvotes: 1

Views: 375

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81990

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)

Upvotes: 1

Related Questions