How to make a table READ ONLY

Visakh Murukesh, SQL Server MVP and my friend, posted about SQLServer Tips: How To Make a Table Read Only in Database in which he showed many methods on how to make a table read-only

I know another method of doing it using a view.

Let us create the following dataset

create table sales_target(sales_year int, sales_target decimal(12,2))
insert into sales_target (sales_year,sales_target)
select 2015, 2000000 union all
select 2016, 2500000

Now create an view out of this table which has union/union all included as shown below
 

CREATE view sales_target_read_only as
select * from sales_target
union all
select * from sales_target where 1=0

Now let the user access to the view sales_target_read_only and not the underlying table sales_target.

If you try to add data to this view

insert into sales_target_read_only(sales_year,sales_target)
select 2017, 3000000

You get the following error

Msg 4406, Level 16, State 1, Line 2
Update or insert of view or function 'sales_target_read_only' failed because it contains a derived or constant field.

Similar error occurs when try to update this view

update sales_target_read_only set sales_target=10000 where sales_year=2015

The error is

Msg 4406, Level 16, State 1, Line 2
Update or insert of view or function 'sales_target_read_only' failed because it contains a derived or constant field.

If you try to delete rows from this view

delete from sales_target_read_only where sales_year=2015

You get this error

Msg 4426, Level 16, State 1, Line 1
View 'sales_target_read_only' is not updatable because the definition contains a UNION operator.


Different methods to find out parameters of a stored procedure

One of my fiends asked me if there is an easy to get the parameters used in the stored procedure. He did not want to use SP_HELP stored procedure because it returns two result sets.

Yes. There are atleast two methods I know other than SP_HELP system stored procedure

Assume the following stored procedure

create procedure proc_add_employee
(
@emp_id int,
@emp_name varchar(40),
@dob date,
@gender char(1),
@address varchar(100)
)
as
Begin
insert into employee(emp_id,emp_name,dob,gender,address)
select @emp_id,@emp_name,@dob,@gender,@address
End

Now you can use the below codes to get the list of parameters used.

1 Use INFORMATION_SCHEMA.PARAMETERS view

select parameter_name, data_type, character_maximum_length from information_schema.parameters
where specific_name='proc_add_employee'

The result is


parameter_name data_type  character_maximum_length
-------------- ---------  -------------------------
@emp_id        int        NULL
@emp_name      varchar    40
@dob           date       NULL
@gender        char       1
@address       varchar    100


2 Use SP_SPROC_COLUMNS system stored procedures

EXEC SP_SPROC_COLUMNS ‘proc_add_employee’

The result is

Let me know if you know any other methods

 

Empty string for non character datatypes

One of my friends asked me why an empty string for a datetime value is stored as 1900-01-01 00:00:00.000. Well you should not pass an empty string to a non- character data type because depends on the datatype SQL Server internally convert empty string and store it differently.

Let us consider the following example

declare @test table (int_col int, datetime_col datetime, float_col float,money_col money)
insert into @test (int_col, datetime_col, float_col,money_col)
select '','','',''
select * from @test

The result is

int_col datetime_col           float_col   money_col
------- ---------------------- ---------   -----------
0       1900-01-01 00:00:00.000   0         0.00

All empty strings are actually 0 and for datetime datatype 0 is Jan 01, 1900

select cast(0 as datetime)

results to

1900-01-01 00:00:00.000

 

My point is that you should not pass empty string to non-character datatype. If you want you need to set it to NULL. Passing empty string will result to default value of 0 in SQL Server and probably will throw error in other RDBMSs. I also think that SQL Server should throw an error in this case