User Variables

Monday Nov 29th 1999 by Darren Green

Holding variables in a SQL table for later use

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]

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

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]

 @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)

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]

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

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'


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

This reads the value of the variable My_Variable, and assigns it the variable @The_value. It is then used as a parameter in a SELECT statement.


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

Mobile Site | Full Site