Raise an error that the C# can catch

Often, our program execute stored procedure to do some processes. In the event of the data is missing or violate the business rules, the stored procedure should stop the process and return a useful message to the caller.

In order for the C#/ASP.net to be able to catch the exception, you have to pass in the appropriate values into raiserror() function.

For example:

  raiserror('This error will be able to catch by C#/asp.net',
11, 
1)

Just beware that the second parameter should start from 11 or otherwise C#/ASP.net will not be able to catch it.

To capture the current error, please use the following code:

declare @err_msg nvarchar(max),
        @err_severity int,
        @err_state int

set @err_msg = error_message()
set @err_severity = error_severity()
set @err_state = error_state()
 
raiserror(@err_msg,@err_severity,@err_state)

Converting the nvarchar value into date/time value

When you are importing the data from CSV file or other data source, you might require to convert the nvarchar(14) into date/time value. We assume that the column value is in a predefined format of 'yyyyMMddhhmmss' (14 characters).

Below function will be very handy in converting those values into date/time type:

create function fn_from_sys_date
(
 @dt_str as nvarchar(14)
)
returns datetime
begin

/*
9-may-12,lhw
-parse the date+time string and returns datetime type.

select 
 dbo.fn_from_sys_date('20120509174210'),
 dbo.fn_from_sys_date('20120509'),
 dbo.fn_from_sys_date('')
 

*/
 declare @result datetime,
  @dd nchar(2),
  @mm nchar(2),
  @yyyy nchar(4),
  @hh nchar(2),
  @minute nchar(2),
  @ss nchar(2)

 -- the value format is yyyymmddhhMMss
 if len(@dt_str) >= 8
 begin
  select 
   @yyyy = left(@dt_str, 4),
   @mm = substring(@dt_str, 5, 2),
   @dd = substring(@dt_str, 7, 2)
 end
 else
 begin
  -- min date.
  select 
   @yyyy = '1753',
   @mm = '01',
   @dd = '01'
 end

 if len(@dt_str) = 14
 begin 
  select
   @hh = substring(@dt_str, 9, 2),
   @minute = substring(@dt_str, 11, 2),
   @ss = substring(@dt_str, 13, 2)
 end
 else
 begin
  select 
   @hh = '00',
   @minute = '00',
   @ss = '00'
 end

 select @result = cast(@yyyy 
    + '-' + @mm 
    + '-' + @dd 
    + ' ' + @hh
    + ':' + @minute
    + ':' + @ss
   as datetime)

 return @result
end

Converting the date/time value into nvarchar

Sometimes, you have to store the data/time value into nvarchar format so that it can be understand by other programs that easier. You need this function to convert the date/time value into nvarchar:

create function fn_to_sys_date
(
 @dt as datetime
)
returns nvarchar(50)
begin

/*
9-may-12,lhw
-format the date value to human readable format. 

select dbo.fn_to_sys_date(getdate())

*/
  declare @result nvarchar(50)
  select @result = replace(convert(nvarchar,@dt,102), 
                           '.', 
                           '')
           + replace(left(convert(nvarchar, @dt, 114), 8), 
                            ':', 
                            '')

 return @result
end

Getting the records within the given range

For example, you have a voucher table which stores the vouchers old to the customer. Each voucher record store the voucher_no_start and voucher_no_end in INT data type (i.e., this means a voucher record is a booklet but not one voucher).

In order for you to check within the voucher booklet has been sold or not (if sold, there will be a record in the tb_voucher), must be able to tell that the start/end number key in by the user falls within any existing records or not.

To do this, you will have to pass in the @start/@end value(of course, you must modify the following statement into stored procedure).

declare @start int,
 @end int

select @start = 300,
 @end = 450

select 
  voucher_no_start, voucher_no_end
from 
  tb_voucher
where 
  (voucher_no_start <= @end or voucher_no_end < @start)
  and @start <= voucher_no_end

If the above query returns some rows, it means that the values between @start and @end already exist in the database.

Load the data for web page using "paging" query

For example, the table of "inv_master_huge" has around 1million rows and you would like to have a functionality for the user to scroll through whatever page that they want. I know this sounds like "no way" or "no user would scroll the data like this". In fact, after filtering the rows by date range for current month, you might still have around 1000 rows.  Anyway, the following query will help in improving the website response time.

The keyword is the "ROW_NUMBER() OVER (ORDER BY ...)".


create proc pr_get_inv_by_page (
    @start_idx int,
    @page_size int
)
as
begin

    select *
    from (
        select
            row_number() over (order by dt) as row_no,
            *
        from inv_master_huge
        -- you may include the date filtering clause here.

    ) as a
    where
        row_no between @start_idx and @start_idx + @page_size

end

Note: in case you allowed the user sort the data by field name, you will have to pass the select statement generated at runtime and pass it to "sp_executesql" stored proc

Sync the SQL user ID

After moving the database from one SQL server to another, the user ID (that has been created in the database) is no longer working. This is mainly due to the "SQL login ID" does not match with the "database user ID". The value that links between these ID-s are call the SID.


Before executing the fixes, you may wonder what has happened to the user ID. Execute the following query:

   use [my_database]
   go
   select * from sysusers where name = 'my_user_name'
   select * from master..syslogins where loginname = 'my_login_name'
   go


From the result, you might notice that the "sid" value in both queries are different. And this is the cause of the login problem.


In MSSQL, there is a system stored procedure that will help you to sync the SID value and it is called "sp_change_users_login".

To get the list of the mismatch user ID, execute this command.

    exec sp_change_users_login 'report'

To sync the database user ID with the SQL login ID:

   exec sp_change_users_login 'update_one', 'my_user_name', 'my_login_name'

Note: you have to replace "my_user_name" and "my_login_name" with the actual values.

Settings the value into a variable

Something that you should know when you are setting the value into a variable. The assignment (i.e., "=" operator) might not work as your expectation. So, try out the script below in the query window:

--declare a table variable
declare @t table (i int)
declare @ii int

-- append 2 record.
insert @t
select 1
union all
select 2

-- show all the records
select *
from @t

-- get the record that match '1'. The expected value in @ii is "1".
select @ii= i
from @t
where i = 1

-- Now, try to get the record that match '0'.
select @ii= i               
from @t
where i = 0                    --<< no record is '0'.

select @ii                    --<< this value is not NULL??!! What is this value??

When "i = 0", there is no record  in @t table variable and "@ii = i" will not be executed. This means the "null" value will not be set to @ii. If you want to get the null value for @ii, then, you should do this:

    select @ii = (select i from @t where i = 0)

Try it out and see the difference.