Dynamic Sql

• thaya2002

  Old Hand

  Points: 343

  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: 397721

  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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

• 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]