Using a Table Variable in ASP 1

[jon24422531]

Hi guys

I am not sure why this doen't work, and can I use a @Table in an ASP Page?

This works:

strSQL3 = " SELECT UPPER(D.UserID)AS UserID, ISNULL(A.cnt, '0') AS [OPEN], ISNULL(B.cnt, '0') AS [ON-HOLD], ISNULL(c.cnt, '0') AS [DEVELOPMENT], D.cnt AS [Total] FROM" & _
" (SELECT DISTINCT(UserID) AS UserID, Count(*) as cnt FROM WorkLog..WorkLog WHERE Finished != 'CLOSED' Group By UserID) D" & _
" LEFT OUTER JOIN" & _
" (SELECT DISTINCT(UserID) AS UserID, Count(*) as cnt FROM WorkLog..WorkLog WHERE Finished = 'open' Group By UserID) A on D.UserID = A.UserID" & _
" LEFT OUTER JOIN" & _
" (SELECT DISTINCT(UserID) AS UserID, Count(*) as cnt FROM WorkLog..WorkLog WHERE Finished = 'On-Hold' Group By UserID) B on D.UserID = B.UserID" & _
" LEFT OUTER JOIN" & _
" (SELECT DISTINCT(UserID) AS UserID, Count(*) as cnt FROM WorkLog..WorkLog WHERE Finished = 'Development' Group By UserID) C on D.UserID = C.UserID"

But this doesn't:

strSQL3 = [COLOR=blue]"DECLARE @T Table(UserID VARCHAR(25), [Open] int, [On-Hold] int, [Development] int, Total int)" & _
" Insert into @T" & _ [/color]
" SELECT UPPER(D.UserID)AS UserID, ISNULL(A.cnt, '0') AS [OPEN], ISNULL(B.cnt, '0') AS [ON-HOLD], ISNULL(c.cnt, '0') AS [DEVELOPMENT], D.cnt AS [Total] FROM" & _
" (SELECT DISTINCT(UserID) AS UserID, Count(*) as cnt FROM WorkLog..WorkLog WHERE Finished != 'CLOSED' Group By UserID) D" & _
" LEFT OUTER JOIN" & _
" (SELECT DISTINCT(UserID) AS UserID, Count(*) as cnt FROM WorkLog..WorkLog WHERE Finished = 'open' Group By UserID) A on D.UserID = A.UserID" & _
" LEFT OUTER JOIN" & _
" (SELECT DISTINCT(UserID) AS UserID, Count(*) as cnt FROM WorkLog..WorkLog WHERE Finished = 'On-Hold' Group By UserID) B on D.UserID = B.UserID" & _
" LEFT OUTER JOIN" & _
" (SELECT DISTINCT(UserID) AS UserID, Count(*) as cnt FROM WorkLog..WorkLog WHERE Finished = 'Development' Group By UserID) C on D.UserID = C.UserID" & _
[COLOR=blue]" Select * from @T ORDER BY [Open] DESC"[/color]

Many thanks

Jonathan

http://www.telecom-stuff.com

 

[gmmastros]

Are you getting an error message? If so, what is the error?

Also, what database are you using (SQL Server?) and what version?

I suspect the error message is something about the recordset being closed. This occurs when you have a compound sql statement, even if only one statement returns data to the front end (ASP). I suspect that adding SET NOCOUNT ON will resolve your problem.

strSQL3 = "[!]SET NOCOUNT ON [/!]DECLARE @T Table(UserID VARCHAR(25), [Open] int, [On-Hold] int, [Development] int, Total int)" & _
" Insert into @T" & _
" SELECT UPPER(D.UserID)AS UserID, ISNULL(A.cnt, '0') AS [OPEN], ISNULL(B.cnt, '0') AS [ON-HOLD], ISNULL(c.cnt, '0') AS [DEVELOPMENT], D.cnt AS [Total] FROM" & _
" (SELECT DISTINCT(UserID) AS UserID, Count(*) as cnt FROM WorkLog..WorkLog WHERE Finished != 'CLOSED' Group By UserID) D" & _
" LEFT OUTER JOIN" & _
" (SELECT DISTINCT(UserID) AS UserID, Count(*) as cnt FROM WorkLog..WorkLog WHERE Finished = 'open' Group By UserID) A on D.UserID = A.UserID" & _
" LEFT OUTER JOIN" & _
" (SELECT DISTINCT(UserID) AS UserID, Count(*) as cnt FROM WorkLog..WorkLog WHERE Finished = 'On-Hold' Group By UserID) B on D.UserID = B.UserID" & _
" LEFT OUTER JOIN" & _
" (SELECT DISTINCT(UserID) AS UserID, Count(*) as cnt FROM WorkLog..WorkLog WHERE Finished = 'Development' Group By UserID) C on D.UserID = C.UserID" & _
" Select * from @T ORDER BY [Open] DESC"

This will prevent extra result sets from returning to your app (and allow the recordset object to work the way you expect it to).

While I was looking in to this issue, I took a close look at the query you are running. There is a more efficient method to return the same information. If your table is small, you probably won't notice a difference in execution time, but with larger tables, the difference will be more noticeable.

Select	UPPER(UserId) As UserId,
        Count(Case When Finished <> 'CLOSED' Then 1 End) As Total,
        Count(Case When Finished = 'open' Then 1 End) As [Open],
        Count(Case When Finished = 'On-Hold' Then 1 End) As [On-Hold],
        Count(Case When Finished = 'Development' Then 1 End) As [Development]
From    WorkLog..WorkLog
Group By UserId
Order By Count(Case When Finished = 'open' Then 1 End)

The trick here is using the Count function in combination with Case. With this version, SQL Server only needs to make one pass through the table to calculate the various counts instead of 4 separate hits on the table. Another advantage here is that you don't need to use a table variable either. Ordinarily table variables are fast, but if you can avoid it, you probably should.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jon24422531]

George

Set NoCount ON......Of course.... Thanks

As for your quick improvement on my cumbersome query, genius!

Many thanks.

I have followed your repies to this forum and they are all very informative, have a star!

Jonathan

http://www.telecom-stuff.com