sp_executesql

• gary1

  SSC-Insane

  Points: 21152

  More actions

  January 17, 2012 at 10:36 am

  #263027

  Hi,

  I'm trying to explore about sp_exectesql and the below query is showing the syntax error at '+mytable+'.

  Please advice..

  DECLARE @TblName varchar(50)

  DECLARE @ColValue int

  set @ColValue=30

  set @tblName='MyTable'

  execute sp_executesql N'Select * from '+ @Mytable + ' where somecol = @ColVal' , '@ColVal int', @ColValue;

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  January 17, 2012 at 11:10 am

  #1434758

  DECLARE @TblName varchar(50)

  DECLARE @ColValue int

  set @ColValue=30

  set @tblName='MyTable'

  execute sp_executesql N'Select * from '+ QUOTENAME(@tblName) + ' where somecol = @ColVal' , '@ColVal int', @ColValue;

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• Eric Hu²º¹²

  Mr or Mrs. 500

  Points: 506

  More actions

  January 17, 2012 at 7:19 pm

  #1434944

  You have to write the job like this:

  DECLARE @tblName nvarchar(50)

  DECLARE @sql nvarchar(200)

  set @tblName='T1'

  SET @sql = N'Select * from '+quotename(@tblName)

  execute sp_executesql @sql

  --The confidence is a premise, the courage is a motive, the perseverance is assurance!
  http://www.cnblogs.com/huyong/[/url]