I do a number of transaction oriented queries such as:
select top 1 * from data_table where (([field_name]='data_value') AND
([identity]>6556))

Now would this be possible as a stored procedure:
find_eq(data_table, field_name, data_value, 6556)

and have the stored procedure execute the select and give me back the
results.

If possible would it be benefitial from a performance standpoint?

Thank,
David

(quincy451 (AT) yahoo (DOT) com) writes:
Yes and no. First, it's important to understand that you cannot pass
table or column names as parameters to stored procedure. (Well you
can, but then you would have to build the SQL string in the stored
procedure, and you would be back where you started.) So you would have
to have one procedure per table and column.

This may sound clunky to you, but this is exactly where performance comes
in. Say that you have these two procedures:

CREATE PROCEDURE get_this_data @data_value varchar(20),
@ident int AS
SELECT TOP 1 col1, col2, col3
FROM this_table
WHERE col8 = @data_value
AND col1 > @ident

CREATE PROCEDURE get_that_data @data_value varchar(20),
@ident int AS
SELECT TOP 1 col1, col2, col3
FROM that_table
WHERE col8 = @data_value
AND col1 > @ident

While these procedures look very similar to each other, it may not be
that they get the same query plan. Maybe there are different indexes
for the two tables. Or maybe the data distribution is different. Say
that in the procedures above, there are non-clustered indexes on
col1 and col8 for both tables. But in this_table, there are very many
different values in col8, where as there are only three different values
in this_table. Therefore it is very likely that the optimizer will
use the index on col8 for the first table, but not for the second.

Now, the optimizer will build a plan, no matter you submit a fix SQL
statement, or use a stored procedure. And the execution time for
running the query will be the same. The difference is that if you
submit these two calls from the client:

select top 1 * from data_table where (([field_name]='data_value') AND
([identity]>6556))
select top 1 * from data_table where (([field_name]='data_value2') AND
([identity]>4147))

SQL Server will spend time on compiling a query plan in both cases.
Whereas if you have stored procedures, the query plan will be cached
and reused. And that's where you have the performance gain. And
depending on your SQL statements and how frequent they are, this can
be a matter of make or break for your application - or have very little
impact at all.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx