Regarding store procedure

Question

Post reply

Regarding store procedure

pallavi.unde

SSCommitted

Points: 1987
More actions
February 11, 2012 at 4:52 am

#143209

Hello,
I have written a stroreprocedure in that i have to execute the 4 conditions, for that i used if loop in it..
can you please guid me how could i write it in other way...
can i use switch case in it??
It is as below;
ALTER PROCEDURE [dbo].[proc_GetCollegeData1]
(
@CourseId numeric(18,0),
@DegreeId numeric(18,0),
@StreamId numeric(18,0) = null,
@CityId numeric(18,0) = null,
@mode char=null
)
AS--select * from CollegeMaster
-- to get college data when stream id is given and city id is specified
if(@mode='A')
begin
SELECT ROW_NUMBER() OVER(ORDER BY main.CollegeName) as SrNo
,main.[CollegeId],[CollegeLogo],[CollegeName], main.PaidClientPage
,[CourseTypeId],[DegreeId],[StreamgroupId]
,[CollegeDescription],[TypeofCollege],[Grade]
,main.[TrustId],[TrustName],main.[UniversityId],[UniversityName]
,main.[ApprovedId],[ApprovedName],main.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType]
,[AddressStreet],[AddressPin]
,main.[CityId],[CityName],main.[StateId],[StateName]
,main.[CountryId],[CountryName] INTO #tempCollegeData1
FROM CollegeMaster main , College_DegreeStreamTransition degTrans ,College_UniversityMaster univ,
College_AccredationMaster acc ,College_ApprovedMaster app , College_TrustMaster trust,
College_CityMaster city ,College_CountryMaster coun ,College_StateMaster stat
WHERE main.CollegeId = degTrans.CollegeId
and main.TrustId=trust.TrustId and main.ApprovedId= app.ApprovedId
and main.AccredationId= acc.AccredationId and univ.UniversityId = main.UniversityId
and main.CountryId = coun.CountryId and main.CityId = city.CityId
and main.StateId= stat.StateId and degTrans.CourseTypeId = @CourseId
and DegreeId = @DegreeId and StreamgroupId = @StreamId and main.CityId = @CityId
and main.CollegeAccType = 'paid'
Group by main.[CollegeId],[CollegeLogo],[CollegeName], main.PaidClientPage
,[CourseTypeId],[DegreeId],[StreamgroupId]
,[CollegeDescription],[TypeofCollege],[Grade]
,main.[TrustId],[TrustName],main.[UniversityId],[UniversityName]
,main.[ApprovedId],[ApprovedName],main.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType]
,[AddressStreet],[AddressPin]
,main.[CityId],[CityName],main.[StateId],[StateName]
,main.[CountryId],[CountryName]
ORDER BY main.CollegeName
else
if(@mode='B')
begin
-- to get college data when stream id is given and city id is all (not specified)
SELECT ROW_NUMBER() OVER(ORDER BY main.CollegeName) as SrNo
,main.[CollegeId],[CollegeLogo],[CollegeName], main.PaidClientPage
,[CourseTypeId],[DegreeId],[StreamgroupId]
,[CollegeDescription],[TypeofCollege],[Grade]
,main.[TrustId],[TrustName],main.[UniversityId],[UniversityName]
,main.[ApprovedId],[ApprovedName],main.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType]
,[AddressStreet],[AddressPin]
,main.[CityId],[CityName],main.[StateId],[StateName]
,main.[CountryId],[CountryName] INTO #tempCollegeData3
FROM CollegeMaster main , College_DegreeStreamTransition degTrans ,College_UniversityMaster univ,
College_AccredationMaster acc ,College_ApprovedMaster app , College_TrustMaster trust,
College_CityMaster city ,College_CountryMaster coun ,College_StateMaster stat
WHERE main.CollegeId = degTrans.CollegeId
and main.TrustId=trust.TrustId and main.ApprovedId= app.ApprovedId
and main.AccredationId= acc.AccredationId and univ.UniversityId = main.UniversityId
and main.CountryId = coun.CountryId and main.CityId = city.CityId
and main.StateId= stat.StateId and degTrans.CourseTypeId = @CourseId
and DegreeId = @DegreeId and StreamgroupId = @StreamId
and main.CollegeAccType = 'paid'
--Added by Pallavi on 27 june 2011
Group by main.[CollegeId],[CollegeLogo],[CollegeName], main.PaidClientPage
,[CourseTypeId],[DegreeId],[StreamgroupId]
,[CollegeDescription],[TypeofCollege],[Grade]
,main.[TrustId],[TrustName],main.[UniversityId],[UniversityName]
,main.[ApprovedId],[ApprovedName],main.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType]
,[AddressStreet],[AddressPin]
,main.[CityId],[CityName],main.[StateId],[StateName]
,main.[CountryId],[CountryName]
ORDER BY main.CollegeName
else
if(@mode='C')
begin
-- to get college data when stream id is all and city id is specified
SELECT distinct main.[CollegeId],[CollegeLogo],[CollegeName]
,course.[CourseTypeId],degree.[DegreeId], main.PaidClientPage
,[CollegeDescription],[TypeofCollege],[Grade]
,main.[TrustId],[TrustName],main.[UniversityId],[UniversityName]
,main.[ApprovedId],[ApprovedName],main.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType]
,[AddressStreet],[AddressPin]
,main.[CityId],[CityName],main.[StateId],[StateName]
,main.[CountryId],[CountryName] INTO #tempCollegeData5
FROM CollegeMaster main , College_DegreeStreamTransition degTrans ,College_UniversityMaster univ,
College_AccredationMaster acc ,College_ApprovedMaster app , College_TrustMaster trust,
College_CityMaster city ,College_CountryMaster coun ,College_StateMaster stat,college_coursetypemaster course, college_degreemaster degree
WHERE main.CollegeId = degTrans.CollegeId
and main.TrustId=trust.TrustId and main.ApprovedId= app.ApprovedId
and main.AccredationId= acc.AccredationId and univ.UniversityId = main.UniversityId
and main.CountryId = coun.CountryId and main.CityId = city.CityId
and course.courseTypeId=degTrans.courseTypeId and degree.degreeid=degTrans.degreeid
and main.StateId= stat.StateId and course.CourseTypeId = @CourseId
and degree.DegreeId = @DegreeId and main.CityId = @CityId
and main.CollegeAccType = 'paid'
ORDER BY main.CollegeName
else
if(@mode='D')
begin
-- to get college data when stream id is all and city id is all
SELECT distinct main.[CollegeId],[CollegeLogo],[CollegeName]
,course.[CourseTypeId],degree.[DegreeId], main.PaidClientPage
,[CollegeDescription],[TypeofCollege],[Grade]
,main.[TrustId],[TrustName],main.[UniversityId],[UniversityName]
,main.[ApprovedId],[ApprovedName],main.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType]
,[AddressStreet],[AddressPin]
,main.[CityId],[CityName],main.[StateId],[StateName]
,main.[CountryId],[CountryName] into #tempCollegeData7
FROM CollegeMaster main , College_DegreeStreamTransition degTrans ,College_UniversityMaster univ,
College_AccredationMaster acc ,College_ApprovedMaster app , College_TrustMaster trust,
College_CityMaster city ,College_CountryMaster coun ,College_StateMaster stat,college_coursetypemaster course,
college_degreemaster degree
WHERE main.CollegeId = degTrans.CollegeId
and main.TrustId=trust.TrustId and main.ApprovedId= app.ApprovedId
and main.AccredationId= acc.AccredationId and univ.UniversityId = main.UniversityId
and main.CountryId = coun.CountryId and main.CityId = city.CityId
and course.courseTypeId=degTrans.courseTypeId and degree.degreeid=degTrans.degreeid and main.StateId= stat.StateId and course.CourseTypeId = @CourseId
and degree.DegreeId = @DegreeId and main.CollegeAccType = 'paid'
ORDER BY main.CollegeName
SELECT distinct main.[CollegeId],[CollegeLogo],[CollegeName]
,course.[CourseTypeId],degree.[DegreeId], main.PaidClientPage
,[CollegeDescription],[TypeofCollege],[Grade]
,main.[TrustId],[TrustName],main.[UniversityId],[UniversityName]
,main.[ApprovedId],[ApprovedName],main.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType]
,[AddressStreet],[AddressPin]
,main.[CityId],[CityName],main.[StateId],[StateName]
,main.[CountryId],[CountryName] into #tempCollegeData8
FROM CollegeMaster main , College_DegreeStreamTransition degTrans ,College_UniversityMaster univ,
College_AccredationMaster acc ,College_ApprovedMaster app , College_TrustMaster trust,
College_CityMaster city ,College_CountryMaster coun ,College_StateMaster stat, college_coursetypemaster course,
college_degreemaster degree
WHERE main.CollegeId = degTrans.CollegeId
and main.TrustId=trust.TrustId and main.ApprovedId= app.ApprovedId
and main.AccredationId= acc.AccredationId and univ.UniversityId = main.UniversityId
and main.CountryId = coun.CountryId and main.CityId = city.CityId
and course.courseTypeId=degTrans.courseTypeId and degree.degreeid=degTrans.degreeid
and main.StateId= stat.StateId and course.CourseTypeId = @CourseId
and degree.DegreeId = @DegreeId and main.CollegeAccType = 'Unpaid'
ORDER BY main.CollegeName
end
I wanted to write it in other way.....
Thanks & Regards,
Pallavi

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Why do you want to write it differently?

The way it is right now is one valid approach to tackle "catch-all-queries"[/url].

It might be an option to move the "common query" into a view. This wouldn't help performance but it might help to reduce duplicate code.

Another option would be dynamic sql.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]