T-SQL Global / Shared Data

Question

When writing TSQL stored procedures I find myself wanting to centralize / normalize some parts of the code. For example, if I have a query like this:

SELECT * FROM SomeTable WHERE SomeColumn IN (2, 4, 8)

For cases like this I would like to put (2,4,8) in some place outside of the procedure that I could reuse in some other query later -- to avoid repetition. Is there a built-in way to do this? What would really neat is if I could break apart entire pieces of SQL code, like parts of the WHERE clause, and reuse those in other queries, but I doubt this is possible.

Thanks.

Answer 1

I've often wanted a similar thing but that specifically does not exist. Here are a couple of things you can do.

Option 1

What we have done is used User Defined Functions (UDF) to gather what you might call global variables.

You're able to call a UDF inline within your query which makes it really useful.

Suppose you wanted to specify a server name which you'd use across multiple stored procedures. Duplicating that value wouldn't be optimally maintainable. Instead you might do something like:

select * from clientNodes where serverName = dbo.SOME_SERVER_NAME()

Option 2

This one is more obvious but worth pointing out. Keep your values in a lookup table and reference it by an ID. The ID wouldn't change but the value it refers to might. Using the sample example as above but for this option:

Table: Servers
Columns: ServerID, ServerName

declare @serverName varchar(50)
select @serverName = ServerName from Servers where ServerID = 1

This is a typical approach to database normalization but people don't necessarily think about this for purposes of keeping DB-logic data centralized.

I hope that helps! Ian

Answer 2

With SQL Server 2005, you could consider using a table-valued function.

E.g.:

SELECT ... FROM SomeTable INNER JOIN SomeFunction() F ON SomeTable.SomeColumn = F.Id

...

CREATE FUNCTION SomeFunction()
RETURNS @IdTable TABLE (Id INT)
AS
RETURN
(
    SELECT 2 Id
    UNION ALL
    SELECT 4 
    UNION ALL
    SELECT 8
)

More commonly, you might want to pass in the values as an argument to a stored procedure. You could do this as a comma-separated string. E.g.

EXEC MyProcedure('2,4,8')

...

CREATE PROCEDURE MyProcedure
(
   @IdString AS VARCHAR(MAX)
)
AS
BEGIN
    SELECT ... FROM SomeTable INNER JOIN SomeFunction(@IdString) F ON SomeTable.SomeColumn = F.Id
END

...

CREATE FUNCTION dbo.SomeFunction
(
  @IdString VARCHAR(MAX)
)
RETURNS @IdTable TABLE (Id INT)
AS
BEGIN
    DECLARE @CommaIndex INT, @TotalLength INT, @StartIndex INT, @Id VARCHAR(10)
    SET @TotalLength=LEN(@IdString)
    SET @StartIndex = 1

    WHILE @StartIndex <= @TotalLength
    BEGIN
     SET @CommaIndex = CHARINDEX(',', @IdString, @StartIndex)
     IF @CommaIndex > 0
     BEGIN
      SET @Id = SUBSTRING(@IdString, @StartIndex, @CommaIndex-@StartIndex)
      SET @StartIndex = @CommaIndex + 1
     END
     ELSE
     BEGIN
      Set @Id = SUBSTRING(@IdString, @StartIndex, @TotalLength-@StartIndex+1)
      SET @StartIndex = @TotalLength+1
     END
     INSERT INTO @IdTable
     (Id)
     VALUES
     (CAST(@Id AS INT))
    END

    RETURN
END

Answer 3

You would usually create a VIEW for what you are trying to accomplish.

First create a function that returns SomeColumn values 2, 4, 8

create function fnSomeTableFilters()
returns @Result table ( ID int not null )
as

begin
    insert @Result(ID)
    select 2
    union select 4
    union select 8

    return
end
GO

Now Create a View that filters 2,4,8

create view vwFilteredSomeTable
as
select * from SomeTable where SomeColumn in (select ID from dbo.fnSomeTableFilters())

Last, your query becomes

SELECT * FROM vwFilteredSomeTable

Make sure to give your view a meaningful name

e.g) I have a view called vwActiveSites for to query against Active sites as name suggests

create view vwActiveSites
as
    select ...
    from Sites S
    where S.Active = 1

The beauty here is that, all your queries that uses vwFilteredSomeTable does not need to change. If you need to filter with different value, only fnSomeTableFilters need be changed

Answer 4

Yeah - use a table or a view. SomeColumn being IN (2, 4, 8) must be meaningful to your data somehow, so model it that way. Give it a meaningful name, and you can reuse it by itself or JOINed to other queries. Why bury it in a UDF or some such?

Eg., if SomeColumn was State and the values ('FL', 'GA', 'SC') were your South Eastern Territories, then instead of spreading:

 SELECT * FROM SomeTable WHERE State IN ('FL', 'GA', 'SC')

all around, just create a Territory column and be done with it.

Repeated use of a static WHERE clause is another table or attribute (aka a concept) just begging to come out.