combining multiple queries in one stored proc

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

combining multiple queries in one stored proc

Author

Topic

stevep
Starting Member

17 Posts

Posted - 2003-02-18 : 23:53:48

Just curious whether the following approach is recommended or not.

I've multiple lookup tables (all with just 2 columns, ID and value). I'm wondering whether it is recommended/advisable to combine all the select statements for bringing back single values from these tables into one SP, using a parameter to know which one to get.

Something like:
CREATE PROCEDURE getValue
@tableNum INT,
@tableVal INT

AS
IF @tableNum = 1
BEGIN
SELECT marketName FROM markets WHERE marketID = @tableVal
END
IF @tableNum = 2
BEGIN
SELECT processName FROM processes WHERE processID = @tableVal
END

etc
--

Whilst this will simplify my stored procedure list, I'm hesitating because I'm not sure how it will affect compilation/performance. Plus the ugliness of not having a SWITCH statement puts me off.

Any comments on whether this "one SP" approach is good or bad?

Thanks

Steve

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2003-02-19 : 07:01:09

I've done this before to try and reduce the number of stored procedures and it works pretty well. I don't believe there's any significant performance impact. You can also consider using some dynamic sql (sp_executesql) instead of/in combination with your method. Sometimes this can reduce the amount of conditional statements.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-02-19 : 07:44:16

Assuming the lookup values are of the same datatype, you can consolidate them into one lookup table and add a Category column to differentiate Markets from Processes, for example. Then you only have to query one table, and you can simply change the SELECT to:

SELECT Value FROM lookups WHERE Category='Market' AND ID=@tableVal

stevep
Starting Member

17 Posts

Posted - 2003-02-19 : 17:26:30

Thank you for both responses.

The idea of combining all the different categories in one table is so blindingly obvious (now) it was like being hit with a large piece of wood.

However a tad complex to move the data around. So version 1 will use the single SP with lots of statements. And I'll work on version 2 when complete.

Thanks.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-02-19 : 20:58:03

You might also consider a view that unions the tables and creates a virtual category column for you.

Then you can select from the view as if you had a single table without having to change all the other processes that update the tables.

This might be a good intermediate solution until you have the time to modify all the processes that would update a single table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources