Trying to SET a variable from a SELECT

Trying to SET a variable from a SELECT

• wsr429

  I appreciate your reply:)

  I have however scoured that sight BEFORE having posted here and it didn't offer me the goods (imagine peeps actually search for an answer 1st... Simply amazing:)

  Problem with the static approach is that I'm actually iterating though a bunch of tables that have a known field and what I'm trying to do is generate a dynamic list of SQL statements that can later be passed on to support personnel.

  I want to look into all tables that have field X and Y then perform a test on the table with the dynamic SQL and if conditions are met, want to print the SQL.

  I did also try sp_executesql but didn't seem to get anywhere with it but will try again.

  I'll forward the snippet tomorrow @ work... Thanks again for the reply!

   

  
  

• nibs

  Hi Bill,

   

  The best advice I can give you is to stear clear of dynamic SQL, and use statis SQL instead:

   

  SET @objCnt = (SELECT COUNT(*) FROM happyhippytable WHERE begtime > endtime)

   

  If you think that you really have to use dynamic SQL, then the following site is a must-read: http://www.sommarskog.se/dynamic_sql.html.

   

  Oh, and the answer to your question is to use sp_executesql (documented in Books Online and on the forementioned site)

   

  
  --
  Hugo Kornelis, SQL Server MVP

   

  <Bill R.@discussions.microsoft.com> schreef in bericht news:ec0dd8a0-e635-4284-9897-4245ebe3db56@discussions.microsoft.com...

  Hello,

  Wondering why the following won't work:

  DECLARE @objName VARCHAR(50)
  SET @objName = 'happyhippytable'
  

  set @objCnt "select count(*) from " + @objName + " where begtime > endtime "

  or

  set @objCnt ("select count(*) from " + @objName + " where begtime > endtime ")

  or

  set @objCnt = ("select count(*) from " + @objName + " where begtime > endtime ")

  or

  set @objCnt = exec( "select count(*) from " + @objName + " where begtime > endtime ")

  or

  set @objCnt = exec( 'select count(*) from ' + @objName + ' where begtime > endtime ')

  (Yes, I've tried a number of version:)

  
  

• grooveBiscuit

  How about this:
  
  DECLARE @OBJNAME VARCHAR(50)
  DECLARE @SQLSTRING NVARCHAR(4000)
  DECLARE @COUNT INT
  DECLARE @PARAMS NVARCHAR(100)
  
  SET @OBJNAME = N'TABLENAME'
  SET @SQLSTRING = N'SELECT @COUNT = COUNT(*) FROM ' + @OBJNAME
  SET @PARAMS = N'@COUNT INT OUTPUT'
  
  EXECUTE SP_EXECUTESQL @SQLSTRING, @PARAMS, @COUNT OUTPUT
  SELECT @COUNT
  
  Theres an example in BOL for sql2005 if you search for sp_executesql and look at the parementerized example showing out parameters
  
  Hope that helps
  
  
• Trying to SET a variable from a SELECT