SQL Reuse

One of my goals for this year is to advance my SQL knowledge.

The real way of doing this is to constantly ask questions of how you do things. I am very lucky as I work beside some really top SQL bods, who i like to bounce different ideas with.

The one thing I think you need to keep in mind is the difference between a SQL developers mentality and a C# developers mentality (the camp i sit in).

Since there are many solutions too different problems, I like to try and work out the best solution (sometimes I see it, sometimes I don't), in yesterdays case I didn't. Thanks to Nick for helping me out on this one.

I had an existing stored proc, which parsed the syscomments table to work out the default parameter for a specified stored procedure. I wanted to ramp this up so i could get the default parameter for all stored procedures. Now this is a batch job, so performance is less of an issue but nonetheless I wanted production level code (with the best performance).

I saw two solutions to this problem:

1) Convert my stored proc to a UDF and then perform the calc for each stored proc as part of a join with sys.objects and sys.parameters
2) Get all the stored procs + parameters stored in a temp table, and then loop over the table, calling my default parameter stored proc.

I thought like a C# developer (caring more about reuse), rather than a SQL developer.

The real solution was to analyse the stored procedure to see what it was doing rather than just trying to reuse it out of the box. This led me to solution 3 :

3) Join to syscomments with my stored proc, and use a UDF to perform the string manipulation to retrieve the default parameter (passing the stored proc text into the UDF), rather than allowing the UDF (per solution 1) to make the call to the syscomments table.

I feel my quest has taken a great leap forward today :)