User Variables

I have regular monthly proccesses that I run on and around SQL Server 7.
The majority of the process is controled via various SQL scripts for ease
of maintenance and debugging. One of the limitions with scripts is
that variable declaration is limited to a batch. To overcome this
I have a table which I use to store all the variables I require.
I can update the table values as required, and all of my scripts
read their variables from this one location.
The three componets required are defined below. I create them
all in the master DB, as indicated in the examples below.

Table Design

if exists (select * from sysobjects 
  where id = object_id(N'[dbo].[tbVariables]') 
  and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbVariables]
GO

CREATE TABLE [dbo].[variables] (
     [variable] [varchar] (30)  NOT NULL,
     [value]    [varchar] (255) NULL
)
GO

This creates the standard table. [variable] is the name of your variable
and [value] is obviously the value of that variable. It is only capable
of holding strings and numeric values, although you could add additional
columns for other data type if required, and filter for the required value
column in the extraction stored procedure (sp_varSelect).

sp_varInsert :  Inserting variables into your table

if exists (select * from sysobjects 
  where id = object_id(N'[dbo].[sp_varInsert]') 
  and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_varInsert]
GO

CREATE PROCEDURE sp_varInsert(
 @variable varchar(30), 
 @value varchar(255)) As
   IF EXISTS(SELECT * FROM tbVariables WHERE variable = @variable)
       DELETE FROM tbVariables WHERE variable = @variable
   
   INSERT tbVariables VALUES(@variable, @value)
GO

This creates the stored procedure sp_varInsert which is used to add variables
to the your variable table.

sp_varSelect :  Returning variables when required

if exists (select * from sysobjects 
  where id = object_id(N'[dbo].[sp_varSelect]') 
  and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_varSelect]
GO

CREATE PROCEDURE sp_varSelect( 
  @variable varchar(30), 
  @value varchar(255) OUTPUT) As
    SET @value = (SELECT value FROM tbVariables WHERE variable = @variable)
GO

This creates the stored procedure sp_varSelect which is used to read variables
from your variable table. You can of course query the table directly using the
SELECT statement deined in the stored procedure above.

Example 1: Setting a variable

EXEC master.dbo.sp_varInsert 'My_Variable', 'My_value'
GO

Example 2: Reading and using a variable

DECLARE @The_value varchar(255)
 EXEC master.dbo.sp_varSelect 'My_Variable', @The_Value OUTPUT
 SELECT * FROM tbAny_Table WHERE Field = @The_Value

See Example 3 from my Text File Connection file name page for how to use within a DTS package.