Scalar variable must be declared in SQL variable

Question

I'm creating a report using sql scripts through management studio and I'm getting the error " Must Declare the scalar variable "@Account". I've been reading other similar questions on this portal but they are related to c#

I'm currently trying to reduce the code on the script so I decided to put a sql script into a variable because depending on a condition the where condition will change. Below is an example of the code

Declare @Account int = 1 , @SQL varchar(max)=''
Select @SQL = N'Select ColumnA,ColumnB, ColumnC from Table1 where ColumnA =1'

if @Account IS NULL
Begin
exec(@SQL)
end

--Here is where the error is hapening
else
begin
--This is the line causing the error
Select @SQL = @SQL + 'AND ColumnB=@Account"
exec(@SQL)
end

If I type manually the value of the variable next to "ColumnB=" it works but the account number will be selected by the user executing the script. I'm thinking on maybe building a temp table to capture the variable value and then do a sub query on the where condition but maybe the solution to this error may be more easier

I'm sorry , I've asked similar questions before and someone always edit that part to include mysql. Thank you for your feedback , I'll review my further questions to avoid wrong data :) — Samayoa
– Samayoa, Commented Dec 6, 2018 at 20:27
@Samayoa pls pay attention to use the correct product tag. Incorrect product tags may lead to incorrect answers wasting both your and the answerer's time. This question has nothing to do with mysql, the code and the error message come from ms sql server. — Shadow
– Shadow, Commented Dec 6, 2018 at 20:38

Gordon Linoff · Accepted Answer · 2018-12-06 20:24:03Z

2

You want sp_executesql:

select @SQL = @SQL + 'AND ColumnB=@Account';

exec sp_executesql @SQL, N'@Account int', @Account=@Account;

This is how you pass parameters into a dynamic SQL statement in SQL Server. I strongly recommend that you only use sp_executesql to execute SQL statements -- even when you don't have parameters. Using it makes it easy to implement parameters when you need them.

answered Dec 6, 2018 at 20:24

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Samayoa Over a year ago

Thank you Gordon , for a reason it always return an error that "sp_executesql" does not exists

Gordon Linoff Over a year ago

@Samayoa . . . It is in all supported versions of SQL Server: learn.microsoft.com/en-us/sql/relational-databases/….

Brisbe · Accepted Answer · 2018-12-06 20:22:35Z

0

You are passing in '@Account' into the @SQL variable -- the underlying EXEC cannot see that variable.

One way of fixing this would instead be to do this:

Select @SQL = @SQL + 'AND ColumnB=' + CAST(@Account as varchar)

answered Dec 6, 2018 at 20:22

Brisbe

1,6382 gold badges21 silver badges43 bronze badges

Collectives™ on Stack Overflow

Scalar variable must be declared in SQL variable

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related