help in stored procedure

Question

help in stored procedure

tsqlnewbie

Newbie

Points: 7
More actions
January 2, 2011 at 9:42 pm

#388539

hi i got a situation where i need to write a stored procedure to return result set to the front end
it should return results depending on the drop downs they select in front end
Those drop downs are like filters to the data i send them top 100 every time
can somebody help me with best practice to implement this because this procedure is called very often (every second or more times in a second)
TIA
ravi

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Also, can you give some more detail as to the full scenario? Thanks

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 2

tsqlnewbie (1/2/2011)
hi i got a situation where i need to write a stored procedure to return result set to the front end
it should return results depending on the drop downs they select in front end
Those drop downs are like filters to the data i send them top 100 every time
can somebody help me with best practice to implement this because this procedure is called very often (every second or more times in a second)
TIA
ravi

How you are going to pass the values to stored procedure? Comma separated?

Thanks

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

It sounds dangerously like a catch-all query, with 10-50 different parameters, any of which can be passed at any time and you have to write a query that deals with all them... etc.

These seem to come up about every other application that I develop. Usually it's meant as a reporting area. The better approach is to suggest setting up ad hoc reporting, create a reporting database, preferably one with a good star schema or other reporting structure. Then let the ad hoc queries go there rather than into a catch-all query.

But, for specific suggestions to your problem, please follow Gail's advice.

"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