question about variables in SQL:

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 2008 Forums

Transact-SQL (2008)

question about variables in SQL:

Author

Topic

joe8079
Posting Yak Master

127 Posts

Posted - 2011-09-03 : 21:33:40

Ok, so I work as an analyst and I pretty much pull data and run reports all day. I use pretty much the basic stuff such as temp tables, CTE, Case statements, rollup, cube, but I have never seen a reason to ever use a variable in SQL while pulling data. Am I missing something? Does anyone use variables in T-SQL while pulling data? I've seen them in books and watched them in tutorials but I can never find any use for them while pulling data in T-sql. The stuff I do isnt advanced in anyway, but I was just wondering if anyone had any examples of when using a variable in T-sql is useful.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-03 : 22:08:12

Yes variables are very common to use. What would you use as a constant for example?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Kristen
Test

22859 Posts

Posted - 2011-09-04 : 02:35:15

[code]
DECLARE @StartDate datetime, @EndDate datetime

SELECT @StartDate = '20110101',
@EndDate = '20110131'

SELECT Col1, Col2
FROM MyTable
WHERE MyDate >= @StartDate
AND MyDate <= @EndDate
[/code]
Now if you are going to reuse that code for a different date range you change the date at the top, rather than finding it wherever it is within the query and changing it there (which is error prone - you, or a colleague, might miss changing one).

If you do this, instead of embedding the Range dates in the query, SQL Server will cache the query plan and reuse it next time.

If you reuse this query a lot you can do:
[code]
CREATE PROCEDURE MyProcedure
@StartDate datetime,
@EndDate datetime
AS

SELECT Col1, Col2
FROM MyTable
WHERE MyDate >= @StartDate
AND MyDate <= @EndDate
[/code]
and then you can just do:
[code]
EXECUTE MyProcedure
@StartDate = '20110101',
@EndDate = '20110131'
[/code]
you can grant permissions to Execute this Stored Procedure to specific people / groups, and you could use it in an application. The logic is encapsulated within the stored procedure, so if you need to change it in the future you don't change the "way" it is used, you still do the same EXECUTE MyProcedure @StartDate = '20110101', @EndDate = '20110131', but you could change the logic within the stored procedure - for example, to add to the Where clause "AND IsArchived = 0" to only include records which have not been marked as Archived.

Again, because the query does not change, SQL Server will cache the query plan for this query, which will make it more efficient.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources