January 2008 - Posts

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

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

Posted by Madhivanan with 3 comment(s)
Filed under: , ,

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