March 12, 2007 at 9:57 pm
Hello all,
I am facing a problem in trying to have variables that scan across stored procedures. I want to declare variables at a central location and then use them as and when necessary. Sometimes the variables can work as constants while otherwise i want to set these to different values in different stored procedures. One way i know is to write a stored procedure itself and declare the variables in them and retrive them using if conditions, but then the values get reinitialized as the stored procedure redeclares the variables. Any help anyone? Would appreciate all the help i can get here.
Thanks and regards
Ramachandran
March 13, 2007 at 12:58 am
A variables table?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 13, 2007 at 2:05 am
you mean a #variables table?
March 13, 2007 at 2:17 am
CREATE TABLE dbo.ProcessVariables (
ProcessId int NOT NULL,
VariableID int NOT NULL,
Value sql_variant NULL,
CONSTRAINT PK_ProcessVariables PRIMARY KEY (ProcessId, VariableID),
CONSTRAINT FK_ProcessVariables_Process FOREIGN KEY (ProcessId) REFERENCES dbo.Process (ID),
CONSTRAINT FK_ProcessVariables_Variable FOREIGN KEY (VariableId) REFERENCES dbo.Variable (ID)
)
Don't forget to create referenced tables first.
You may add column SPID if you need to set different variables for processes started by different users (different connections).
_____________
Code for TallyGenerator
March 13, 2007 at 2:45 am
No, I meant a real table. Depending whether you need the variables global across all connections or global across all queries on a single connection you could have aa spid column (or not as needed)
Temp tables can get expensive to create and drop, especially if you're doing it frequently.
Ideally you'd want to delete the rows once done. Whatever proc sets the variables can also check to see if there are left-over variables with its spid and delete them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply