SQL Server Performance Forum – Threads Archive
reusable sp
hey guys i’m just new to sp..my question is how can i make stored procedure reusable somthin like this create PROCEDURE test@fields VARCHAR(100),
@criteria VARCHAR(100),
@sort varchar(50) AS
/* SET NOCOUNT ON */
select @fields from employee where @criteria order by @sort
GO
so i dont have to create sp for each query that i made with this employee table. pls. advice.TIA
do not create reusable procs,
that is an idea from C programming
reusable procs have horrible performance consequences in SQL Server and even other databases.
in fact, sometimes duplicating a stored proc,
so that 2 or more stored proc having exaclty the same code
one is called when a certain input criteria is supplied,
the other for a different set of criteria,
sometimes this can solve some severe performance problems get this concept completely out of your head, or go back to C programming
so should i stick with this kind of code or this is oldschool, maybe there is a better way? ALTER PROCEDURE getProject
@code varchar(5),
@option smallint
AS
SET NOCOUNT ON IF @option = 1 SELECT COL_A FROM MYTABLE ORDER BY COL_A,COL_B
IF @option = 2
SELECT COL_A FROM MYTABLE
WHERE CODE = @code
ORDER BY COL_B IF @option = 3
SELECT COL_B FROM MYTABLE
WHERE CODE = @code
ORDER BY COL_A
That looks correct approach
Otherwise you need to use Dynamic SQL which is not preferrable in this case Madhivanan Failing to plan is Planning to fail
Code reuse is a good thing. Unfortunately this doesn’t hold true when it comes to databases. A generic approach here mostly leads not only to messy code, but also suboptimal performance. If used wisely it surely helps achieving some encapsulation and reusability, but that’s not the case for your question. One link that almost always is mentioned in the regard ishttp://www.sommarskog.se/dynamic_sql.html Make sure to read it closely, although it’s a quite long article. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
]]>