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 Msg 170, Level 15, State 1, Line 29 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 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 '' ''
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.[iiCountry
a.[PrefAddress1] + ' ' + a.[PrefAddress2] + ' ' + a.[PrefCity] + ' ' + a.[PrefState] + ' ' + a.[PrefZip] + ' ' + dbo.aaGetCountryDescription([PrefadCount
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)
Line 29: Incorrect syntax near 'ISNULL'.
Msg 170, Level 15, State 1, Line 35
Line 35: Incorrect syntax near ')'.
No comments:
Post a Comment