how could i change the procedure???

Question

how could i change the procedure???

pallavi.unde

SSCommitted

Points: 1987
More actions
December 6, 2011 at 5:06 am

#391043

please tell me how could i change the procedure now..
i have written as below
ALTER procedure [dbo].[proc_GetCollegeData24]
(
@CourseId numeric(18,0),
@DegreeId numeric(18,0),
@streamid varchar(MAX),
@CityGroupId numeric(18,0)=null,
@mode char
)
As
-- to get college data when stream id is given and citygroup id is specified
if (@mode='A')
begin
SELECT distinct a.[CollegeId],a.[CollegeName], a.PaidClientPage
,b.[CourseTypeId],b.[DegreeId]
,[CollegeDescription],[TypeofCollege],[Grade]
,a.[TrustId],[TrustName],a.[UniversityId],[UniversityName]
,a.[ApprovedId],[ApprovedName],a.[AccredationId],[AccredationName]
,[Status],[YearofEst],[CollegeAccType]
,[AddressStreet],[AddressPin]
,a.[CityId],[CityName],a.[CityGroupId],[CityGroupName]
,a.[StateId],[StateName]
,a.[CountryId],[CountryName]
from collegemaster a inner join
college_degreestreamtransition b on a.collegeid = b.collegeid inner join College_UniversityMaster c on a.universityid=c.universityid inner join College_AccredationMaster d on a.AccredationId=d.AccredationId inner join College_ApprovedMaster e on a.approvedid=e.approvedid inner join College_TrustMaster f on a.trustid=f.trustid inner join College_CityMaster g on a.cityid=g.cityid inner join College_Citygroupmaster h on a.citygroupid=h.citygroupid inner join College_StateMaster i on a.stateid= i.stateid inner join college_countrymaster j on a.countryid=j.countryid
where b.coursetypeid=@CourseId and b.degreeid=@DegreeId
and b.streamgroupid LIKE '''%,'+@streamid+',%'''
or b.streamgroupid LIKE +@streamid+',%'+''
or b.streamgroupid LIKE ''+'%,''%'+@streamid+''
or b.streamgroupid LIKE +@streamid+'%'
end
pls reply
Thanks & Regards,
Pallavi

Viewing 4 posts - 1 through 3 (of 3 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Duplicate post, work in progress here.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ally Abbas SSC Enthusiast Points: 156 More actions · Answer 2

connect to the sql instance and run it.

Dont forget to run it against the correct DB or put:

USE [DB_NAME] before it

Hope this helps

SqlSpider... Ten Centuries Points: 1254 More actions · Answer 3

See Every Thing Is in Where Condition, Don't be Tensed.. Why Ur adding '+' Every Where...?

Just try With this and try to check with single Where Condition if it works fine then add another where condition and finally u will reach......

ALTER procedure [dbo].[proc_GetCollegeData24]

(

@CourseId numeric(18,0),

@DegreeId numeric(18,0),

@streamid varchar(MAX),

@CityGroupId numeric(18,0)=null,

@mode char = 'A'

)

As

-- to get college data when stream id is given and citygroup id is specified

begin

if (@mode='A')

SELECT distinct a.[CollegeId],a.[CollegeName], a.PaidClientPage

,b.[CourseTypeId],b.[DegreeId]

,[CollegeDescription],[TypeofCollege],[Grade]

,a.[TrustId],[TrustName],a.[UniversityId],[UniversityName]

,a.[ApprovedId],[ApprovedName],a.[AccredationId],[AccredationName]

,[Status],[YearofEst],[CollegeAccType]

,[AddressStreet],[AddressPin]

,a.[CityId],[CityName],a.[CityGroupId],[CityGroupName]

,a.[StateId],[StateName]

,a.[CountryId],[CountryName]

from collegemaster a

inner join college_degreestreamtransition b

on a.collegeid = b.collegeid

inner join College_UniversityMaster c

on a.universityid=c.universityid

inner join College_AccredationMaster d

on a.AccredationId=d.AccredationId

inner join College_ApprovedMaster e

on a.approvedid=e.approvedid

inner join College_TrustMaster f

on a.trustid=f.trustid

inner join College_CityMaster g

on a.cityid=g.cityid

inner join College_Citygroupmaster h

on a.citygroupid=h.citygroupid

inner join College_StateMaster i

on a.stateid= i.stateid

inner join college_countrymaster j

on a.countryid=j.countryid

where b.coursetypeid=@CourseId and b.degreeid=@DegreeId

and b.streamgroupid LIKE '%'+','+@streamid+','+'%'

or b.streamgroupid LIKE @streamid+','+'%'

or b.streamgroupid LIKE '%'+','+'%'+@streamid

or b.streamgroupid LIKE @streamid+'%'

end