How to Declare Global Variable in SQL Server..

how to declare global variable in SQL Server..?

There is no way to declare a global variable in Transact-SQL. However, if all you want your variables for is to be accessible across batches of a single script, you can use the SQLCMD tool or the SQLCMD mode of SSMS and define that tool/mode-specific variables like this:

:setvar myvar 10

and then use them like this:

$(myvar)

To use SSMS's SQLCMD mode:

how to declare global variable in sql

Assuming MS SQL Server, and assuming the MS definition of 'Global Variable' you cannot. If you need a variable to be accessable across multiple stored procedures, or ad-hoc queries, you will need some other way to hold the data: say a table which holds the variable for you.

Note: the hyperlink does not go to Microsoft, I could not find a copy of Microsoft's specific definition, but the website linked came close enough for this discussion.

how to create a global variable in sql server 2008

I don't know if the SQL Server has global variables, but you can use a user defined function as follows:

CREATE FUNCTION dbo.fn_GetDomainName()
RETURNS STRING
AS
BEGIN
    RETURN 'domain_name\\'
END

and do a SELECT dbo.fn_GetDomainName() + Login FROM table WHERE ... at the corresponding locations in your views.

Global variables in SQL

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 

Create a global static variable in SQL Server?

I guess this will work the best for me:

CREATE FUNCTION [dbo].[fn_GetDefaultPercent]()
RETURNS decimal(5,4)
AS
BEGIN
    RETURN 1.0000
END

---

Related Topics