declare @tags1 varchar(max)
declare @blockcount int
declare @blockstring varchar(max)
set @tags1='%Gifts%' Or CategoryTag Like'%Packaging%'
set @blockstring= 'SELECT @blogcount=count(*) FROM M_PHBLogs where CategoryTag LIKE '+ @tags1 +' AND ContentType=1 '
exec (@blockstring)
I want to store the result of exec(@blockstring) into another variable like
@blockcount=exec(@blockstring)
if(@blockcount!=0)
BEGIN
//something
END
Not with EXEC (as far as I know), but using sp_ExecuteSQL you can define parameters and pass these parameters as input or output to the dynamically created SQL script
Here is simplified version of your SQL script
declare @tags1 nvarchar(max)
declare @blockcount int
declare @blockstring nvarchar(max)
declare @blogcount_out int;
set @blockstring= 'SELECT @blogcount = count(*) FROM UserDocuments'
EXECUTE sp_executesql @blockstring, N'@blogcount int output', @blogcount = @blogcount_out output
select @blogcount_out
Please read the tutorial Use sp_ExecuteSQL T-SQL Stored Procedure with Input and Output Parameters for more detailed samples on how to use params with sp_executesql
Use sp_executesql
Remember to declare @blockstring nvarchar(max)
Call it this way:
exec sp_executesql @blockstring, N'@blogcount int output', @blogcount = @blockcount OUTPUT;
select @blockcount
Use sp_executesql. In fact, you should always use this function because it allows parameterization.
declare @tags1 varchar(max);
declare @blockcount int;
declare @blockstring varchar(max);
set @tags1 = '''%Gifts%'' Or CategoryTag Like ''%Packaging%''';
set @blockstring= 'SELECT @blogcount = count(*) FROM M_PHBLogs where CategoryTag LIKE '+ @tags1 +' AND ContentType = 1';
exec sp_executesql @blockstring, N'@blockcount int output', @blockcount = @blockcount;
