How to get count from dynamic query

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  September 30, 2010 at 1:12 am

  #103996

  Hi

  i need to get count (@count ) from below query

  DECLARE

  @Count int,

  @Query nvarchar(300),

  @table nvarchar(50),

  @Condition nvarchar(100)

  SET @table = 'sysobjects'

  SET @condition = 'xtype = ''u'''

  SET @query = 'Select COUNT(1) FROM ' + @table + ' WHERE ' + @Condition

  PRINT @query

  SET @count= EXEC(@query)

  last Statement

  SET @count= EXEC(@query) is not working

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• Kingston Dhasian

  SSCoach

  Points: 19799

  More actions

  September 30, 2010 at 1:42 am

  #1229483

  You can use sp_executeSQL for the same

  DECLARE

  @Count int,

  @Query nvarchar(300),

  @table nvarchar(50),

  @Condition nvarchar(100)

  SET @table = 'sysobjects'

  SET @condition = 'xtype = ''u'''

  SET @query = 'Select @Count = COUNT(1) FROM ' + @table + ' WHERE ' + @Condition

  EXECUTE sp_executeSQL @query, N'@Count INT OUTPUT', @Count OUTPUT

  SELECT @Count

  ---

  Kingston Dhasian

  How to post data/code on a forum to get the best help - Jeff Moden
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  September 30, 2010 at 1:49 am

  #1229485

  Thanks a lot kingston ...can u brief about EXECUTE sp_executeSQL @query, N'@Count INT OUTPUT', @Count OUTPUT basically the OUTPUT parameter.

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• Kingston Dhasian

  SSCoach

  Points: 19799

  More actions

  September 30, 2010 at 2:01 am

  #1229487

  Bhuvnesh (9/30/2010)

  ---

  Thanks a lot kingston ...can u brief about EXECUTE sp_executeSQL @query, N'@Count INT OUTPUT', @Count OUTPUT basically the OUTPUT parameter.

  The OUTPUT parameter will allow you to use the value assigned to the variable @count even outside the scope of the Dynamic Query. In our example we have used the variable in the SELECT @Count statement. Had we not declared the parameter as an OUTPUT parameter, we will get a NULL as the value for @Count.

  ---

  Kingston Dhasian

  How to post data/code on a forum to get the best help - Jeff Moden
  http://www.sqlservercentral.com/articles/Best+Practices/61537/