Assign result of dynamic sql to variable
Dynamic SQL is the SQL statement that is constructed and executed at runtime based on input parameters passed. Let us go through some examples using the EXEC command and sp_executesql extended stored procedure.
By Using sp_executesql with output parameter.
declare @out_ varchar(max); execute sp_executesql N'select @out_ = ''Welcome''', N'@out_ varchar(max) OUTPUT', @out_ = @out_ output; select @out_; DECLARE @COUNT_STRING NVARCHAR(4000) SET @COUNT_STRING='select @total_filters = count(1) from db_data WHERE 1=1 ' SET @COUNT_STRING += @otherfilters execute sp_executesql @COUNT_STRING, N'@total_filters int OUTPUT', @total_filters = @total_filters output;