views:

39

answers:

3

Hi All,

I'm trying to understand a historical stored procedure I need to fix. I found this DRVTBL key word, and I couldn’t find out what it means??? (This is a sql2000 database)

SELECT ...
FROM (      
      ...)
) DRVTBL
A: 

Can you show the complete SQL statement ? As far as I can see right now, DRVTBL is not a keyword, but an alias that has been given to the subquery that is used in your FROM clause.

Frederik Gheysels
@Frederik Gheysels, thanks for the quick response. this DRVTBL doesn’t appear anywhere else in the SP.
avnic
When you remove the alias, it is possible that you'll receive a syntax error, and that SQL Server won't be able to execute the query.You can change the name of the alias though to a name that is more appropriate.
Frederik Gheysels
+1  A: 

DRVTBL, from the query you have posted, looks like an alias. The work like temporary tables in your T-SQL Query. SQL Server 2005 has a little bit advanced version of this functionality, called Common Table Expressions.

An example -

SELECT *
FROM
    (
        SELECT
            Id,
            Name
        FROM Employee
        WHERE Name LIKE 'A%'
    ) EmployeeA
WHERE EmployeeA.Name = 'Albert'

This will create an aliased table containing all the Employees whose name starts with A, and the outer query will, in turn, select the employees with the name Albert.

Same can be written using CTE as -

WITH EmployeeA AS
(
    SELECT
        Id,
        Name
    FROM Employee
    WHERE Name LIKE 'A%'

)
SELECT * FROM EmployeeA WHERE EmployeeA.Name = 'Albert'
Kirtan
+3  A: 

DRVTBL is an alias for the subquery that precedes it. When you use a subquery within a SELECT like this, you have to give it an alias. If you remove DRVTBL, you will get an error. It doesn't then have to go on and be used anywhere else.

AdaTheDev