Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dve
Starting Member
19 Posts |
 Posted - 2005-08-26 : 16:33:00
|
| Hi,Does SQL server have a concept of global variables that can be user defined?? |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-08-26 : 19:14:23
|
| In DTS there are global variables, but in SQL, users can define local, not global, variables... e.g. DECLARE @MyValue INTIf you need user defined global variables, consider creating a table to hold the names and values you need. |
 |
|
|
Stalker
Yak Posting Veteran
80 Posts |
Posted - 2005-08-27 : 05:15:34
|
| or you can use UDF |
|
|
|
dve
Starting Member
19 Posts |
Posted - 2005-08-27 : 06:38:52
|
| UDF? |
|
|
|
Stalker
Yak Posting Veteran
80 Posts |
Posted - 2005-08-27 : 07:37:03
|
| yes, user defined functionsfor exampleCREATE FUNCTION [dbo].[const_pi] () RETURNS decimal(9,8) AS BEGIN return 3.14159265ENDnow you can call your constant as "select dbo.const_pi()" |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-08-27 : 09:38:47
|
SQL Server already has a built-in PI() function.  |
|
|
|
dve
Starting Member
19 Posts |
Posted - 2005-08-27 : 11:32:31
|
| Great, but can functions alter data?I recall having read somewhere that functions can't really alter data? And is it possible to have only 1 session at a time enter the function? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-08-27 : 11:44:12
|
| Functions cannot make permanent changes to data, they can only change things that are created and destroyed within their own definition.If you need to store global values, create a regular table for them that you can query, for example:CREATE TABLE constants(name varchar(20) not null, value float not null)INSERT constants VALUES('Pi', PI())SELECT value FROM constants WHERE name='Pi'That's just an example, but it should illustrate the point. If you need to hold or edit the value of a constant against multiple sessions, you need to isolate the transaction with a transaction isolation level like REPEATABLE READ or SERIALIZABLE.It might be better to explain what you're trying to do, it sounds like you're trying to apply procedural programming to SQL, which is almost always an ineffective direction. |
|
|
|
|
|
|
|
|
Global variables ?