December 3, 2005 at 1:46 am
Please help with this newbie syntax problem.
I would like to populate my table variable with a complex sql statement (simplified here) so that I can use the table in another select statement later in the procedure. However when I run this procedure I get error "Must declare variable @StockTrns"
Syntax check is happy if I take out the join statement at the end and replace with "SELECT * FROM @StockTrns"
Also do I have to add any syntax after this final select statement to get the procedure to return my records, or will that happen automatically?
-----------
CREATE PROCEDURE dbo.Test (@LocationID int)
as
DECLARE @StockTrns Table (StockID int, Qty real)
INSERT @StockTrns
--Fill table variable
SELECT TOP 100 PERCENT dbo.STOCK_TRN.StockID, dbo.STOCK_TRN.Qty
FROM dbo.STOCK_TRN
WHERE (dbo.STOCK_TRN.LocationID = @LocationID)
--Join the above records
SELECT * FROM dbo.STOCK_ITEM LEFT OUTER JOIN
@StockTrns ON dbo.STOCK_ITEM.StockID = @StockTrns.StockID
GO
December 3, 2005 at 6:02 am
In the future. please provide DDL and sample data as per http://www.aspfaq.com/etiquette.asp?id=5006
Renaming the temporary table to a alias that does not begin with the @ symbol seems to solve the problem.
SELECT * FROM dbo.STOCK_ITEM
LEFT OUTER JOIN db@StockTrns AS StockTrnsTEMP
ON dbo.STOCK_ITEM.StockID
= StockTrnsTEMP.StockID
I have no idea why this is required.
I have been writing stored procedure and triggers since 1993, have rarely used a temporary tables and have never had to use a table variable.
If you post the source of the tables and the stored procedure, along with some test data, perhaps someone can help.
SQL = Scarcely Qualifies as a Language
December 3, 2005 at 6:30 am
Thanks for the help Carl.
Will check out the necessary etiquette for next time.
I can't believe that you so seldom have found the need to use a temp table or table variable. Being a newbie and coming from a background in MSAccess I just assumed that using a table variable would be a great way to break down a complex query.
In my case I want to join to fields from many different tables and only group the fields from one of those tables. Breaking down the query allows me to avoid having to GROUP BY on all the fields.
Is there some other way I should be achieving this.
December 3, 2005 at 8:33 am
Temporary tables are useful for development and debuging where you need to examine the intemediate results.
Say you have:
insert into #One (select ... from ONE ....) as One
insert into #Two (select ... from TWO ....) as Two
insert into #Three (select ... from THREE ....) as Three
and finaly
select *
from #One join #Two join #Three
This can be replaced by a single SQL statement and all of the overhead of the temporary table is gone.
select *
from (select ... from ONE ....) as One
join (select ... from TWO ....) as Two
join (select ... from THREE ....) as Three
This replacement is possible because the relational model follows the principle of closure. That is, the result of any set operator is another set. That is why there are no operators that accept as input or produce as output such data organizations as arrays, ordered lists, or linked lists.
The situation where temporary tables are useful is when the results of the same query need to be used more than once within a single SQL Statement.
Using the Northwind database as the schema and a requirement to get the top 2 most expensive Products by Product Category. This is RANK window function, is part of the SQL standard, is supported in SQL Server 2005 but not by 2000.
The SQL would be:
SELECT Products.CategoryId , Products.ProductID, count(*)
FROM dbo.Products
join dbo.Products as ProductHigherPrice
on ProductHigherPrice.CategoryId = Products.CategoryId
and ProductHigherPrice.UnitPrice > Products.UnitPrice
group by Products.CategoryId , Products.ProductID
having count(*) RedProducts.UnitPrice
group by RedProducts.CategoryId , RedProducts.ProductID
having count(*) RedProducts.UnitPrice
group by RedProducts.CategoryId , RedProducts.ProductID
having count(*) <= 2
I have also tested the case where an index is created on the temporary table.
The IO Counts for the 3 alternatives are:
Single SQL Statement wo index ----- 78 Scans and 1702 Logical Reads
Single SQL Statement with index --- 26 Scans and 78 Logical Reads
Temporary Table has a total of ---- 26 scans and 56 Logical Reads
Temporary Table with an index has - 27 Scans and 33 Logical Reads
The temporary table solution uses about 3% of the resources of the single SQL statement without the index and 4% with an index. The absolute numbers do not appear to be significant, but take the Chicago located company Newark Electronics which has 250,000 Products.
Here are the Statistics IO for each condition:
Single SQL Statement without an index:
....Table 'Products'. Scan count 78, logical reads 1702, physical reads 0, read-ahead reads 0
Single SQL Statement with an index:
Table 'Products'. Scan count 78, logical reads 78, physical reads 0, read-ahead reads 0.
Temporary Table without an index:
First populate the temporary table:
...Table '#RedProducts'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
...Table 'Products'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
The query the temporary table.
...Single SQL StatementTable '#RedProducts'. Scan count 26, logical reads 52, physical reads 0, read-ahead reads 0.
Temporary Table with an index:
First populate the temporary table:
....Table '#RedProducts'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
....Table 'Products'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
Then index the temporary table:
....Table '#RedProducts'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
The query the temporary table.
....Table '#RedProducts'. Scan count 26, logical reads 27, physical reads 0, read-ahead reads 0.
SQL = Scarcely Qualifies as a Language
December 3, 2005 at 10:28 pm
Thanks Carl for your substantial reply.
I didn't consider using the group by subquery in the join. It is more difficult to for me to read and understand but I suppose you get used to it. I will take some time to reread and digest your examples.
Thanks again for your advice it's much appreciated.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply