using sp_executesql

• Brad Allison

  Hall of Fame

  Points: 3550

  More actions

  February 11, 2011 at 7:42 am

  #234931

  This should be a simple question and hopefully a simple answer. I usually build stored procs directly for use in SQL Server 2005, however now I have a case where I need to run an OPENQUERY SQL string against a different linked database server.

  In my Stored proc, I am concantenating several strings together to form the actual SELECT statement for this other database. Before I hit the EXEC sp_executesql statement, is there a way to print or see the string that is stored in my @SQLQuery variable?

  Here is the actual concatenations that I am doing:

  SET @SQLQuery = 'SELECT * from OPENQUERY([PI], SELECT x.time, x.value as Rate, x2.value as Surge '

  SET @SQLQuery = @SQLQuery + 'FROM piarchive..piinterp2 x '

  SET @SQLQuery = @SQLQuery + 'INNER JOIN piarchive..piinterp2 x2 on x.time=x2.time '

  SET @SQLQuery = @SQLQuery + 'WHERE x.tag= ''' + @rateTag + ''' '

  SET @SQLQuery = @SQLQuery + 'AND x.time> ''' + @startDate + ' ' + '06:59:00 AM '

  SET @SQLQuery = @SQLQuery + 'AND x.time> ''' + @endDate + ' ' + '07:01:00 AM '

  SET @SQLQuery = @SQLQuery + 'AND x.timestep=''1m'' AND x2.tag= ''' + @surgeTag + ''''

  Thanks for the information.

• Brad Allison

  Hall of Fame

  Points: 3550

  More actions

  February 11, 2011 at 7:44 am

  #1285716

  Actually I think I answered my own question

  I inserted PRINT @SQLQuery and I can see the string in the message window.