Converting dynamic sql query's COUNT result to INT

Question

I am trying to search within the values (table names) returned from a query to check if there is a record and some values in that record are null. If they are, then I want to insert the table's name to a temporary table. I get an error:

Conversion failed when converting the varchar value 'count(*) 
FROM  step_inusd_20130618 WHERE jobDateClosed IS NULL' to data type int.

This is the query:

  DECLARE @table_name VARCHAR(150)
  DECLARE @sql VARCHAR(1000)
  DECLARE @test int

  SELECT @table_name =  tableName FROM  #temp WHERE id = @count

   SET @sql = 'SELECT * FROM  ' + @table_name + ' WHERE jobDateClosed IS NULL'
--ERROR is below:
select @test =   'count(*) FROM  ' + @table_name + ' WHERE jobDateClosed IS NULL'
   --PRINT @sql
  -- EXEC(@sql)
   IF @test > 0 
   BEGIN 
    INSERT INTO #temp2 (tablename) VALUES ( @table_name);
   END
  SET @count = @count + 1

Any ideas how to convert the result of the count into an integer?

Giannis Paraskevopoulos · Accepted Answer · 2014-07-25 12:22:18Z

1

Check for sp_executesql where you may define output parameters.

DECLARE @table_name VARCHAR(150)
DECLARE @sql VARCHAR(1000)
DECLARE @test int

SELECT @table_name =  tableName FROM  #temp WHERE id = @count

DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

SET @SQLString = N'SELECT @test = count(*) FROM  ' + @table_name + ' WHERE jobDateClosed IS NULL'
SET @ParmDefinition = N'@test int OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @test=@test OUTPUT;

IF @test > 0 
    BEGIN 
        INSERT INTO #temp2 (tablename) VALUES ( @table_name);
    END
SET @count = @count + 1

answered Jul 25, 2014 at 12:22

Giannis Paraskevopoulos

18.5k2 gold badges52 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

lpg · Accepted Answer · 2014-07-25 12:23:01Z

1

Shouldn't be "SET" instead of "select" ?

E.g., changing:

select @test =   'count(*) FROM  ' + @table_name + ' WHERE jobDateClosed IS NULL'

for:

SET @test =   'select count(*) FROM  ' + @table_name + ' WHERE jobDateClosed IS NULL'

answered Jul 25, 2014 at 12:23

lpg

4,9371 gold badge18 silver badges16 bronze badges

Comments

Stanislav · Accepted Answer · 2014-07-25 12:23:05Z

0

As I see, your problem is, that $test variable is INT and you are trying to assign to it the TEXT value 'count ...'

Use aproach like: SELECT somevalue INTO myvar FROM mytable WHERE uid=1;

answered Jul 25, 2014 at 12:23

Stanislav

112 bronze badges

Comments

How 'bout a Fresca · Accepted Answer · 2014-07-25 12:34:58Z

0

I trimmed out the stuff not needed to show how to do this, so here it is:

DECLARE @table_name VARCHAR(150)
DECLARE @CountStatement NVARCHAR(1000)
DECLARE @test int

SELECT @table_name =  tableName FROM  #temp WHERE id = @count

SET @CountStatement = 'select @test = count(*) FROM  ' + @table_name + ' WHERE jobDateClosed IS NULL'
EXECUTE sp_executesql @CountStatement, N'@test INT OUTPUT', @test OUTPUT;
SELECT @test

answered Jul 25, 2014 at 12:34

How 'bout a Fresca

2,3371 gold badge18 silver badges27 bronze badges

Collectives™ on Stack Overflow

Converting dynamic sql query's COUNT result to INT

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related