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 set SQL Server query result in variable(Global) and reuse it?
MSSQL server does not have a concept of global variables so you cannot
run first query and store the ID in the variable, then run second query.
If you store it in a variable like shown above it will only last till the query is completed.
If you want to be available to the current query window no matter what query you execute it there are multiple ways to do it.
1)Select it into a temp table(#tablename) and it is available in the current query window
2)select it into global temp table(##tablename) and it is available to the current session
3)one other way to make it available to the current session it to store it in a context info and then retreive it.
For your purpose #table seems the more appropriate option.
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
Global Variables in SQL statement
Try this one:
If MID
is number:
Dim strSQL As String
strSQL = "UPDATE Workstations SET [MID] = " & newvalue & " WHERE [MID] = " & tempvalue
DoCmd.RunSQL strSQL
If MID
is string (if newvalue
/tempvalue
doesn't contain single quote '
):
Dim strSQL As String
strSQL = "UPDATE Workstations SET [MID] = '" & newvalue & "' WHERE [MID] = '" & tempvalue & "'"
DoCmd.RunSQL strSQL
If MID
is string (if newvalue
/tempvalue
contains single quote '
like newvalue="Mike's car"
):
Dim strSQL As String
strSQL = "UPDATE Workstations SET [MID] = '" & Replace(newvalue, "'", "''") & "' WHERE [MID] = '" & Replace(tempvalue, "'", "''") & "'"
DoCmd.RunSQL strSQL
If MID
is date:
Dim strSQL As String
strSQL = "UPDATE Workstations SET [MID] = #" & newvalue & "# WHERE [MID] = #" & tempvalue & "#"
DoCmd.RunSQL strSQL
Thanks to @HansUp for pointing out in comments, that
MID is the name of a function, so it would be safer to bracket or alias that field name in the SQL statement: SET [MID]
Using a global var in an SQL query
One option would be to create a VBA function to return the value of the global variable. For example
Public Function GetActiveUser() As String
GetActiveUser = activeUser
End Function
Then you could use the function in a saved query like
SELECT WBS_Id,
Verantw,
Commentaar
FROM tbl_New_WPE_User
WHERE UserName LIKE GetActiveUser()
However, in this particular case I would be inclined to get rid of the global variable and just call the function to get the current user (i.e., the code you used to populate the global variable). I doubt that the extra overhead of retrieving the value each time (e.g., by getting the .Username
property from a WScript.Network
object) would amount to very much, and if it did turn out to be an issue then you could always consider using a Static
variable inside your function to cache the value.
Related Topics
SQL Server: How to Optimize "Like" Queries
Convert Rows to Columns in SQL
Trouble Making a Running Sum in Access Query
Oracle Shutdown Error Ora-01033
Syntax Error: Unexpected End of File
How to Change the Formatting for My Return Values in This Function
Combine Two SQL Queries in One Statement
Parse a Date from Unformatted Text in SQL
What's the Equivalent for Listagg (Oracle Database) in Postgresql
How to Use a Calculated Column by Another Calculated Column
Oracle Show All Employees with Greater Than Average Salary of Their Department
Natural Sort Supporting Big Numbers
Syntax Error with Update Query When Join with Some Table
Sqlite: How to Select "Most Recent Record for Each User" from Single Table with Composite Key