CODEWITHSUNDEEP
sql-serversql-server-2008where-clause
Gareth
Gareth

Reputation: 5243

Common WHERE Clause Across Multiple Stored Procedures

I have a number of queries that all use the same WHERE clauses for example:

--Query1
CREATE PROC Query1
    @Param int
AS
BEGIN
    SELECT
        field2,
        COUNT(field2)

    FROM
        some_table

    WHERE
        field1 = condition1
        AND field2 = @Param

    GROUP BY
        field1
END

--Query2
CREATE PROC Query2
    @Param int
AS
BEGIN
    SELECT
        field2,
        COUNT(field2)

    FROM
        some_table

    WHERE
        field1 = condition1
        AND field2 = @Param

    GROUP BY
        field2
END

I'm wondering if I can create a function or some sort that I can use across the queries to prevent repeating the code and for better maintainability?

Upvotes: 1

Views: 200

Answers (2)

mikeb
mikeb

Reputation: 11267

You could create a view to run the queries against, the you have the same issue with refreshing. If you create the view with select *, you make the view with the columns that match the * at the time you created the view.

CREATE_VIEW func_view AS 
SELECT *
      FROM   some_table
      WHERE  field1 = condition1

Then, run your selects like select * from func_view group WHERE field2 = @Param

Upvotes: -1

Martin Smith
Martin Smith

Reputation: 453028

You would use an inline table valued function for this

CREATE FUNCTION dbo.some_function 
(   
    @Param INT
)
RETURNS TABLE 
AS
RETURN 
(
      SELECT *
      FROM   some_table
      WHERE  field1 = condition1
             AND field2 = @Param
)

Then your procedures would use

SELECT field1,
       COUNT(field1)
FROM   dbo.some_function(@Param) 
GROUP  BY field1

or

SELECT field2,
       COUNT(field2)
FROM   dbo.some_function(@Param) 
GROUP  BY field2

If you do use * in the function definition you must remember to refresh it if the underlying table definition changes with sp_refreshsqlmodule to avoid strange results so for that reason it is probably better to just list the columns explicitly.

Upvotes: 2

Related Questions