“SET NOCOUNT ON” vs “SET NOCOUNT OFF”

Let’s try this out. First, open your SQL Query Analyzer. Then, run these three lines of code. (I assume your SQL Server 2000 has default Pubs database).

Use Pubs
SET NOCOUNT ON
SELECT au_lname FROM authors

Then, change from SET NOCOUNT ON to SET NOCOUNT OFF and run them.

Use Pubs
SET NOCOUNT OFF
SELECT au_lname FROM authors

Spot the difference?

Okay, if you didn’t, the hint is at the left bottom tab. Click Messages tab. Got it? The difference is

  • SET NOCOUNT ON – the count is not returned
  • SET NOCOUNT OFF – the count is returned
  • Why we need to set it ON or OFF? Answer is performance. If set it ON, it has a better performance.

    Execute a stored procedure from another stored procedure

    You can execute a stored procedure from another stored procedure. This means that you can create a common stored procedure so that any stored procedure which needs this common stored procedure can execute it.

    Example, you have these three stored procedures. One is a common one. The other two are normal one.

  • sp_update
  • sp_delete
  • sp_common
  • Inside sp_update, you have

    UPDATE mytable SET status ‘N’

    Inside sp_delete, you have

    @ID int

    AS

    DELETE FROM mytable WHERE ID = @ID

    Inside sp_common, you have

    @ID int,
    @desc varchar(3000),
    @submit_by varchar(50),
    @submit_by varchar(50)

    AS

    INSERT INTO mytable (id, desc, submitby, submitdt, status)
    VALUES (@ID, @desc, @submit_by, @submit_dt, ‘N’)

    return 1

    If sp_update and sp_delete need sp_common, just add one more line (if there is no return value).

    Inside sp_update, add the line in bold

    UPDATE mytable SET status ‘N’

    EXEC sp_common

    Note: You can use either EXEC or EXECUTE. They are the same. But EXEC() is a different thing.

    If there is return value, then inside sp_update, add a declaration line and edit the existing execution line (in bold):

    DECLARE @status int

    UPDATE mytable SET status ‘N’

    EXEC @status = sp_common

    Fix null problem

    Somehow, null will always exists in the database.

    Without doing some null checking at your code, you will get an error.

    One way to fix it is by using isNull at your query. Basically this check, if counter is null, then a No value string will be assigned to it.

    SELECT isnull(counter,’No value’) AS counter FROM test

    So, the output will becomes,

    counter
    99
    12
    34
    No value

    Convert DateTime Data Type

    (This example was done using SQL Query Analyzer in SQL Server 2000)

    Say you have this table below,

    product_type log_datetime
    xbox 2008-06-11 16:26:46.000
    xbox 2008-06-13 16:30:09.653
    psp 2008-06-14 16:28:08.000
    ps 2008-06-16 16:21:40.000

    And you would like to display log_datetime in different format, how do you do it? One way is to do the formating at the query side. Example, if you want the date format to be dd mmm yyyy format, use CONVERT to format log_datetime like below

    SELECT product_type, convert(varchar(11),log_datetime,106) AS log_date FROM test ORDER BY log_datetime

    The output becomes,

    product_type log_datetime
    xbox 11 Jun 2008
    xbox 13 Jun 2008
    psp 14 Jun 2008
    ps 16 Jun 2008

    If you want to just show the time only, just change the 106 to 108 to become,

    SELECT product_type, convert(varchar(11),log_datetime,108) AS log_date FROM test ORDER BY log_datetime

    The output becomes,

    product_type log_datetime
    xbox 16:26:46
    xbox 16:30:09
    psp 16:28:08
    ps 16:21:40

    For more reference, http://doc.ddart.net/mssql/sql70/ca-co_1.htm