March 29, 2004 at 1:05 pm
Hi, I am converting some old asp dynamic sql to a stored procedure. In my dynamic sql I first prepare a select statment that will function as a derived table to act as a constraint on the other tables.
Statement for derived table:
select uid from students where.....
Then about 6 queries like this:
SELECT TOP 100 PERCENT MAX(coursescore) AS MaxScore, MIN(coursescore) AS MinScore, AVG(coursescore) AS avgSCore FROM dbo.results INNER JOIN (" & strStudentDerived & ") studentDerived ON dbo.results.student_fk = studentDerived.student_fk
When I am converting this to a stored procedure I expect that I would want to use a table variable.
When I use:
declare @StudentDerived table (student_fk int)
select * from @StudentDerived
insert into @StudentDerived select uid as student_fk from student where (status_fk<3) and (isTestAccount=0) and client_fk=@clientID and (delegate_fk=@delegateID)
It seems to work until I try to use it in a join:
SELECT COUNT(DISTINCT dbo.course_enrolments.student_fk) AS Expr1
FROM dbo.course_enrolments INNER JOIN
dbo.Course ON dbo.course_enrolments.course_fk = dbo.Course.uid AND dbo.course_enrolments.courseversion = dbo.Course.version INNER JOIN
@StudentDerived ON dbo.course_enrolments.student_fk = @StudentDerived.student_fk
WHERE (dbo.course_enrolments.course_fk = @courseID)
At which point it tells me I havn't declared @StudentDerived
When I use a temp table it works fine.
So I guess I have two questions; Can I use a table variable in this way?
And if not then when BOL says: "Local temporary tables are visible only in the current session;" What do they mean by session? I would be calling this proc from ASP so all calls would be using the same connection string and possible via connection pooling the same connection.
Thanks,
dave
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
March 29, 2004 at 1:59 pm
Are you creating your
"SELECT COUNT(DISTINCT dbo.course_enrolments.student_fk) AS Expr1
FROM dbo.course_enrolments INNER JOIN
dbo.Course ON dbo.course_enrolments.course_fk = dbo.Course.uid AND dbo.course_enrolments.courseversion = dbo.Course.version INNER JOIN
@StudentDerived ON dbo.course_enrolments.student_fk = @StudentDerived.student_fk
WHERE (dbo.course_enrolments.course_fk = @courseID)"
as dynamic SQL and then using sp_executesql or EXECUTE then you won't be able to use table variables. What BOL is saying is that if you create a table variable you can only use it in the area you have created it. even though sp_executesql and execute are in the same SP they are like calling out to another procedure. So just like other variables the stored procedures you call can't reference the variables declared in the calling SP.
I hope that helps.
Not sure why you need dynamic SQL from your code above.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
March 29, 2004 at 2:53 pm
Could you post the entire sp that is giving you the error? It is hard to tell what the problem could be from the snippets you have provided.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 29, 2004 at 3:19 pm
Sorry it wasn't clear. No I am not using dynamic sql.
I'll abstract it.
this works
============================
create table #temptable
( id int)
insert into #tempTable select uid from tableA where some condition
select thisField from dbo.tableB inner join #temptable on dbo.tableB.fk = #tempTable.id
======================
this doesn't
======================
declare @tblVar table
( id int)
insert into @tblVar select uid from tableA where some condition
select thisField from dbo.tableB inner join @tblVar on dbo.tableB.fk = @tblVar.id
=========================
I am just trying to figure out my misundertanding of the difference between a local scoped temp table and a "table variable"
In the examples using a local temp table works just fine, but with a table variable I keep getting the message that I must declare @tblVar even tho I am sure that I have
dave
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
March 30, 2004 at 12:09 am
Just give the table variable an alias e.g.
declare @tblVar table ( id int)
insert into @tblVar select uid from tableA where some condition
select thisField from dbo.tableB inner join @tblVar x on dbo.tableB.fk = x.id
March 30, 2004 at 12:21 am
Ok I'll try that in the morning. BTW is there any function difference between a temp table and a table variable? scope or otherwise.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
March 30, 2004 at 1:14 am
Table variables are created only in memory so there is no costly disk writing involved unlike temp tables which are pretty much real tables that just get destroyed once you've had your wicked way with them.
The only other differences that I know of are that you have to give a table variable an alias in a join and you cannot perform a select into statement on a table variable
March 30, 2004 at 2:50 am
Hmm can't do a select into..that sounds familiar, and that probably explains why my code was throwing an error.
So is the scope of a temp table local to the procedure? What happens if the procedure is called more then once in the same "moment" each running version of the proc have it's own temp table?
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
March 30, 2004 at 2:57 am
As the table variable is a local variable it should behave in the same manner as any other variable type i.e. it is visible only within the scope of the sproc that created it. I see no reason why you wouldn't be able to simultaeneously create two through two calls to the same sproc as they would occupy different memory addresses.
March 30, 2004 at 3:00 am
Sorry I misread your post. A normal temp table is actually a table that gets deleted at the end of the sproc so if you call the sproc again before the previous one has ended and dropped the temp table I imagine you would get a create error of some sort. Use table variables they're just better all round.
March 30, 2004 at 10:28 am
Ya well I'd love to, but since I can't do a "insert into" it won't work for my current solution.
In my dynamic sql version of this solution I use a derived table.
e.g.
select from here inner join (select...) derivedTable on here.fk = derivedTable.key
This is for a reports page involving 6 or more seperated statements, and this method makes it very easy to offer the user all manner of filtering options that are all applied to the derivedTable statement which then acts as a constraint on all the other statments.
So using a table variable I would:
declare @tblVar table (id int)
insert into @tblVar (select fldList where wherelist)
But since I can't do an insert into, Im kinda stumped on this whole approach.
Perhaps if I offloaded the creation of this table to another proc with a table var as an output type maybe I can get around this limitation.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
March 30, 2004 at 11:19 am
Temp tables (i.e # tables, not table variables) belong to a connection, and don't get deleted at the end of a stored proc. They remain until explicitly deleted, or the session is disconnected. So you could create a temp table in Sproc1, that gets used by Sproc2, Sproc3 etc, as long as they are executed on the same connection.
You can use temp (#) tables or table variables to do an Insert ... Select ..., but you can't do a SELECT ... INTO ... using a table variable.
March 30, 2004 at 11:51 am
Thanks everyong, it looks like it was useing the alias that really made the difference. I have my proc working correctly using a table variable now.
As usual so many questions come to mind. Especialy the definition of a "connection". Using an ado connection object, if I open the connection, run it and then close it/destroy it. I could imagine that this is one connection. But how is this effected by ADO connection pooling? How many other processes used the "same" connection?
I'll dive into the ADO docs concerning that.
Thanks again all, hopefully others will find this thread usefull in diverning the differences between temp tables and table variables.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply