I have came across a situation that i have to assign a value to a variable using dynamic query.
I need the data which is stored in the table T1, but I have the table name T1 which is stored in other table M1. At the same time, i need to store the value in variable which will be used in various places in my stored procedure. The solution i provided here solves my issue with ease of code.
Declare @sql nvarchar(max) --Note the datatype nvarchar is used since it is used for dynamic query.
Declare @vcTemp varchar(max)
SELECT @sql=N'SELECT TOP 1 @vcTemp=colName FROM T1'
EXEC sp_executesql @sql, N'@vcTemp varchar(max) output', @vcTemp output
The above example explains, how the value is assigned to a variable using the dynamic query. Since i have table name T1 stored in table M1, i need this type of solution makes my job easier.
Hope this post would help everyone. Please do reply me, if you have any questions.