January 16, 2020 at 4:26 am
On some website, I came across this:
"Global variable names begin with a @@ prefix. You do not need to declare them, since the server constantly maintains them. They are system-defined functions and you cannot declare them."
Global variables are being discussed, but then it says they are system-defined functions. How can a variable also be a function? I thought functions have a pair of ( ) after the function name to accept parameter values.
------------------------------------------------------------------------------------------------------------------------------------------------------------
Looks like system variables can have global scope.
Can system variables have local scope?
User defined variables can have local scope. Can user defined variables have global scope?
------------------------------------------------------------------------------------------------------------------------------------------------------------
Then in a different situation but on a similar note, I have this code from a PDF file I bought about SQL Interview questions.
-- drop all user defined stored procedures Declare @procName varchar(500) Declare cur Cursor For Select [name] From sys.objects where type = 'p' Open cur Fetch Next From cur Into @procName While @@fetch_status = 0 Begin Exec('drop procedure ' + @procName) Fetch Next From cur Into @procName End Close cur Deallocate cur
Not sure why the returns didn't appear when I pasted the code above. Anyway, my question is about this part:
Declare cur Cursor For Select [name] From sys.objects where type = 'p'
The person is declaring cur of type Cursor. What exactly is cur? It looks like it's a variable but it can't be because it doesn't have the @ sign before it, i.e. @cur, so I'm not sure what cur is in this case.
January 16, 2020 at 4:36 am
Be brave. Read the documentation. There are examples of how to properly use all this stuff there.
January 16, 2020 at 11:11 am
-- drop all user defined stored procedures
Declare @procName varchar(500)
Declare cur Cursor
For Select [name] From sys.objects where type = 'p'
Open cur
Fetch Next
From cur Into @procName
While @@fetch_status = 0 Begin
Exec('drop procedure ' + @procName)
Fetch Next From cur Into @procName
End
Close cur
Deallocate curAnyway, my question is about this part:
Declare cur Cursor For Select [name] From sys.objects where type = 'p'The person is declaring cur of type Cursor. What exactly is cur? It looks like it's a variable but it can't be because it doesn't have the @ sign before it, i.e. @cur, so I'm not sure what cur is in this case.
cur is a cursor. You could, if you wanted to, declare it with an @:
-- drop all user defined stored procedures
Declare @procName varchar(500)
Declare @cur Cursor
SET @cur = cursor
For Select [name]
From sys.objects
where type = 'p'
Open @cur
Fetch Next
From @cur Into @procName
While @@fetch_status = 0 Begin
Exec('drop procedure ' + @procName)
Fetch Next From @cur Into @procName
End
Close @cur
Deallocate @cur
PS: Don't run this code on your system, unless you want to drop all the stored procedures on your database!
January 16, 2020 at 2:24 pm
Ah, the joys of old knowledge repeated forever, despite things changing.
Read the docs. System functions can start with @@ and they used to be called global variables. They're not now. They're functions and, as the document says, follows the rules of functions.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply