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 is dying with the party to which it belongs, and myProcedure requires its own party. Obviously, I could create a #temp table and observe any values ​​that I need, but then I will need to choose from it - adding code that, although trivial, has more readability and seems silly when all I need , is a global variable. Is there a better way?

Be painfully clear. I KNOW SQL Server has "global variables" called "tables." I mentioned in the previous paragraph that using # tables is a possible solution, as the actual constant table is used. What I'm looking for here is probably more of a global constant that I can use anywhere within a given script, rather than a global variable, so we can all stop blurring our pants about the evil of global variables.

Answer 1

It is not clear why the stored process has a dependency on your global set of two parties in your example. I see two main possibilities: either SP has a global dependency at the time of creation (for example, code generation - case 1), or SP has a global runtime dependence (i.e. you have to choose between parameterization - case 2 - or self-configuration - Case3).

If it depends on the execution time, regardless of whether it is received from any place outside the SP and passed as a parameter or inside the SP, this is the main design decision. The choice of when to transfer data as a parameter and when to pull from tables is not quite a science, it all depends on all cases of use in real life 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 - Parameterization:

 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 when myProcedure is myProcedure , it should always be passed the @SomeVariable parameter. This is recommended if you regularly call the same SP with a different parameterization.

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 execute EXEC myProcedure, you need to make sure that the table has been configured. This scenario is recommended for slowly changing configuration cases. In this case, you can wrap the initialization of @SomeVariable in a scalar-valued UDF, so that at any time when the same configuration is used in different SPs, they will all call through the same UDF, which frees you from changes to the configuration table settings (you don’t give your users SELECT permission for your tables, anyway?), and if the UDF should start to change depending on the user or the like, now you have a breakpoint that provides 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 

Answer 2

The GO statement, which is not part of the SQL language specification, is a package delimiter. Your local variables are package bound. Therefore, they go beyond the scope of the GO statement. I think your only alternative is what you described.

Answer 3

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

If you just want the variable that you use in the same procedure several times, you include it in the definition of the procedure.

 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 need a good encapsulation of logic that you can reference in a convenient way, then a scalar user-defined function (UDF) may be what you are after.

Answer 4

I think we need a little more context, since I really don't see the point here, but I can give you a little understanding based on what I know.

Here you have two different parties, one of which is a regular block of code, and the other, which actually does nothing but create a stored procedure.

If you want the stored procedure to be meaningful, just enter it.

If you need to share the above value between stored procedures, you can potentially use a scalar function to return the value you need on request.

Answer 5

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

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

Answer 6

How to create a table called dbo.Configuration in which you can store a bunch of values, pulling them out when you need to? It will be a maximum of one or two pages, so you’ll probably quickly get access to RAM all the time, and you can dive into it from anywhere.