Why I am getting the error message Server: Msg 245
-
I am getting the following error while building the dynamic sql query Server: Msg 245, Level 16, State 1, Procedure proc_sch_ReceivedMails_Sort, Line 45 Syntax error converting the varchar value 'INSERT INTO @tblTemp( and the dynamic query is with one parameter +@pProjectPK DECLARE @tblTemp TABLE ( TempPK INT IDENTITY(1,1), ContactTypeCodeFK tinyINT, ContactTypeDesc VARCHAR(50), ProjectFK INT ) set @queryString = 'INSERT INTO @tblTemp ( ContactTypeCodeFK, ContactTypeDesc, PartFK ) ( SELECT ContactTypeCodeFK, ContactTypeDesc, PartFK, PartMaster.PartDesc FROM tbl_Correspondence CORES INNER JOIN tbl_PartCode ON CORES.PartCodeFK = tbl_PartCode.PartCodePK WHERE CORES.ProjectFK = "'+@pProjectPK+'")' Print @querystring exec @querystring Kalyan
-
I am getting the following error while building the dynamic sql query Server: Msg 245, Level 16, State 1, Procedure proc_sch_ReceivedMails_Sort, Line 45 Syntax error converting the varchar value 'INSERT INTO @tblTemp( and the dynamic query is with one parameter +@pProjectPK DECLARE @tblTemp TABLE ( TempPK INT IDENTITY(1,1), ContactTypeCodeFK tinyINT, ContactTypeDesc VARCHAR(50), ProjectFK INT ) set @queryString = 'INSERT INTO @tblTemp ( ContactTypeCodeFK, ContactTypeDesc, PartFK ) ( SELECT ContactTypeCodeFK, ContactTypeDesc, PartFK, PartMaster.PartDesc FROM tbl_Correspondence CORES INNER JOIN tbl_PartCode ON CORES.PartCodeFK = tbl_PartCode.PartCodePK WHERE CORES.ProjectFK = "'+@pProjectPK+'")' Print @querystring exec @querystring Kalyan
What I don't understand is why you are even using dynamic SQL for this? You use Dynamic SQL when you need to do something that cannot be done otherwise because of the security risks involved.
INSERT INTO @tblTemp
(
ContactTypeCodeFK,
ContactTypeDesc,
PartFK
)
SELECT ContactTypeCodeFK,
ContactTypeDesc,
PartFK,
PartMaster.PartDesc
FROM tbl_Correspondence CORES
INNER JOIN tbl_PartCode
ON CORES.PartCodeFK = tbl_PartCode.PartCodePK
WHERE CORES.ProjectFK = @pProjectPKThis will work fine like this without being "dynamic"
Scottish Developers upcoming sessions include: .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy My: Website | Blog
-
What I don't understand is why you are even using dynamic SQL for this? You use Dynamic SQL when you need to do something that cannot be done otherwise because of the security risks involved.
INSERT INTO @tblTemp
(
ContactTypeCodeFK,
ContactTypeDesc,
PartFK
)
SELECT ContactTypeCodeFK,
ContactTypeDesc,
PartFK,
PartMaster.PartDesc
FROM tbl_Correspondence CORES
INNER JOIN tbl_PartCode
ON CORES.PartCodeFK = tbl_PartCode.PartCodePK
WHERE CORES.ProjectFK = @pProjectPKThis will work fine like this without being "dynamic"
Scottish Developers upcoming sessions include: .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy My: Website | Blog
-
I am getting the following error while building the dynamic sql query Server: Msg 245, Level 16, State 1, Procedure proc_sch_ReceivedMails_Sort, Line 45 Syntax error converting the varchar value 'INSERT INTO @tblTemp( and the dynamic query is with one parameter +@pProjectPK DECLARE @tblTemp TABLE ( TempPK INT IDENTITY(1,1), ContactTypeCodeFK tinyINT, ContactTypeDesc VARCHAR(50), ProjectFK INT ) set @queryString = 'INSERT INTO @tblTemp ( ContactTypeCodeFK, ContactTypeDesc, PartFK ) ( SELECT ContactTypeCodeFK, ContactTypeDesc, PartFK, PartMaster.PartDesc FROM tbl_Correspondence CORES INNER JOIN tbl_PartCode ON CORES.PartCodeFK = tbl_PartCode.PartCodePK WHERE CORES.ProjectFK = "'+@pProjectPK+'")' Print @querystring exec @querystring Kalyan
kalyan_vb wrote:
"'+@pProjectPK+'")'
Two things I see * You are using a quoteation mark " instead of two appostrophes '' - although since this is an INT you don't need the extra apostrophes anyway * @pProjectPK is possibly an INT in which case you need to
CAST(@pProjectPK AS VARCHAR(20))before you inject it into a string. You can also use sp_executesql[^] (like this[^]) so you don't have to inject values where you could use a parameter. This will improve the security of your application a little bit over usingEXECin this scenario.
Scottish Developers upcoming sessions include: .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy My: Website | Blog
-
I am getting the following error while building the dynamic sql query Server: Msg 245, Level 16, State 1, Procedure proc_sch_ReceivedMails_Sort, Line 45 Syntax error converting the varchar value 'INSERT INTO @tblTemp( and the dynamic query is with one parameter +@pProjectPK DECLARE @tblTemp TABLE ( TempPK INT IDENTITY(1,1), ContactTypeCodeFK tinyINT, ContactTypeDesc VARCHAR(50), ProjectFK INT ) set @queryString = 'INSERT INTO @tblTemp ( ContactTypeCodeFK, ContactTypeDesc, PartFK ) ( SELECT ContactTypeCodeFK, ContactTypeDesc, PartFK, PartMaster.PartDesc FROM tbl_Correspondence CORES INNER JOIN tbl_PartCode ON CORES.PartCodeFK = tbl_PartCode.PartCodePK WHERE CORES.ProjectFK = "'+@pProjectPK+'")' Print @querystring exec @querystring Kalyan
Hi! Sorry, I don't think that your query will work. You cannot insert into a table variable from inside a dynamic query. Try the following:
declare @test table ( MyId int )
insert into @test values (5)declare @stmt varchar(128)
set @stmt = 'insert into @test values (6)'
exec( @stmt )The dynamically executed statment will not work because it does not know about the table var @test. Additionally I saw that you forgot the ( ) in your exec-statement.
exec @querystringcannot execute a dynamic query; you have to writeexec ( @querystring ). Otherwise SQL Server will think you want to execute a stored procedure with the name stored in @querystring. See Transact-SQL-Reference for EXECUTE in Books Online for details. Regards, Rainer. Rainer Stropek Visit my blog at http://www.cubido.at/rainers