CODEWITHSUNDEEP
sql-servert-sql
Ejaz
Ejaz

Reputation: 145

Assign value to a variable inside a string

I have a line in my code which assigns a value to a variable.

select @startdate = MIN(Stime) from mytable

Now I want to implement my code inside a stored procedure and get tablename as argument. So I changed the code to look like this

Set @sql = 'select @startdate = MIN(Stime) from '+@mytable
EXEC (@sql)

I'm getting an error which says that @startdate variable is not defined. Is there any workaround to this?

Upvotes: 1

Views: 2066

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280260

You need to stop using EXEC() for dynamic SQL. One reason is that for output parameters you need to be able to declare them - a bit tougher for EXEC() to handle. But there are even more important reasons. I assume you are properly sanitizing or validating the value for @mytable, wherever it comes from...

DECLARE @startdate DATETIME;

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT @startdate = MIN(Stime) FROM ' + @mytable;

EXEC sp_executesql @sql, N'@startdate DATETIME OUTPUT', @startdate OUTPUT;

SELECT @startdate;

Upvotes: 4

Related Questions