How are global variables assigned in SQL? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How are global variables assigned in SQL?

Author

Topic

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-01-31 : 09:37:27

I've done this in embedded SQL, but can someone help me with how it's done using just SQL 8.0?

I'd like to assing a global variable DATE01 before the query resolves in the compiler:

DATE01 = '2001-01-01'

Then use that variable in one or more subsequent queries (note that I'm just using & in this case and don't know the right way to do it).

SELECT
PRICE

FROM
PRICE_TABLE

WHERE
DATE >= &DATE /* HERE IS WHERE MY DATE WILL RESOLVE */
AND DATE <= CURRENT DATE

Norwich
Posting Yak Master

158 Posts

Posted - 2006-01-31 : 09:46:33

[code]Declare @Datex as Datetime

Set @Datex = '2001-01-01'

SELECT
PRICE

FROM
PRICE_TABLE

WHERE
DATE >= @Datex
AND DATE <= CURRENT DATE
[/code]

Regards
N

The revolution won't be televised!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-31 : 17:51:34

>> SQL 8.0?
MS SQL Server 2000 ?

Declare @Datex as Datetime

Set @Datex = '2001-01-01'

SELECT PRICE
FROM   PRICE_TABLE
WHERE  DATE >= @Datex
AND    DATE <= GETDATE()

----------------------------------
'KH'

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-01 : 14:21:06

Excellent thanks!

Norwich
Posting Yak Master

158 Posts

Posted - 2006-02-02 : 12:30:10

I can't spot the difference between my code and Khtan's???

N

The revolution won't be televised!

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-06 : 10:21:12

I think it's that Khtan is using the GETDATE() function.
I'm pretty sure the Microsoft product doesn't support the IBM standard of CURRENT DATE.
You'll get a syntax error.

Norwich
Posting Yak Master

158 Posts

Posted - 2006-02-23 : 05:20:08

quote:
Originally posted by pug2694328

I think it's that Khtan is using the GETDATE() function.
I'm pretty sure the Microsoft product doesn't support the IBM standard of CURRENT DATE.
You'll get a syntax error.

aah, I used CURRENT DATE because you had it in your code and didn't realise that it was part of your question. Your are quite right CURRENT DATE is not supported

N

The revolution won't be televised!

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-23 : 08:53:13

Sorry, I'm an old DB2 SQLer.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-02-23 : 19:19:20

SQL Server does support the ANSI standard CURRENT_TIMESTAMP function though.

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-24 : 13:42:50

Hey that's usefull to know thanks robo!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources