April 17, 2007 at 2:58 pm
Ok, I've spent literally all day on trying to get SQL to accept this ISNULL line. I am out of ideas. I know it is better to code this in sp_executesql but I'm not going there today because I am not as familiar with it. I did convert it to using that, but I find using sp_executesql hard to troubleshoot even when using print, etc.
So, I cannot get the ISNULL line syntax to be happy here:
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.[FeAddress1] + ' ' + a.[FeAddress2] + ' ' + a.[FeCity] + ' ' + a.[FeState] + ' ' + a.[FeZip] + ' ' + dbo.aaGetCountryDescription([FeadCountryId]) AS ShippingAddress
FROM [aaStaff] a
INNER JOIN mydb.dbo.[iiStaff] s ON s.[iiStaffId] = a.[iiStaffId]
INNER JOIN Mydb2.dbo.RrUsers u ON u.iiStaffId = s.iiStaffID
LEFT JOIN mydb2.dbo.rrUserRoles
' + CASE WHEN @TermID IS NULL THEN '' ELSE '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)
I have not gotten past these errors all afternoon in regards to that ISNULL and I've tried so many combinations such as taking out the quotes, etc...you name it.
Msg 170, Level 15, State 1, Line 33
Line 33: Incorrect syntax near ' + CASE WHEN @TermID IS NULL THEN ' ELSE '.
April 17, 2007 at 3:08 pm
Try this:
' + CASE WHEN IsNull(@TermID, 0) = 0 THEN '''' ELSE '
Since you are checking a string, it may be necessary to convert phrase to a string like this:
' + CASE WHEN convert(varchar(20), isnull(@TermID,0)) = 0 THEN '''' ELSE '
April 17, 2007 at 3:35 pm
I'm very confused by your statements about not using sp_executesql. Are you doing an EXEC (@sqlString), or are you trying to run the SELECT as is? If the former, then your case logic is the one thing that shouldn't be in a string, yet that's where you have it. If the latter, that's not going to work at all unless I'm missing something, as it certainly appears that you're attempting to use dynamic SQL, which has to be executed. Either way, as Keith West pointed out, to have a single quote show up inside a string, you have to double it up to "escape" the quote.
SELECT '''' for instance, will return a single quote. The outer single quotes are to denote that it's a string, while the double inner quotes denote a single quote.
To show an empty string, you use outer singles, as well as two pairs of inner singles, for the same reason, so it would be SELECT ''''''
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply