Regarding store procedure

  • pallavi.unde

    SSCommitted

    Points: 1987

    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

  • LutzM

    SSC Guru

    Points: 107049

    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]

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

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