David Turvey's Blog

This is just an FYI, I’m sure most people know about this but I thought I’d blog about it because I’ve noticed a lot of stored procedures in the database at work that don’t take the schema into account when they drop the stored procedure.

When dropping some object from the database, e.g. a table or stored procedure, the following statement is often used:

IF EXISTS (
        SELECT *
        FROM sysobjects
        WHERE type = 'P' AND name = 'GetAlertGeographies' )
BEGIN
    DROP PROCEDURE [Members].[GetAlertGeographies]
END
GO

Now this might appear ok but it is not; the select statement that is checking the sysobjects table is not taking the schema into account. So this statement could attempt to drop the stored procedure even though it doesn’t exist because a stored procedure with the same name exists in a different schema.

This is the correct statement to use to take schemas into account:

IF EXISTS (
        SELECT TOP 1 NULL
        FROM information_schema.routines
        WHERE specific_name = 'GetAlertGeographies' AND specific_schema = 'Members' )
BEGIN
    DROP PROCEDURE [Members].[GetAlertGeographies]
END
GO

If you want to check for a table just change “routines” to “tables”.

Normally when you commit a change to the database using LINQ it will generate SQL to perform the insert/update/delete action. Most of the time this is fine, but LINQ does provide the ability to override this behaviour and specify a stored procedure that should be executed instead of the default behaviour. And luckily it’s really easy.

First, create your stored procedure, normally if you are creating an insert or update stored procedure the proc will have a parameter for each field but this is not a requirement as you can map the individual fields to the properties of the class.

Drag the stored procedure onto your DBML so that the LINQ framework creates a method for the stored procedure, in this example the stored procedure is called InsertLessee.

Then right click on the LINQ entity in the DBML and select Configure Behaviour.

Then (1) choose the behaviour to replace, (2) select Customize and select the stored procedure from the drop down list, in this case InsertLessee, and (3) if needed alter the stored procedure parameter to class property mapping.

It is as simple as that, now whenever a Lessee is inserted the LINQ framework will call the stored procedure instead of generating SQL at runtime.