storeprocedure not showing any data

Question

storeprocedure not showing any data

pallavi.unde

SSCommitted

Points: 1987
More actions
December 6, 2011 at 4:08 am

#261067

Below is the query in my store procedure..
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+',%'''
and b.streamgroupid LIKE +@streamid+',%'+''
and b.streamgroupid LIKE ''+'%,''%'+@streamid+''
and b.streamgroupid LIKE +@streamid+'%'
end
query is running perfectly but ,....., storeprocedure not showing any data.
please reply me.
Thanks & Regards,
Pallavi

Viewing 11 posts - 1 through 10 (of 10 total)

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

please post your full ddl scripts including sample data for all tables (inc indexes) and insert statements for the data so that we can assist you better

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

See In where Condition Ur Given lot's of ''And'' Operation for a Single String Finding...

Just Remove the ''AND'' And Try With ''OR'' Surly U will get the results over there....

Thanks,:-D

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

it does look like conditional logic errors with all the AND's.

if not and the logic is how the OP requires it then we need DDL, data, expected outputs and paramter values for @streamid

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Yes, the conditions in the WHERE clause do appear to be mutually exclusive. Don't change the ANDs to ORs just for the sake of getting it working, though - if you try to explain exactly what you're trying to do, maybe we can help. This is where table DDL and sample data help us to help you. And you need to post the full procedue definition so that we don't have to guess what the data types for the variables are, and so on.

You have quite a few JOINs in your query, and they're all INNER JOINs. This means that if any of them doesn't produce a match, you won't have any rows in your result set. Once you've got your WHERE clause sorted out, if you're still not getting any results, try removing one JOIN at a time to see which one isn't producing any matches.

John

pallavi.unde SSCommitted Points: 1987 More actions · Answer 5

I used or in to the storeprocedure,

It executed but not taking id's for all conditions,

I mean to say,

i have streamid in my transition table like

%,519%

%,519,%

%519,%

%519%'

so i gave the condition

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+'%'

but it showing the record of only LIKE +@streamid+'% condition... 🙁

Is there any problem in syntax of other one?????

Thanks & Regards,
Pallavi

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

pallavi.unde (12/6/2011)
Below is the query in my store procedure..
SELECT .....
where b.coursetypeid=@CourseId and b.degreeid=@DegreeId
and b.streamgroupid LIKE '''%,'+@streamid+',%'''
and b.streamgroupid LIKE +@streamid+',%'+''
and b.streamgroupid LIKE ''+'%,''%'+@streamid+''
and b.streamgroupid LIKE +@streamid+'%'
end
query is running perfectly but ,....., storeprocedure not showing any data.
please reply me.

This won't compile, there are syntax errors. In fact, only two of the LIKE expressions are correct.

Edit: only two

^{“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

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

Perhaps usage is supposed to be like this:

DECLARE @streamid VARCHAR(5)

SET @streamid = '519'

SELECT @streamid

FROM (

SELECT streamgroupid = '%,519%' UNION ALL

SELECT '%,519,%' UNION ALL

SELECT '%519,%' UNION ALL

SELECT '%519%'

) d

WHERE @streamid LIKE streamgroupid

SET @streamid = '519,'

SELECT @streamid

FROM (

SELECT streamgroupid = '%,519%' UNION ALL

SELECT '%,519,%' UNION ALL

SELECT '%519,%' UNION ALL

SELECT '%519%'

) d

WHERE @streamid LIKE streamgroupid

^{“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

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

Can U Plz Try with This Syntax....

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

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

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

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

and b.streamgroupid LIKE @streamid+'%'

end

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

OK, this is the last time I'm going to ask. I know it's extra effort for you to prepare and post full DDL, INSERT statements and procedure definitions, but if you don't then we're only guessing, and you are wasting everybody's time. I can't tell from what you've posted whether those % signs form part of the data or are wildcards. This is why we ask for the information in the way we do.

John

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 10

Performance on that query should be interesting. By interesting I mean, very, very slow.

Just so you know, LIKE statements with a leading wild card lead to scans with no opportunity for change without changing the structure of the query.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning