Global variables in SQL

Question

Let's say I wanted to create a sql script and do something like this:

DECLARE @SomeVariable int
SET @SomeVariable = 'VALUE'
  FROM someTable
--do stuff with @SomeVariable
GO

CREATE PROCEDURE myProcedure
(
  @MyParameter
)
AS
SET NOCOUNT ON

--Do something
--Do something using @SomeVariable
SET NOCOUNT OFF
RETURN 0
GO

I can't, because @SomeVariable dies with the batch he belongs to, and myProcedure requires its own batch. Obviously I could create a #temp table and stuff any values I needed in there, but then I would have to select from it - adding code that, while trivial, hurts readability and seems silly when all I need is a global variable. Is there a better way?

To be painfully clear. I KNOW SQL Server has "global variables" called "tables" - I mentioned in the above paragraph that using a #table is a possible solution, as is using an actual permanent table. What I'm looking for here is probably more of a global constant that I can use anywhere within a given script, not a global variable - so we can all stop wetting our pants about the evils of global variables.

Answer 1

The GO statement, which is not part of the SQL language specification, is a batch separator. Your local variables are scoped to the batch. Therefore, they go out of scope at the GO statement. I think your only alternative is something along the lines of what you described.

Answer 2

If you are after some globally persistent value that multiple different procedures can use then storing it in a table is the best I can think of.

If you just want a variable that you use within a single procedure multiple times then you include it within the procedure definition.

CREATE PROCEDURE myProcedure 
( 
  @MyParameter 
) 
AS 
SET NOCOUNT ON 

DECLARE @SomeVariable int 
SET @SomeVariable = 'VALUE' 
  FROM someTable 
--do stuff with @SomeVariable 

--Do something 
--Do something using @SomeVariable 
SET NOCOUNT OFF 
RETURN 0 
GO 

And if you want some nice encapsulation of logic that you can reference in a convenient way, then a scalar User Defined Function (UDF) could be what you are after.

Answer 3

Global variable is bad practice in any programming language. Why not just pass the variable as a parameter in the stored procedure.

CREATE PROCEDURE myProcedure
(
  @MyParameter,
  @SomeVariable  -- the global variable
)
AS
...

Answer 4

How about creating a table called something like dbo.Configuration, which you can store a bunch of values in, pulling them out when you need? It'll be one or two pages at most, so quick to access, probably left in RAM the whole time, and you can dip into it from anywhere.

Answer 5

I am thinking that we need a bit more context on this, as I don't really see the point here, but I can give you a bit of insight based on what I do know.

You have two different batches here, one that is just a regular block of code, and the other that, does NOT actually do anything but creates a stored procedure.

If you need the stored procedure to have a value to work with, just make it an input.

If you need to share the above value across stored procedures, potentially you could use a scalar function to return the value that you need on demand.

Answer 6

It isn't clear why the stored proc has a dependency on your global in your example set of two batches. I see two main possibilities: either the SP has a dependency on the global at time of creation (i.e. code generation - Case 1), or the SP has a runtime dependency on the global (i.e. you must choose between parameterization - Case 2 - or self-configuration - Case3).

In the case of runtime dependency, whether that is obtained from some place outside the SP and passed in as a parameter or inside the SP directly is the basic design decision. The choice of when to pass data as a parameter and when to pull from tables is not exactly a science, it all depends on all the real-world usage cases in the system.

Case 1 - Code generation:

DECLARE @SomeVariable int 
SET @SomeVariable = 'VALUE' 
  FROM someTable 
--do stuff with @SomeVariable 
GO 

DECLARE @sp as varchar(MAX)

SET @sp = '
CREATE PROCEDURE myProcedure -- I would actually name this myProcedure_ + CONVERT(varchar, @SomeVariable), since each proc generated might function differently
( 
  @MyParameter 
) 
AS 
SET NOCOUNT ON 
DECLARE @SomeVariable AS int -- This is going to be an initialized local copy of the global at time of SP creation
SET @SomeVariable = ' + CONVERT(varchar, @SomeVariable) + '

--Do something 
--Do something using @SomeVariable 
SET NOCOUNT OFF 
RETURN 0 
'
EXEC(@sp) -- create the procedure dynamically

Executing the producedure normally as EXEC myProcedure or EXEC myProcedure_1, etc.

Case 2 - Parametrization:

DECLARE @SomeVariable int 
SET @SomeVariable = 'VALUE' 
  FROM someTable 
--do stuff with @SomeVariable 
GO 

CREATE PROCEDURE myProcedure 
( 
  @MyParameter 
  ,@SomeVariable int
) 
AS 
SET NOCOUNT ON 

--Do something 
--Do something using @SomeVariable 
SET NOCOUNT OFF 
RETURN 0 
GO 

Now whenever myProcedure is called, it must always be passed the parameter @SomeVariable. This is recommended when you are calling the same SP with different parametrization regularly

Case 3 - Configuration:

DECLARE @SomeVariable int 
SET @SomeVariable = 'VALUE' 
  FROM someTable 
--do stuff with @SomeVariable 
GO 

CREATE PROCEDURE myProcedure 
( 
  @MyParameter 
) 
AS 
SET NOCOUNT ON 

--Do something 
DECLARE @SomeVariable int 
SET @SomeVariable = 'VALUE' 
  FROM someTable 

SET NOCOUNT OFF 
RETURN 0 
GO 

Now, whenever you EXEC myProcedure, you need to ensure that the configuration has been set in the table. This scenario is recommended for slowly-changing configuration cases. In this case, you can wrap the @SomeVariable initialization in a scalar-valued UDF, so that any times this same configuration is used in different SPs, they will all call through the same UDF, which frees you to change your configuration table conventions (you don't give your users SELECT permission on your tables, anyway, right?) and if the UDF needs to start varying based on user or similar, you now have a control point which enforces consistency, permissions and interface calling conventions:

DECLARE @SomeVariable int 
SET @SomeVariable = dbo.udf_Global(username, session, etc.)
--do stuff with @SomeVariable 
GO 

CREATE PROCEDURE myProcedure 
( 
  @MyParameter 
) 
AS 
SET NOCOUNT ON 

--Do something 
DECLARE @SomeVariable int 
SET @SomeVariable = dbo.udf_Global(username, session, etc.)

SET NOCOUNT OFF 
RETURN 0 
GO