0

I am trying to get count of a rows with specific values in a table, and if the count is 0, then add a value in the table. This count is a local variable in stored procedure.

I am building the SQL dynamically and storing SQL statement into a nvarchar variable.

Then, using EXEC I am running this SQL as follows hoping to populate count variable.

But it's not working.

DECLARE @qry NVARCHAR(max)
DECLARE @count INT

-- building @qry will result as follows
@qry = SELECT @count = COUNT(*) FROM aTable WHERE (col1 = @col1 AND ...)


@count = EXEC @qry
IF @count = 0
BEGIN
  -- carry on with adding
END
Jordan Running
  • 102,619
  • 17
  • 182
  • 182
user1889838
  • 325
  • 4
  • 13
  • 37
  • In what way is it not working? Are you getting an error? Are you getting a different result than you expected? – Jordan Running Feb 26 '14 at 17:22
  • This should help I believe: http://stackoverflow.com/questions/6968512/assign-result-of-dynamic-sql-to-variable – Marc Feb 26 '14 at 17:22
  • Try doing it with `@count` as an `OUT` parameter, [this](http://stackoverflow.com/questions/1589466/execute-stored-procedure-with-an-output-parameter) may help. – user2989408 Feb 26 '14 at 17:22
  • at `@count = EXEC @qry` it gives me error. Incorrect syntax near '@count'. – user1889838 Feb 26 '14 at 17:24

3 Answers3

1

In your sql ,why you are execute your query through EXEC because of your required output is already in @count variable so it is not need in your case. Please refer below syntax.

DECLARE @qry Numeric
DECLARE @count INT

-- building @qry will result as follows
SELECT @count = COUNT(*) FROM aTable WHERE (col1 = @col1 AND ...)

IF @count = 0
BEGIN
  -- carry on with adding
END
Pragnesh Khalas
  • 2,908
  • 2
  • 13
  • 26
0

I think @qry needs to be a string for executing, not the result of the select, like so:

DECLARE @qry NVARCHAR(max);
DECLARE @count INT;

-- building @qry will result as follows
SET @qry = 'SELECT COUNT(*) FROM aTable WHERE (col1 = @col1 AND ...)';

SET @count = exec @qry;
Starscream1984
  • 3,072
  • 1
  • 19
  • 27
0

If you are building the query dynamically, you need sp_executesql. Try something like 

-- building @qry will result as follows
@qry = 'SELECT @count = COUNT(*) FROM aTable WHERE (col1 = @col1 AND ...)'

EXEC sp_executesql @qry, N'@count INT OUTPUT', @count OUTPUT;
--Do whatever you want with @count...

Source: Aaron Bertrand's answer here and sp_executesql explanation..

Community
  • 1
  • 1
user2989408
  • 3,127
  • 1
  • 17
  • 15