April 16, 2007 at 2:24 pm
Dynamic SQL when used right, as in a parameter to hold the sql, is one of the better ways to code it if you absoutely must use dynamic SQL. But what bothers me, is then, sometimes in order to do basically a join, I end up having to do it in the WHERE clause if I want that join to be based on a check. This bothers me because you should never do a join through a where clause. So is there another way to to the INNER Join here before the where clause still but dependent on if @TermID has a value?
SELECT @sql = 'a.[FirstName] + '' '' + a.[LastName] AS FullName,
s.[Phone] AS Phone,
s.[HomePhone] AS HomePhone,
a.[PersonalEmail] AS Email,
s.[Email] AS AlternateEmail,
s.[Addr1] + '' '' + s.[Addr2] + '' '' + s.[City] + '','' + s.[State] + '' '' + s.[Zip] + '' '' + dbo.aaGetCountryDescription(s.[syCountryId]) AS PrimaryAddress,
a.[PrefAddress1] + '' '' + a.[PrefAddress2] + a.[PrefCity] + '' '' + a.[PrefState] + a.[PrefZip] + '' '' + dbo.aaGetCountryDescription(a.[PrefadCountryId]) AS ShippingAddress
FROM aaStaff a
LEFT JOIN mydb.dbo.[syStaff] s ON s.[syStaffId] = a.[syStaffId]
LEFT JOIN VirtualCampus.dbo.VcUsers u ON u.syStaffId = s.syStaffID
LEFT JOIN virtualcampus.dbo.vcUserRoles
INNER JOIN mydb.dbo.AdClassSchedTerm st ON st.AdTermID = @TermID
WHERE s.Active = 1'
So looking at above, I need to check if @TermID has a value, since this is an optional selection in my .NET Web Search UI. but I don't think you can put an IF statement right before the inner join to say only join if there is a value to join against.
So what you end up having to do is perform an IF in the where clause and do a WHERE mydb.dbo.AdClassSchedTerm st ON st.AdTermID = @TermID
This totally defeats the right way to do it, with INNER JOIN commands properly is my beef!
April 16, 2007 at 2:41 pm
1) Why are you using dynamic sql or this ?
2) there is something wrong itn the last innerjoin what are you innerjoining with ? (there is no match with the other tables !!)
IMO the on-clause you have specified for the innerjoin should be a where clause !
3 If you insist on using dynamic sql, you micht consider using :
SELECT @sql = 'a.[FirstName] + '' '' + a.[LastName] AS FullName,
s.[Phone] AS Phone,
s.[HomePhone] AS HomePhone,
a.[PersonalEmail] AS Email,
s.[Email] AS AlternateEmail,
s.[Addr1] + '' '' + s.[Addr2] + '' '' + s.[City] + '','' + s.[State] + '' '' + s.[Zip] + '' '' + dbo.aaGetCountryDescription(s.[syCountryId]) AS PrimaryAddress,
a.[PrefAddress1] + '' '' + a.[PrefAddress2] + a.[PrefCity] + '' '' + a.[PrefState] + a.[PrefZip] + '' '' + dbo.aaGetCountryDescription(a.[PrefadCountryId]) AS ShippingAddress
FROM aaStaff a
LEFT JOIN mydb.dbo.[syStaff] s ON s.[syStaffId] = a.[syStaffId]
LEFT JOIN VirtualCampus.dbo.VcUsers u ON u.syStaffId = s.syStaffID
LEFT JOIN virtualcampus.dbo.vcUserRoles ur ON ur.vcUserId = u.vcUserId AND ur.vcRoleId = 5
' + case when isnull(@TermID, '') = '' then '' else ' INNER JOIN mydb.dbo.AdClassSchedTerm st ON st.AdTermID = @TermID ' end + '
WHERE s.Active = 1'
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 16, 2007 at 2:45 pm
Why are you using dynamic sql or this ?
because, I have a bunch of params incoming from a search interface where the user is able to either select or not select search criteria!
April 16, 2007 at 2:47 pm
the reason I need to perform an IF on that @TermID is because the user may have not selected a TErm in the Search UI. Thus, if that param is null, then you cannot just do an inner join in that case. So of course I need to check that to see if there's a valid ID, and only then do I join.
You should never do a Where match on an ID field in my experience, that's where you do a JOIN. It's poor code to match on IDs in the where clause and defeats the entire purpose of JOINS. However, when you have an ID field that is incoming as part of a search UI, and that ID may be null..if hte user hasn't selected a value in that dropdown field, then of course you can't just assume a JOIN unless the user has selected a value and that ID has a proper value.
April 16, 2007 at 3:05 pm
SELECT @sql = 'a.[FirstName] + '' '' + a.[LastName] AS FullName,
s.[Phone] AS Phone,
s.[HomePhone] AS HomePhone,
a.[PersonalEmail] AS Email,
s.[Email] AS AlternateEmail,
s.[Addr1] + '' '' + s.[Addr2] + '' '' + s.[City] + '','' + s.[State] + '' '' + s.[Zip] + '' '' + dbo.aaGetCountryDescription(s.[syCountryId]) AS PrimaryAddress,
a.[PrefAddress1] + '' '' + a.[PrefAddress2] + a.[PrefCity] + '' '' + a.[PrefState] + a.[PrefZip] + '' '' + dbo.aaGetCountryDescription(a.[PrefadCountryId]) AS ShippingAddress
FROM aaStaff a
LEFT JOIN mydb.dbo.[syStaff] s ON s.[syStaffId] = a.[syStaffId]
LEFT JOIN VirtualCampus.dbo.VcUsers u ON u.syStaffId = s.syStaffID
LEFT JOIN virtualcampus.dbo.vcUserRoles ur ON ur.vcUserId = u.vcUserId AND ur.vcRoleId = 5
' + ISNULL('INNER JOIN mydb.dbo.AdClassSchedTerm st ON st.AdTermID = ' + @TermID, '') + '
WHERE s.Active = 1'
If no criteria selected @TermID must be NULL to make it work.
You may use NULLIF function to make NULL from zero length string.
_____________
Code for TallyGenerator
April 16, 2007 at 3:10 pm
what if you want to check for zero or one, like it's boolean. Or check for an empty string?
April 16, 2007 at 3:13 pm
Empty string gonna be '''''', not ''
_____________
Code for TallyGenerator
April 16, 2007 at 3:32 pm
ouch...Thanks.
April 17, 2007 at 8:01 am
You could use CASE statements to include/exclude JOINs as in the example below:
SELECT @sql =
'SELECT a.[FirstName] + '' '' + a.[LastName] AS FullName,
s.[Phone] AS Phone,
s.[HomePhone] AS HomePhone,
a.[PersonalEmail] AS Email,
s.[Email] AS AlternateEmail,
s.[Addr1] + '' '' + s.[Addr2] + '' '' + s.[City] + '','' + s.[State] + '' '' + s.[Zip] + '' '' + dbo.aaGetCountryDescription(s.[syCountryId]) AS PrimaryAddress,
a.[PrefAddress1] + '' '' + a.[PrefAddress2] + a.[PrefCity] + '' '' + a.[PrefState] + a.[PrefZip] + '' '' + dbo.aaGetCountryDescription(a.[PrefadCountryId]) AS ShippingAddress
FROM aaStaff a
LEFT JOIN mydb.dbo.[syStaff] s ON s.[syStaffId] = a.[syStaffId]
LEFT JOIN VirtualCampus.dbo.VcUsers u ON u.syStaffId = s.syStaffID
LEFT JOIN virtualcampus.dbo.vcUserRoles ur ON ur.vcUserId = u.vcUserId AND ur.vcRoleId = 5 '
+ (CASE WHEN @TermID IS NULL THEN ''
ELSE
' INNER JOIN mydb.dbo.AdClassSchedTerm st ON st.AdTermID = ' + @TermID
END) + ' ' +
'WHERE s.Active = 1'
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply