CODEWITHSUNDEEP
sql-serversql-server-2008stored-procedures
James
James

Reputation: 2246

Which style of stored procedure writing is better?

I want to write 5 SELECT statements of different types, but with the same purpose.

Should I write 5 individual stored procedure for them?

OR should I write one stored procedure and follow the CASE style to go to the specific stored procedure?

The 5 simple SELECT statements are like :

SELECT a, b, c FROM table1 WHERE vv = 'ss'
SELECT a, b, c FROM table2 WHERE vv = 'ss'

Upvotes: 1

Views: 102

Answers (2)

Schuere
Schuere

Reputation: 1649

You could also write a stored procedure with a dynamic query, where your parameter points to the correct table.

Create proc test (@table varchar(50))
AS
BEGIN
DECLARE @str varchar (MAX)
SET @str = 'SELECT a, b, c FROM ' + @table + ' WHERE vv = ''ss'''
EXEC(@str)

Pro's here: your tablename is dynamic
con's: U need to define this tablename where you call the procedure.

Upvotes: 0

Joe G Joseph
Joe G Joseph

Reputation: 24046

Performance wise there is advantage of having different SPs, as it can have different execution plans and SQL server can pick up the correct execution plan, more efficiently.

Having one procedure is more maintainable and will reduce code..

Upvotes: 3

Related Questions