February 10, 2008 at 5:38 pm
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
February 10, 2008 at 9:29 pm
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..
February 11, 2008 at 8:36 am
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
February 11, 2008 at 10:50 am
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]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply