Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 29, 1999

User Variables

By Darren Green

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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM