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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply