April 18, 2007 at 11:40 am
Ok, I'm trying to get used to this putting the sql into a string and using sp_executesql rather than the other way around as in hard coding it not in a string like most people do out there. Anyway, I can't figure out now again why it's complaining. I know it's something simple but I can't get my hands around it in this case as again, I have IF statements in here in the JOINs so not sure what I need to do here to fix it. It's complaining about some comma, but that's one of the downsides (unless I just don't know about an easy way to get errors like you do when you straight up code sql), is that you cannot troubleshoot sql that's in a string very well, because the errors you get are usually pretty general and it tells you a line number but usually, it's not very specific in details as to the "what"
So, with that, here's my existing query
DECLARE @sql NVARCHAR(4000),
@paramlist NVARCHAR(4000),
@ooCampusID AS INT,
@ooStaffID AS INT,
@adProgramID AS INT,
@TermID AS INT,
@LastName AS varchar(50),
@FirstName AS varchar(50),
@TaughtCourseID INT,
@CanTeachCourseID INT,
@StatusID INT,
@jjRoleID INT,
@debug BIT
--SET @FirstName = 'David'
SET @debug = 1
SET @TermID = 1
SET @ooCampusID = 5
SET @jjRoleID = 1
SET @TaughtCourseID = 252
SET @CanTeachCourseID = 15
SET @StatusID = 1
SET @adProgramID = 4
-- Setup parameter list to be passed to sp_executesql
SELECT @paramlist = '@xooCampusID AS INT,
@xTaughtCourseID AS INT,
@xCanTeachCourseID AS INT,
@xadProgramID,
@xjjRoleID AS INT,
@xooStaffID AS INT,
@xTermID AS INT,
@xLastName AS varchar(50),
@xFirstName AS varchar(50),
@xCourseID INT,
@xStatusID INT'
-- Get Parent Row information for Parent Results Grid
SELECT @sql =
'SELECT DISTINCT a.[FirstName] + '' '' + a.[LastName] AS FullName,
s.[Phone] AS Phone,
s.[HomePhone] AS HomePhone,
a.[PersonalEmail] AS Email,
s.[ooStaffID],
s.[Email] AS AlternateEmail,
s.[Addr1] + '' '' + s.[Addr2] + '' '' + s.[City] + '','' + s.[State] + '' '' + s.[Zip] + '' '' + dbo.aaGetCountryDescription(s.[ooCountryId]) AS PrimaryAddress,
a.[GAddress1] + '' '' + a.[GAddress2] + '' '' + a.[GCity] + '' '' + a.[GState] + '' '' + a.[GZip] + '' '' + dbo.aaGetCountryDescription([GadCountryId]) AS ShippingAddress
FROM [aaStaff] a
INNER JOIN cmydb3.dbo.[ooStaff] s ON s.[ooStaffId] = a.[ooStaffId]
INNER JOIN Mydb2.dbo.JjUsers u ON u.ooStaffId = s.ooStaffID
LEFT JOIN mydb2.dbo.jjUserRoles
-- Check Whether to include Term Join
IF @TermID IS NOT NULL
SELECT @sql = @sql + ' INNER JOIN cmydb3.dbo.AdClassSchedTerm st ON st.AdTermID = @xTermID' + CHAR(13)
-- Check Whether to include Course Taught Join
IF @TaughtCourseID IS NOT NULL
SELECT @sql = @sql + ' INNER JOIN cmydb3.dbo.adClassSched sc ON sc.AdCourseID = @xTaughtCourseID AND convert(varchar,EndDate,101) < convert(varchar,GetDate(),101)' + CHAR(13)
-- Check Whether to include Course Taught Join
IF @StatusID IS NOT NULL
SELECT @sql = @sql + ' INNER JOIN Mydb2.dbo.aaStatuses sta ON sta.aaStatusID = @xStatusID' + CHAR(13)
-- Check Whether to include Program Join
IF @adProgramID IS NOT NULL
SELECT @sql = @sql + ' INNER JOIN Mydb2.dbo.aaStaffPrograms sp ON sp.ooStaffID = @xooStaffID AND sp.adProgramID = @xadProgramID' + CHAR(13)
-- Check Whether to include Can Teach Course Join
IF @CanTeachCourseID IS NOT NULL
SELECT @sql = @sql + ' INNER JOIN Mydb2.dbo.aaEligibleCourses ec ON ec.ooStaffID = @xooStaffID AND ec.adCourseID = @xCanTeachCourseID' + CHAR(13)
+ ' WHERE 1 = 1
AND s.[Active] = 1
AND u.[Active] = 1
AND u.[ooCampusID] = @xooCampusID' + CHAR(13)
IF @FirstName IS NOT NULL
SELECT @sql = @sql + ' AND (a.[FirstName] LIKE @xFirstName OR @xFirstName = ''%%'' OR @xFirstName IS NULL)' + CHAR(13)
IF @LastName IS NOT NULL
SELECT @sql = @sql + ' AND (a.[LastName] LIKE @xLastName OR @xLastName = ''%%'' OR @xLastName IS NULL)'
IF @debug = 1
print @sql
execute sp_executesql @sql , @paramlist, @xooCampusID = @ooCampusID, @xjjRoleID = @jjRoleID, @xooStaffID = @ooStaffID , @xTermID = @TermID ,
@xLastName = @LastName , @xFirstName = @FirstName , @xStatusID = @StatusID, @xTaughtCourseID = @TaughtCourseID,
@xadProgramID = @adProgramID, @xCanTeachCourseID = @CanTeachCourseID
Now here are the errors I get. I have not a clue why it's complaining about a comma. Does Line 4 mean from the start of my stored proc, or within the sql string? I have checked both, and I don't see the problem as it's not really bringing me to the line of the real error.
Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ','.
April 18, 2007 at 11:47 am
To make this easier, comment out the EXECUTE portion of this script at the bottom and then run it. If you don't get the error, then paste the results of your PRINT @SQL here.
April 18, 2007 at 11:47 am
I count the lines
1
2
3
4 Found it!
@xadProgramID, --<---- This line needs a data type.
N 56°04'39.16"
E 12°55'05.25"
April 18, 2007 at 11:48 am
declare the variable '@xvcRoleId'.
April 18, 2007 at 11:51 am
err...thanks, didn't see that one for the ProgramID. That ended up being it. Thank you!
April 18, 2007 at 11:53 am
Peter found at least one of the problems.
By the way, you're actually halfway there to debugging dynamic SQL, which is one of your complaints about it. That print statement you have is wonderful for that. Just print the string without executing it. You can even take the printed string, dump it into a QA window, and execute it manually to see what's wrong.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply