Wait for a while..

Here is the code that you need to put your process into sleep mode:

declare @wait_time nvarchar(255)

print 'start working on it.. => current ms: ' + cast(datepart(millisecond, getdate()) as nvarchar)

set @wait_time = '00:00:00.' + left(cast(cast(rand() * 1000 as int) as nvarchar), 3)
waitfor delay @wait_time

print 'im done and waited for ' + @wait_time
    + ' => current ms: '
    + cast(datepart(millisecond, getdate()) as nvarchar)

Concatenating the records and use appropriate separator

If we have 4 records, we want to concatenate all 4 records and the last separator should be "&" symbol. We can achieve this by using row_number() function and then decide whether the separator is "," or "&".

For example:

declare @s nvarchar(max)

create table #tb1 (
    email nvarchar(255)
)
insert into #tb1 values ('a@a.com');
insert into #tb1 values ('b@a.com');
insert into #tb1 values ('c@a.com');
insert into #tb1 values ('d@a.com');

select
    @s = coalesce(@s
        + case when rowidx < cnt then ',' else ' & ' end
        + email, email)
from (
    select
        rowidx=row_number() over( order by email )
        , email
        , cnt = (select count(*) from tb1)
    from #tb1
) as a

select @s

drop table #tb1

Code sharing

I've been thinking of sharing my codes for many years and finally I decided to do it.

I have uploaded the table structures, views, stored procedures and functions into Github. You may download and use it freely. I will update the contents if I encounter the useful script and table design.

  https://github.com/lauhw/MSSQLProc

Let me know if you have anything to share with me. ;)

Get the Guid.Empty constant in MS SQL

The following function returns the Guid.Empty (.Net constant) in MS SQL:

create function dbo.fn_empty_guid ()
returns uniqueidentifier
as
begin
    declare
        @result uniqueidentifier

    set @result = cast(cast(0 as varbinary) as uniqueidentifier)
   
    return @result
   
end
go

Usage:

select dbo.fn_empty_guid()   

Lock or no lock

While I was reviewing other programmer's work, I found out that most of the SELECT statements contain this keyword - "WITH (NOLOCK)". So, I posted this question to the programmer who is handling the job. His answer is quite simple, "we need this to reduce the query response time and then our program will run faster". And the actual fact is that, the programmer does not know the side effect of this keyword.

Yes, the query will run faster because it does not require to lock all the necessary records before reading it from the table. But, the side effects is that the data returned by the query might be "dirty" (i.e., the changes have not been committed).

If the program has read the data which have been rolled back by other user, it could lead to many unpredictable situation. This could lead to unsolvable mystery.

Concatenate all records into one line text

Below is the code that you can use for concatenating all records into one line text:

declare @s nvarchar(max)

select
    @s = coalesce(@s + ',' + prod_code, prod_code)   
from prod

select @s

Using dynamic SQL for reporting process

What is the user requirements for the report?

• Lots of criteria on the screen but most of them are optional.
• The final result is a subset of a result.
• User allows to choose different sorting order.
• User allows to choose different grouping.
• The fields in the report was specified by the user at runtime.

By using dynamic SQL, you will be able to compose the appropriate SQL statement and then meets the user expectation. Another thing is that, you don't need to have many static SQL statements in the stored procedure.