Regarding store procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Notification Services » Administration » Regarding store procedure

Regarding store procedure

Rate Topic

Display Mode

Topic Options

Author

Message

pallavi.unde

Posted Saturday, February 11, 2012 4:52 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 14, 2013 5:35 AM
Points: 191, Visits: 170

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

Post #1250718

LutzM

Posted Saturday, February 11, 2012 5:49 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131

Why do you want to write it differently?
The way it is right now is one valid approach to tackle "catch-all-queries".

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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function

Post #1250733

« Prev Topic | Next Topic »

Permissions