“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

    The service did not start due to a logon failure

    When I go to SQL Server Enterprise Manager to create a New SQL Server Registration for localhost, I got an error message saying “The service did not start due to a logon failure“.

    So, I copy this error message and google it. And quickly I found the solution. The problem is I have changed my network password and I didn’t update it at MSSQLSERVER. To do so, I went to Start > Settings > Control Panel > Administrative Tools > Services > MSSQLSERVER.

    Then, I right click to go to Properties. At the Log on tab, I changed the password. Then, I went to SQL Server Service Manager to start it. I managed to start it. Then, I went back to SQL Server Enterprise Manager to create a New SQL Server Registration for localhost and it works!

    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