Set a variable to result of executed dynamic query

• Martin Hawley

  SSChasing Mays

  Points: 650

  More actions

  November 23, 2007 at 5:50 am

  #176996

  Hi,

  I am trying to populate a variable (@rows) with the result set of a dynamic sql statement (@strsql).

  If I use static sql I can simply use:-

  SELECT @rows = COUNT(*) FROM tblTest

  However if I use :-

  SET @strsql = 'COUNT(*) FROM tblTest'

  SELECT @rows = @strsql

  I get @rows set to the sql string, not the result set. Trying

  SELECT @rows = EXECUTE(@strsql)

  simply gives me an error message.

  I cannot use static sql as I need an associated WHERE clause needs to be built up first.

  Any ideas please?

  TIA,

  Martin

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  November 23, 2007 at 6:07 am

  #753613

  You could use sp_executesql procedure....

  DECLARE @TotalRows INT

  DECLARE @sql NVARCHAR(1000)

  SET @sql = 'SELECT @TotalRows = COUNT( * ) FROM sysobjects'

  EXECUTE SP_EXECUTESQL @sql, N'@TotalRows INT OUTPUT',@TotalRows = @TotalRows OUTPUT

  SELECT @TotalRows

  or you could also use table variable or temp table like

  DECLARE @TableRows TABLE( TotalRows INT NOT NULL )

  DECLARE @sql NVARCHAR(1000)

  SET @sql = 'SELECT COUNT( * ) FROM sysobjects'

  INSERT @TableRows( TotalRows )

  EXECUTE( @sql )

  SELECT * FROM @TableRows

  --Ramesh
  

  ---

• Martin Hawley

  SSChasing Mays

  Points: 650

  More actions

  November 23, 2007 at 7:29 am

  #753631

  Thanks Ramesh.

  Two points:

  1) Think I can use this. The only problem I can see is if the dynamic sql exceeds 8000 characters, but hopefully that will not be the case.

  2) The temp table method gives me an error message "EXECUTE cannot be used as a source when inserting into a table variable."

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  November 23, 2007 at 7:41 am

  #753634

  I forgot which forum i'm in....

  Just convert the table variable into temporary table....

  --Ramesh
  

  ---

• Martin Hawley

  SSChasing Mays

  Points: 650

  More actions

  November 23, 2007 at 8:04 am

  #753643

  Thanks again for your help.

  I have got the first method working and tested it against the longest sql it will encounter. Everything is working fine 🙂