Roy Harvey
3/21/2007 2:05:00 PM
I am in complete agreement with Tom.
And there is one more factor to consider. ISNULL only works with two
parameters, while COALESCE works with any number of parameters. This
means that you will be using COALESCE some of the time anyway, so your
code will be cleaner if you simply standardize on COALESCE.
That said, I confess I started writing T-SQL before the implemented
COALESCE and my fingers seen to end up typing ISNULL without my
thinking about it. Old habits are hard to break.
Roy Harvey
Beacon Falls, CT
On Wed, 21 Mar 2007 09:43:13 -0400, "Tom Cooper"
<tomcooper@comcast.no.spam.please.net> wrote:
>Just to add to the comments from the sites Razvan gave you, I use Coalesce.
>And, I believe, most people in this newsgroup would recommend that as a best
>practice. Not because of any performance benefits (for any practical
>purpose, the performance is the same) and not because of the technical
>differences in the result returned from obscure cases and not even because
>Coalesce will accept more than two parameters. The primary reason is that
>Coalesce is a standard feature and IsNull is a Microsoft SQL Server
>proprietary feature. Now I am perfectly willing to use a feature that
>exists only in SQL Server when it gives me something that I cannot get with
>the standard features that are available. But in cases like this, where
>there is a standard feature that works just as well or better, I always try
>to use the standard method.
>
>Tom
>
>"Lee Clements" <lee.clements.nospam@btopenworld.com> wrote in message
>news:OKPeF34aHHA.2064@TK2MSFTNGP05.phx.gbl...
>> Thanks Tom, I have done a check on the Coalesce function and I think I
>> prefer it to using ISNULL().Is there any benefit in terms of performance
>> using one over the other, or maybe in terms of best practice?
>>
>> Thanks again for your assistance. This newgroup has proven to be extremely
>> useful with some very helpful people contributing. Once I am up to speed
>> ,hopefully I will be able to reciprocate and assist others with their
>> problems.
>>
>> Cheers
>> Lee
>>
>> "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message
>> news:%233TginzaHHA.1300@TK2MSFTNGP02.phx.gbl...
>>> When you concatenate two or more string variables and/or constants
>>> together, the result will be NULL if any of those vaiables or constants
>>> contain NULL. (Actually, there is a option you can set to change this,
>>> but using that option is not good practice). So when, for example,
>>> @Assistant was NULL, then the value you put into @TempData was NULL.
>>>
>>> The way PRINT works, is if you try to PRINT a value that is NULL, you
>>> get nothing, just a blank line. So, if you are using PRINT to debug
>>> something and the value might be NULL, you probably want to use Coalesce
>>> on the value you are PRINTing. To see an example of this, try running
>>> the following:
>>>
>>> Declare @Part1 varchar(10),
>>> @Part2 varchar(10),
>>> @Part3 varchar(10),
>>> @Result varchar(10)
>>> Set @Part1 = 'Abc'
>>> Set @Part2 = 'Def'
>>> Set @Part3 = 'Xyz'
>>>
>>> Print 'Without Nulls'
>>> Set @Result = @Part1 + @Part2 + @Part3
>>> Print @Result
>>>
>>> Print 'Now with nulls'
>>> Set @Part2 = Null
>>> Set @Result = @Part1 + @Part2 + @Part3
>>> Print @Result
>>>
>>> Print 'Now with nulls and Coalesce'
>>> Print Coalesce(@Result, 'Tried to print NULL value')
>>>
>>> Tom
>>>
>>> "Lee Clements" <lee.clements.nospam@btopenworld.com> wrote in message
>>> news:%23uBJrfyaHHA.4140@TK2MSFTNGP06.phx.gbl...
>>>> Ok, I have located the problem and as I suspected it was due to the null
>>>> values so I have encased each variable that could be null using
>>>> ISNULL(@variable,'n/a/'). Now all 5 records are returned regardless.
>>>>
>>>> However, I would like to understand why this caused a problem and
>>>> prevent the data from being assigned to @TempData if anybody knows?
>>>>
>>>> Many thanks
>>>> Lee
>>>>
>>>> "Lee Clements" <lee.clements.nospam@btopenworld.com> wrote in message
>>>> news:%23vPZ3KyaHHA.1400@TK2MSFTNGP06.phx.gbl...
>>>>>I am constructing a stored procedure to populate a temporary table, I
>>>>>have created and tested the queries in isolation to be sure they all
>>>>>work, the first query:
>>>>>
>>>>> SELECT tblJobs.job_id, tblJobs.job_date, O.staff_name AS owner,
>>>>> A.staff_name AS assistant, tblJobs.job_site, tblJobs.job_notes,
>>>>> tblCars.car_name
>>>>> FROM tblJobs LEFT JOIN
>>>>> tblStaff AS O ON tblJobs.job_owner = O.staff_id LEFT JOIN
>>>>> tblStaff AS A ON tblJobs.job_assistant = A.staff_id LEFT JOIN
>>>>> tblCars ON tblJobs.job_car = tblCars.car_id
>>>>> WHERE job_date BETWEEN '3/25/07' AND '3/31/07'
>>>>> AND tblJobs.job_owner = 100
>>>>>
>>>>> returns all the records I am expecting, 5 in total. You need to know at
>>>>> this point (because I think it is the cause of the error) that the
>>>>> car_id and the job_assistant fields can be NULL. Of the 5 records
>>>>> returned 2 have a NULL value for job_assistant and 1 has a NULL value
>>>>> for car_id.
>>>>>
>>>>> When I insert the exact same query into a stored proc using a cursor I
>>>>> only get back the two records that do not possess any NULL values in
>>>>> any of the fields. The extract from the stored proc is listed below
>>>>> (again I have been testing this as is, in isolation to be sure nothing
>>>>> else in my stored proc is affecting the results). You will see that at
>>>>> the end of the WHILE I am printing out the @@FETCH_STATUS and this
>>>>> prints out 0 for each iteration. There are definately 5 records being
>>>>> returned because I see 5 zero's printed, but for some reason there is a
>>>>> problem getting the data into the variables and I need to understand
>>>>> how to capture this.
>>>>>
>>>>> -- declare variables to hold dataset from job_detail_cursor
>>>>> DECLARE @JobID int, @JobDate datetime, @Owner nvarchar(50),
>>>>> @Assistant nvarchar(50), @Site nvarchar(50), @Notes nvarchar(max),
>>>>> @CarName nvarchar(50), @TempData nvarchar(1000);
>>>>>
>>>>> -- Declare an inner cursor based on staff_id from the outer cursor.
>>>>> DECLARE job_detail_cursor CURSOR FOR
>>>>> SELECT tblJobs.job_id, tblJobs.job_date, O.staff_name AS owner,
>>>>> A.staff_name AS assistant, tblJobs.job_site, tblJobs.job_notes,
>>>>> tblCars.car_name
>>>>> FROM tblJobs LEFT JOIN
>>>>> tblStaff AS O ON tblJobs.job_owner = O.staff_id LEFT JOIN
>>>>> tblStaff AS A ON tblJobs.job_assistant = A.staff_id LEFT JOIN
>>>>> tblCars ON tblJobs.job_car = tblCars.car_id
>>>>> WHERE job_date BETWEEN '3/25/07' AND '3/30/07'
>>>>> AND tblJobs.job_owner = 100
>>>>>
>>>>> -- open the cursor and get the first recordset
>>>>> OPEN job_detail_cursor
>>>>> FETCH NEXT FROM job_detail_cursor INTO @JobID, @JobDate, @Owner,
>>>>> @Assistant, @Site, @Notes, @CarName
>>>>>
>>>>> -- keep processing until there are no more records
>>>>> WHILE @@FETCH_STATUS = 0
>>>>> BEGIN
>>>>> SET @TempData = 'Job Ref: ' + CAST(@JobID as varchar) + CHAR(13) +
>>>>> 'Site: ' + @Site + CHAR(13);
>>>>> SET @TempData = @TempData + 'Car: ' + @CarName + CHAR(13);
>>>>> SET @TempData = @TempData + 'Assistant: ' + @Assistant + CHAR(13) +
>>>>> 'Notes: ' + @Notes + CHAR(13);
>>>>>
>>>>> -- print the data for debug purposes and then insert into temp table
>>>>> PRINT @TempData;
>>>>>
>>>>> -- get details of next job
>>>>> FETCH NEXT FROM job_detail_cursor INTO @JobID, @JobDate, @Owner,
>>>>> @Assistant, @Site, @Notes, @CarName;
>>>>>
>>>>> -- for debug
>>>>> PRINT @@FETCH_STATUS;
>>>>> END
>>>>>
>>>>> -- clean-up and close the cursor
>>>>> CLOSE job_detail_cursor
>>>>> DEALLOCATE job_detail_cursor
>>>>>
>>>>> Again, your help and guidance is very much appreciated. I have learnt
>>>>> more about SQL Server in the last 2 days than in the last 4 years of
>>>>> programming (of course I always had the data access layer created for
>>>>> me and never got exposed to these sort of issues).
>>>>>
>>>>> Cheers
>>>>> Lee
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>