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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Nov 28, 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]
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

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

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date