Use numeric inside SQL statement in procedure

Question

How to use numeric variable in sql statement inside procedure?
This is my try:

create procedure ##sp_check (

    @tolerance numeric
)

AS

Declare @SQL nvarchar(max)
SET @SQL = '
SELECT
    *
FROM
    a
WHERE
    value > @tolerance
'
    exec sp_executesql @SQL

go

exec ##sp_check 1

and the ERROR: Must declare the scalar variable "@tolerance".
I think this is because the variable is invisible between ' and ' so I can do it by declaring @tolerance as varchar and in sql statement converting it into numeric but its a bit confusing...

Richard · Accepted Answer · 2018-06-06 10:00:32Z

1

You have to explicitly pass the variable into the sp_executesql (it runs in a different scope that doesn't have access to variables declared in the calling scope).

SET @SQL = 'SELECT * FROM a WHERE value > @t'
exec sp_executesql @SQL, N'@t numeric', @t=@tolerance

answered Jun 6, 2018 at 10:00

Richard

110k21 gold badges214 silver badges279 bronze badges

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

5 Comments

stakowerflol Over a year ago

what this N means?

stakowerflol Over a year ago

oh I guess the N before ' ' says that in brankets is numeric value not character

B3S Over a year ago

N means NVARCHAR since sp_executesql wants nvarchar parameters

stakowerflol Over a year ago

and how modify this when I wanna inject 2 variables or more?

stakowerflol Over a year ago

oh, it goes like this, then: exec sp_executesql @SQL, N'@var1 decimal(10,2), @var2 decimal(10,2)', @var1= @var1, @var2= @var2

B3S · Accepted Answer · 2018-06-06 09:53:48Z

1

I wonder why you're using Dynamic SQL here.. try this:

create procedure ##sp_check (

    @tolerance numeric
)

AS

SELECT * FROM a WHERE value > @tolerance

go

exec ##sp_check 1

answered Jun 6, 2018 at 9:53

B3S

1,0817 silver badges18 bronze badges

Comments

Thom A · Accepted Answer · 2018-06-06 09:55:16Z

0

You can't reference a variable declared outside of a dynamic statement inside it. Thus, the correct syntax would be:

CREATE PROC ##sp_check (@tolerance numeric)
AS

    DECLARE @SQL nvarchar(max)
    SET @SQL = N'SELECT *' + NCHAR(10) +
               N'FROM a' + NCHAR(10) +
               N'WHERE value > @dtolerance;';
    PRINT @SQL;
    EXEC sp_executesql @SQL, N'@dtolerance numeric',@dtolerance = @tolerance;

go

exec ##sp_check 1;

I suggest, however, you declare your scale and precision on your numeric, it's very bad practice to not do so.

answered Jun 6, 2018 at 9:55

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Collectives™ on Stack Overflow

Use numeric inside SQL statement in procedure

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related