Dynamic Sql

• thaya2002

  Old Hand

  Points: 393

  More actions

  February 10, 2008 at 5:38 pm

  #203176

  hi,

  i wanted to test dynamic SQL scripts to get the basic knowledge.

  I get this error message

  "Server: Msg 207, Level 16, State 3, Line 1

  Invalid column name 'Paolo'.",when i run the script below.

  would anyone run this script on pubs database

  and interpret the error message for me.

  use pubs,

  go

  Declare @sql varchar(2000),@fnam varchar(20)

  set @fnam='Paolo'

  set @sql='select fname,lname from employee where fname='+@fnam

  exec (@sql)

  thanks for your help.

  regards,

  thaya

• Nisha-475382

  Hall of Fame

  Points: 3535

  More actions

  February 10, 2008 at 9:29 pm

  #776798

  USE PUBS

  GO

  DECLARE @SQL VARCHAR(2000)

  ,@fnam VARCHAR(20)

  SET @fnam='Paolo'

  SET @SQL = 'SELECT fname,lname FROM employee WHERE fname= '''+ @fnam + ''''

  PRINT @SQL

  EXEC (@SQL)

  Edit : You'll see how many quotes there are when you paste it in your query window..

• Grant Fritchey

  SSC Guru

  Points: 398882

  More actions

  February 11, 2008 at 8:36 am

  #776922

  If you have to use dynamic sql, be sure to look up & understand sp_executesql as the preferred method of calling it rather than using exec 'somestring'

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• Charlie-551146

  Say Hey Kid

  Points: 677

  More actions

  February 11, 2008 at 10:50 am

  #777002

  this way uses sp_executesql.

  use pubs

  GO

  declare @fnam varchar(20);

  declare @sqlstring nvarchar(4000);

  declare @Parm nvarchar(4000);

  set @sqlstring =

  N'SELECT fname,lname FROM pubs.dbo.employee WHERE fname= @empname';

  set @Parm = N'@empname varchar(20)';

  set @fnam = 'Paolo';

  execute sp_executesql @sqlstring, @Parm,

  @empname = @fnam;

  You can find info at:

  [font="Arial"]Thank You,
  [/font]
  [font="Comic Sans MS"]Charlie[/font]

  [font="Arial"]Charles Eaton[/font]