2
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
INDIA IT TECH
  • 1,902
  • 4
  • 12
  • 25
Nikhil
  • 71
  • 1
  • 9
  • Possible duplicate of [How to assign an exec result to a sql variable?](http://stackoverflow.com/questions/2245691/how-to-assign-an-exec-result-to-a-sql-variable) – HoneyBadger Mar 31 '16 at 10:53
  • 3
    Possible duplicate of [How to get sp\_executesql result into a variable?](http://stackoverflow.com/questions/803211/how-to-get-sp-executesql-result-into-a-variable) – Ravi Singh Mar 31 '16 at 10:58

3 Answers3

2

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

Eralper
  • 6,461
  • 2
  • 21
  • 27
0

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

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
alessalessio
  • 1,224
  • 1
  • 15
  • 28