February 27, 2009 at 1:26 pm
I cannot figure why this query statement is unacceptable in my SPROC. Here's the message when executed (I've included a print of the Select statement):
SELECT p.Name_Last, p.Name_First, p.Name_Mid, p.SSN, p.DOB, p.Sex,
dc.dcName, dc.regionName AS RegionServ,
dc.pPhone, dc.geoAddress, dc.geoOptional,
dc.geoCity, dc.geoState, dc.geoZip, dc.mailAddress, dc.mailOptional,
dc.mailCity, dc.mailState, dc.mailZip, pg.pgName, pg.Address, pg.City,
pg.Zip, pg.BusAddress,
pg.BusCity, pg.BusZip
FROM AppSecurity.dbo.t_DC dc INNER JOIN
t_Registration reg ON dc.dcID = reg.dcID INNER JOIN
t_Person p ON reg.perID = p.perID INNER JOIN
AppSecurity.dbo.t_Programs pg ON reg.pgID = pg.pgID
WHERE (reg.Begda < CONVERT(DATETIME, 'Feb 27 2009 2:03PM', 102))
AND (reg.Endda >= CONVERT(DATETIME, 'Feb 27 2009 2:03PM', 102)) AND (reg.dcID = 1)
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'reg'.
I'm constructing a select statement in the SPROC which runs fine in a query window, but not when I try to execute it in the SPROC. The SPROC is listed here:
ALTER PROCEDURE [dbo].[s_SSCPartInfo]
@RID int,
@idcID int,
@pgID int,
@iDate datetime
AS
DECLARE
@iWhere varchar(400),
@inVar varchar(2000)
CREATE TABLE #outTab (
idNum int IDENTITY,
ProviderName varchar(50),
RegionServ varchar(50),
NameL varchar(20),
NameF varchar(20),
[...eliminated for brevity...]
VocProviderPhone varchar(16),
VocProgramName varchar(50),
VocProgramPhysAddr varchar(50),
VocProgramMailAddr varchar(50)
)
SET @iWhere = ' (reg.Begda < CONVERT(DATETIME, ''' + CAST(@iDate As Varchar) + ''', 102))
AND (reg.Endda >= CONVERT(DATETIME, ''' + CAST(@iDate As Varchar) + ''', 102)) ' IF @RID > 0
SET @iWhere = @iWhere + ' AND (dc.regionNum = ' + CAST(@RID As Varchar) + ')'
IF @idcID > 0
SET @iWhere = @iWhere + ' AND (reg.dcID = ' + CAST(@idcID As Varchar)+ ')'
IF @pgID > 0
SET @iWhere = @iWhere + ' AND (reg.pgID = ' + CAST(@pgID As Varchar)+ ')'
SET @inVar = 'SELECT p.Name_Last, p.Name_First, p.Name_Mid, p.SSN, p.DOB, p.Sex,
dc.dcName, dc.regionName AS RegionServ,
dc.pPhone, dc.geoAddress, dc.geoOptional,
dc.geoCity, dc.geoState, dc.geoZip, dc.mailAddress, dc.mailOptional,
dc.mailCity, dc.mailState, dc.mailZip, pg.pgName, pg.Address, pg.City,
pg.Zip, pg.BusAddress,
pg.BusCity, pg.BusZip
FROM AppSecurity.dbo.t_DC dc INNER JOIN
t_Registration reg ON dc.dcID = reg.dcID INNER JOIN
t_Person p ON reg.perID = p.perID INNER JOIN
AppSecurity.dbo.t_Programs pg ON reg.pgID = pg.pgID
WHERE ' + @iWhere
PRINT @inVar --got the print of the offensive select statement
/* commented out here to avoid extra confusion
INSERT INTO #outTab (NameL, NameF, NameM, SSN, DOB, Gender,
hPhysAddr1, hPhysAddr2,hPhysCity, hPhysState, hPhysZip,
ProviderName, RegionServ, ProviderPhone, ProviderPhysAddr1, ProviderPhysAddr2,
ProviderPhysCity, ProviderPhysState, ProviderPhysZip, ProviderMail1, ProviderMail2,
ProviderMailCity,ProviderMailState, ProviderMailZip, ProgramName,
ProgramPhysAddr, ProgramPhysCity, ProgramPhysZip,
ProgramMailAddr, ProgramMailCity, ProgramMailZip)
*/
EXEC(@iWhere)
--Select * FROM #outTab
DROP TABLE #outTab
February 27, 2009 at 1:30 pm
Why are you executing your Where clause? "exec (@iWhere)". From what I can see, that's just a set of boolean comparisons, without a Select, etc. Do you mean to execute @inVar?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 27, 2009 at 1:40 pm
Ok, I've been fighting this issue for hours. Obviously I'm too tired to see simple, obvious mistakes. Can I go home now? :blush:
March 2, 2009 at 7:20 am
Sometimes it takes a separate set of eyes to pick these things up.
One thing I always do with dynamic SQL, is have a print command right before the exec command. Before I take it to production, I comment that out, but it really helps in debugging dynamic SQL, because you can see what you're trying to execute.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply