Monday, March 26, 2012

Incorrect syntax near 'ISNULL'

I'm trying to get that ISNULL happy below but all my attempts (taking out the quotes, etc.) have not worked...so I'm posting to see if someone else can find the simple fix that I cannot. It must be simple?!?!

DECLARE @.iiCampusID AS INT,
@.iiStaffID AS INT,
@.TermID AS INT,
@.LastName AS varchar(50),
@.FirstName AS varchar(50),
@.CourseID INT,
@.StatusID INT,
@.debug BIT

SET @.FirstName = 'John'
SET @.TermID = 1
SET @.iiCampusID = 5
SET @.debug = 1

-- Get Parent Row information for Parent Results Grid
SELECT a.[FirstName] + ' ' + a.[LastName] AS FullName,
s.[Phone] AS Phone,
s.[HomePhone] AS HomePhone,
a.[PersonalEmail] AS Email,
s.[iiStaffID],
s.[Email] AS AlternateEmail,
s.[Addr1] + ' ' + s.[Addr2] + ' ' + s.[City] + ',' + s.[State] + ' ' + s.[Zip] + ' ' + dbo.aaGetCountryDescription(s.[iiCountryId]) AS PrimaryAddress,
a.[PrefAddress1] + ' ' + a.[PrefAddress2] + ' ' + a.[PrefCity] + ' ' + a.[PrefState] + ' ' + a.[PrefZip] + ' ' + dbo.aaGetCountryDescription([PrefadCountryId]) AS ShippingAddress
FROM [aaStaff] a
INNER JOIN mydb.dbo.[iiStaff] s ON s.[iiStaffId] = a.[iiStaffId]
INNER JOIN Mydb2.dbo.EeUsers u ON u.iiStaffId = s.iiStaffID
LEFT JOIN mydb2.dbo.eeUserRoles ur ON ur.eeUserId = u.eeUserId AND ur.eeRoleId = 1
' + ISNULL('INNER JOIN mydb.dbo.AdClassSchedTerm st ON st.AdTermID = ' + convert(varchar, @.TermID), '') + '
WHERE AND u.[Active] = 1
AND u.[iiCampusID] = @.iiCampusID
IF @.FirstName IS NOT NULL
AND (a.[FirstName] LIKE @.FirstName OR @.FirstName = '%%' OR @.FirstName IS NULL)
IF @.LastName IS NOT NULL
AND (s.LastName like @.LastName OR @.LastName = '%%' OR @.LastName is null)

Msg 170, Level 15, State 1, Line 29
Line 29: Incorrect syntax near 'ISNULL'.
Msg 170, Level 15, State 1, Line 35
Line 35: Incorrect syntax near ')'.

You can't use control of flow statements in a query. You could use CASE expression. But there are better ways to write this query. Please take a look at the link below for various techniques:

http://www.sommarskog.se/dyn-search.html

|||Yea, I was using that article this entire week. However, it's a huge amount of work to do the sp_executesql...in terms of getting the damn string of sql to be happy and all sorts of tricks. Also, it's very hard to troubleshoot when the sql is thrown into a variable as I found out|||

I don't know man, I just cannot get this thing to work, even with yours:

FROM [aaStaff] a

INNER JOIN mydb.dbo.[eeStaff] s ON s.[eeStaffId] = a.[eeStaffId]

INNER JOIN mydb2.dbo.iiUsers u ON u.eeStaffId = s.eeStaffID

LEFT JOIN mydb2.dbo.iiUserRoles ur ON ur.iiUserId = u.iiUserId AND ur.vcRoleId = 1

' + CASE WHEN @.TermID IS NULL THEN '' ELSE 'INNER JOIN mydb.dbo.iiClassSchedTerm st ON st.iiTermID = ' + convert(varchar, @.TermID) + '

WHERE AND u.[Active] = 1

Msg 156, Level 15, State 1, Line 31

Incorrect syntax near the keyword 'WHERE'.

Msg 170, Level 15, State 1, Line 36

Line 36: Incorrect syntax near ')'.

FROM [aaStaff] a

INNER JOIN mydb.dbo.[eeStaff] s ON s.[eeStaffId] = a.[eeStaffId]

INNER JOIN mydb2.dbo.iiUsers u ON u.eeStaffId = s.eeStaffID

LEFT JOIN mydb2.dbo.iiUserRoles ur ON ur.iiUserId = u.iiUserId AND ur.vcRoleId = 1

' + CASE WHEN @.TermID IS NULL THEN '' '' ELSE 'INNER JOIN mydb.dbo.iiClassSchedTerm st ON st.iiTermID = ' + convert(varchar, @.TermID) + '

WHERE AND u.[Active] = 1

same error after I changed it to '' ''

No comments:

Post a Comment