CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresdatabase-design
Joshua Frank
Joshua Frank

Reputation: 13848

Is it possible to make reusable queries or procs in TSQL?

My company's application is designed around large numbers of stored procedures, and the stored procedures are painfully non-modular. For instance, a common (anti-)pattern is:

IF @param = MAGIC_VALUE_1
  SELECT 20 fields 
  FROM 4 JOINED TABLES
  WHERE SOMEFIELD < 20
ELSE IF @param = MAGIC_VALUE_2
  SELECT 20 fields 
  FROM 4 JOINED TABLES
  WHERE SOMEFIELD < 40
ELSE IF @param = MAGIC_VALUE_3
  SELECT 20 fields 
  FROM 4 JOINED TABLES
  WHERE SOMEFIELD < 60
...3 or 4 more cases

The SELECT statements themselves are reasonable business logic, but they can be very complex and repeating them many times like this is horrible for comprehension and maintenance.

I want to refactor logic like this into reusable routines like what you can do in EntityFramework:

query = SELECT 20 fields 
FROM 4 JOINED TABLES

IF @param = MAGIC_VALUE_1
  query = SELECT * FROM query
          WHERE SOMEFIELD < 20
...3 or 4 more cases, differing ONLY in the where clause

or even better:

query = SELECT 20 fields 
FROM 4 JOINED TABLES

query = SELECT * FROM query
        WHERE applyWhereConditionFromMagicParam(query, @param)

Is there any way to get even a little closer to this more modular way of composing queries, so I can bring some sanity to our stored procedures?

Upvotes: 0

Views: 262

Answers (3)

High Plains Grifter
High Plains Grifter

Reputation: 1571

you could make a lookup table (whether as a table variable or temp table in the stored procedure, or as a database table), with columns for Procedure name, MAGIC_VALUE and value corresponding to the MAGIC_VALUE. You could then join to this table in the procedures using the procedure name OBJECT_NAME(@@PROCID) and the @param, to get the value associated with the parameter and procedure.

This approach, while still a bit unpleasant, will let you control the magic centrally and will be a consistent and easy to audit line in all the affected procedures. You could add an error check to see that the procedure name has not changed since the table was updated and that only allowable @param values are provided.

Upvotes: 0

paparazzo
paparazzo

Reputation: 45106

I may be missing something but
Just a function to return an int? 

query = SELECT * FROM query
        WHERE SOMEFIELD < db.MagicParam(@param)

Could also do this with a MagicParam table

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33581

In your example I would use a case expression to make this for less painful.

Where SomeField < case @param 
                    when MAGIC_VALUE_1 then 20
                    when MAGIC_VALUE_2 then 40
                    when MAGIC_VALUE_3 then 60
                    --etc...
                    end

Upvotes: 2

Related Questions