alternative to declare @xx as table

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)

alternative to declare @xx as table

Author

Topic

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-02-04 : 00:44:59

I need to reserve memory and keep performance the best I am using declare @mytable as table (xxxx)

Is there a better way please see example below
in my stored procedure. Example I have a table
section (sectionid, sectionname, parentsection)
enrollment (studentid,sectionid,enrollmentstatus)
assessment (sectionid,assessmentid,assessmentname,maxpoint)
assessmentpoint(assessmentid,studentid,score)

all students enrolled in either section or child section should be included in the assessments of the parent the child section has no assessment and on adding a new assessment I need to pull all students enrolled either in section or children section add a record for each in the assessmentpoint with a point zero and if a new student is enrolled I add a record for him for all assessments in the section that he is enrolled

example of using temporary tables
declare @tempenrollment table
(sectionid int,studentid int,parentsection int)

insert into @tempenrollment
(sectionid,studentid,parentsection)
(select
S.sectionid,E.studentid,S.parentsection
from sp.section s
inner join
sp.sectionsemester ss
on
s.sectionid=ss.sectionid
inner join
enrollment.enrollment e
on
e.sectionid=s.sectionid
where semesterid =@semesterid
and enrollmentstatus=1)

update @tempenrollment
set sectionid=parentsection
where parentsection !=0

declare @TempAssessmentPoint table
(AssessmentID int,studentid int,Points decimal,updatedby int)
insert into
@TempAssessmentPoint
(AssessmentID,studentid,Points,updatedby)
(select AssessmentID,StudentID ,Null,1
from GradeBook.Assessment A
inner join
@tempenrollment E
on
A.SectionID=E.SectionID)

merge into GradeBook.AssessmentPoint T

USING @TempAssessmentPoint S
ON (T.AssessmentID=S.AssessmentID
and
T.StudentID=S.StudentID)

WHEN NOT MATCHED
THEN INSERT (AssessmentID,StudentID,Points,Updatedby)

VALUES (S.AssessmentID,S.StudentID,S.Points,S.Updatedby);

sarah

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-04 : 02:40:34

You'll probably be better off with a temp table (CREATE TABLE #...) as with table variables you have no statistics so SQL can't accurately cost the queries.

--
Gail Shaw
SQL Server MVP

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-02-04 : 10:07:11

You mean create physical temporary table then deleted but if several users used the same stored procedure at the same time.

Thanks

sarah

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-04 : 11:32:53

Yes, a standard temporary table - CREATE TABLE #Enrolement ....
I'm not suggesting a physical table in TempDB or a global temp table, so there are no issues with concurrent access.

--
Gail Shaw
SQL Server MVP

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-02-11 : 12:01:28

Thank you

sarah

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources