Madhivanan

Forcing integrity between tables and procedures

You can force integerity between tables and views with schemabinding when you create views so that tables cant be dropped until views that bind on tables are dropped. Unfortunately it is not possible to force this integrity between tables and procedures. Tables can be dropped though there are procedures referencing them

But if you use SQL Server 2005 or higher version, it is possible with DDL trigger which is one of finest features available

CREATE TABLE test_table(code int, names varchar(100))

GO
CREATE TRIGGER alert_me
ON DATABASE 
FOR DROP_TABLE 

AS
declare @table_name varchar(100)
select @table_name= right(ddl,charindex(' ',reverse(ddl))-1) from
(
select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','varchar(100)') as DDL
) as t 


If exists
(
select * from information_schema.routines
where routine_definition like '% '+@table_name+'%'
)

begin
Raiserror('One or more procedures depend on this table and cant be dropped' , 16, 1) 
Rollback
end

 
GO
 
create procedure test_sp
as
select * from test_table

GO
drop table test_table



Now you would get error as test_sp is referencing the table

Beware of Implicit conversions

Sometimes you may wonder why integer divisions are not giving exact result

Consider this example

Select 3/4

Select 4/3

Because both numerator and denominators are INTegers, results 0.75, 1.3333 are truncated to integers resulting 0 and 1 respectively

Suppose you want to find out percentage number of orders taken for each shipcity against total orders

select shipcity,count(*)/(select count(*) from northwind..orders)*100  as percentage
from northwind..orders
group by shipcity


The result of percentage column is 0

To avoid this, you need to convert one of the openrands by float ,decimal or multiply by 1.0

Select 3*1.0/4

Select 4*1.0/3

select shipcity,count(*)*1.0/(select count(*) from northwind..orders)*100 as percentage
from northwind..orders
group by shipcity