Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
debradeng
Starting Member
46 Posts |
 Posted - 2006-10-20 : 21:34:53
|
| I have created over a hundred stored procedures for each query.But since some queries perform similar functions.My question is how to use less SPs to implement all the functions,so that those SPs don't look stupid? what facters I should consider to improved those created SPs?Any suggestion would be lovely!Thank you! |
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2006-10-20 : 23:27:26
|
| Make stored procedures that covers many possibilities.CREATE PROC covermost (@naturalkey varchar(50) = null,@type int = null,@leftdate datetime = '1900-01-01 00:00:00.000',@rightdate datetime = '2100-12-31 00:00:00.000')ASSELECT anaturalkey, atype, adate FROM atableWHERE anaturalkey = COALESCE(@nuturalkey,anaturalkey)AND atype = COALESCE(@type, atype)AND adate BETWEEN @leftdate AND @rightdatehttp://www.sqlteam.com/item.asp?ItemID=2077simple selective control flow using IF statements depending on a parameter that can be used as a switch is also effective and although wordy makes very clear code and can handle a lot of trivial routines that have two or three options or CASES. Thus the proc while delivering or performing the same "idea" could run significantly different codeIf @parameter = USBEGINDo right side up codeENDELSEIF @parameter = AUSTRALIABEGINDo upside down codeENDELSE......Hint, If you are working with finacials get used to including date parameters often."it's definitely useless and maybe harmful". |
 |
|
|
debradeng
Starting Member
46 Posts |
Posted - 2006-10-21 : 01:13:47
|
| Thank you Sitka,I really appreciate that! |
|
|
|
|
|
|
How to create SP by given queries