Understanding the numerous types of variable SQL Server has to offer, and their appropriate usage is one of the cornerstones of developing effective database code. I recently helped a co-worker fix an error message in his code.
-- Error Msg 137, Level 15, State 2, Line 3 Must declare the scalar variable "@l_INT".
The code snippet looked something like this, which led to a discussion about scope of T-SQL variables and an interesting way to fix this problem.
CREATE TABLE #scope_test1 (col1 INT); CREATE TABLE #scope_test2 (col1 INT); GO DECLARE @l_INT INT = 42; INSERT INTO #scope_test1 (col1) SELECT @l_INT; GO INSERT INTO #scope_test2 (col1) SELECT @l_INT; GO
T-SQL language supports local variables (their names begin with a single @). The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared. A T-SQL batch is a group of statements that SQL Server parses as a single unit. A batch can be delimited by the BEGIN..END statements, or a client tool like SQL Server Management Studio (SSMS) can specify the end of a batch with a GO command (you can set any word to be a batch separator in SSMS, but we will leave that discussion for another time). The names of some Transact-SQL system functions begin with two at signs (@@). They are commonly referred to as Global variables. 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 not variables and do not have the same behaviors as variables. All the global variables represent information specific to the server or a current user sessions. Some of the commonly used ones are @@ERROR, @@IDENTITY, @@VERSION. Traditionally, DECLARE command is used to declare a local variable and SET or SELECT used to initialize its value. With SQL Server 2012, both of these tasks can be accomplished in a single statement ;
DECLARE @MyCounter INT = 12 ;
The variables we discussed so far are scalar variables, which can hold a single data value of a specific type. A table variable is a special data type that can be used to store a result set for processing at a later time. table is primarily used for temporary storage of a set of rows returned as the result set of a table-valued function. Functions and variables can be declared to be of type table. table variables can be used in functions, stored procedures, and batches. A DECLARE statement (similar to local scalar variables) is used to DECLARE a table variable. While they behave exactly like local variables, with a well defined scope,they can be thought of as being similar to temporary table, but with several limitations.
The SQLCMD utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt, in Query Editor in SQLCMD mode, in a Windows script file or in an operating system (Cmd.exe) job step of a SQL Server Agent job. This utility uses ODBC to execute Transact-SQL batches. Scripting variables can be used in SQLCMD scripts. Scripting variables enable one script to be used in multiple scenarios. The setvar command is used to define scripting variables. Variables that are defined by using the setvar command are stored internally. Scripting variables should not be confused with environment variables that are defined at the command prompt by using SET.
The scope of a SQLCMD scripting variable can span several batches, which can be used to implement variables that don’t go out of scope even when a batch ends. The example below is a simplified demo of the same ;
:setvar l_int "42" CREATE TABLE #scope_test1 (col1 INT); CREATE TABLE #scope_test2 (col1 INT); GO INSERT INTO #scope_test1 (col1) SELECT $(l_int); GO INSERT INTO #scope_test2 (col1) SELECT $(l_int); GO -- Results (1 row(s) affected) (1 row(s) affected)
A review of the types of variables the T-SQL language has to offer, has helped refresh my understanding of their appropriate usage.
- Table Variables – http://msdn.microsoft.com/en-us/library/ms175010.aspx
- T-SQL Variables – http://msdn.microsoft.com/en-us/library/ms187953(v=sql.105).aspx
- Global Variables – http://www.codeproject.com/Articles/39131/Global-Variables-in-SQL-Server
- Scripting variables with SQLCMD – http://msdn.microsoft.com/en-us/library/ms188714.aspx
- SQLCMD utility - http://msdn.microsoft.com/en-us/library/ms162773.aspx
SQL Saturday #154 | St.Louis on Sept 15th 2012, was the first SQL Saturday event in St.Louis, I am happy to say that we started off the SQL Saturday series in St.Louis ( yes, there will be one next year as well !) on a great note. We had over 130 attendees, 11 onsite sponsors and 25 technical sessions spread across 5 classrooms.
Center for Application of Information Technology (CAIT) at WashU, generously offered the venue (6 awesome classrooms plus the common areas and facilities) to host this event. The support of sponsors played a huge role in making this event successful. We got local as well as national sponsors, plus plenty of SWAG sponsors as well. Within a month of opening our call for speakers, we had over 55 sessions submitted for this event. They were a great mix of new and experienced speakers, both local and out of town, on topics ranging across Database Administration, Application Development, Business Intelligence and Professional Development. The real challenge was selecting only 25 sessions and the selection committee put a lot of thought into coming up with the session schedule for the event.
Our team took some time to review the event, which will help us make the next St.Louis SQL Saturday even better event. Feedback from attendees, sponsors, speakers and volunteers was crucial for the team to pin-point areas of improvement.
- The duration of the overall event should be constrained from 9 AM to 4 PM, by either cutting down the session duration from 75 to 60 minutes or possibly having 4 sessions per classroom. We are trying to secure more classrooms at the venue to make this feasible, so that the total number of session offerings during the event remains the same.
- We heard great feeback about the location and quality of the recommended event hotel, and we plan to continue the same for next year
- We will definitely invest in bigger and more number of signs on the sidewalks around the venue, to make it easier for attendees to find their way to the parking area, registration desk and locate the facilities in the venue.
- We are also looking at offering a better packaged box lunch and making some healthy meal options available for next year, not only for lunch, but also for breakfast, snacks and beverages.
- The SPEEDPASS worked out very well and we were able to move attendees efficiently through the registration process. We will probably do an online ONLY version of the event schedule for next year, to mitigate any confusion arising out of last minute schedule changes.
- We plan to leverage Twitter and Linkedin for communicating a majority of updates to event attendees , and use emails ONLY for crucial milestones during the days leading up to the event
Our team is always happy to hear feedback from the attendees and we have plenty of new ideas for the next year’s event. We do have some resource constraints, considering the fact that the event is free to attend, and all of the organizers are volunteers. The event sponsorship, availability of speakers, venue, and costs can change from one year to another and our team will strive to continue providing a valuable learning experience at the event. Organizing a SQL Saturday event, represents team work at its best. On that note, I would like to thank everyone involved; the core team, all the volunteers, PASS & community mentors, sponsors, speakers and friends & family for a successful first SQL Saturday in St. Louis.
SQL Saturday #154, the first SQL Saturday in St.Louis is almost here (Sept 15) and I wanted to share some details of this exciting upcoming event.
This event will be held Sep 15th, 2012 at the CAIT Campus of Washington University in St.Louis. The Street address of the venue is – 5 Jackson Avenue, Center for Application of Information technology (CAIT) at Washington University in St.Louis, Clayton, MO 63105. Please follow this link for directions to the venue. The venue is only a short walk from the recommended hotel (Crowne Plaza Clayton). There is free parking for the event attendees in the attached parking Garage (Lot #60) on saturday, you do not need any parking pass or permit. The Garage can be accessed via Lee avenue. From this Garage, take the elevators to Level 1 and follow the signs for SQL Saturday #154 Registration desk. The registration desk will be open for attendees from 7.30 AM and the first 100 attendees to come in with a SPEEDPASS will get a free event t-shirt ! There will be coffee and donuts for breakfast (first come first served!). The first session for the day starts at 8.30 AM.
CAIT has generously offered the use of their facilities to host the event and we would like to remind all of our attendees be respectful of their premises. We do want to make sure they will continue supporting us next year. We have 200 registered attendees (unfortunately, there are still 60+ on the waitlist!), 25 awesome sessions , and 16 sponsors (11 onsite) ! This is going to be a big event in a tight space and it would help for all the attendees to familiarize themselves with the classroom locations in the building.
We will be bringing in lunch boxes from Amighetti’s , a St.Louis favorite on the Hill (The Hill has all the awesome Italian restaurants in town). Your lunch tickets (for those who paid for lunch before midnight of Tuesday 9/11/2012) will be available on your SPEEDPASS. You can take your food and beverages (cans and plastic bottles ONLY) into the classrooms to eat. The Gold sponsors will be presenting 30 minute sessions during lunch in each classroom. Please do stop by their booths and to say hi (such events are not possible without their generous support) and drop your raffle tickets (also on your SPEEDPASS) to enter yourself in drawings for awesome prizes.
There will be plenty of volunteers to help out at the event (look for people wearing blue shirts and wearing lanyards that say “STAFF”). After the last session of the day, we will all assemble in and around room #024 for the closing ceremonies – namely a thank you note and raffle prize drawings ! While we are trying our best to make sure we have taken care of everything, this is our first time organizing an event of this size and we would really appreciate all the help and support we can get to ensure a great event.
The after party will be a small informal gathering of the event attendees at Schlafly Bottleworks, in Maplewood, MO 63143. The SQL Saturday #154 team is only suggesting this venue. Attendees are responsible for their own food and beverages at the after party. SQL Saturday #154 organizers will not be responsible for anything at the after party.
On Friday night (sept 14) around 9.30 PM, we are planning to having a pre-event party in the form of SQL Karaoke. Mike Talayna’s Juke Box Restaurant (1115 Hampton Avenue St. Louis, MO 63139 (314) 647-7600) starts their karaoke at 9.30 PM. All speakers and attendees are welcome to attend (look for kathi Kellenbeger at the event to find the SQL people). The SQL Saturday #154 team is only suggesting this venue. Attendees are responsible for their own food and beverages at this party. SQL Saturday #154 organizers will not be responsible for anything at the party.
We strive to ensure all of you will have a great time at SQL Saturday #154. Please do make the best of the awesome free opportunity to learn and network.