sql server programming: For dozens of entities, I have a Select (returns one record with @ID param)
and a SelectList (returns all records.. no param passed) stored procedure.
E.g...

CREATE PROCEDURE dbo.SelectEmployeeList
AS
SELECT *
FROM Employee

CREATE PROCEDURE dbo.SelectEmployee
@EmployeeID uniqueidentifier
AS
SELECT *
FROM Employee
WHERE @EmployeeID = @EmployeeID

Given the nearly duplicate syntax, I'm considering handling both via a
single proc (using dynamic SQL) like this:

CREATE PROCEDURE dbo.SelectEmployee
@EmployeeID uniqueidentifier = null
AS
DECLARE @Sql nvarchar(1000)
SELECT @Sql = 'SELECT * FROM Employee'
IF @EmployeeID IS NOT NULL
SELECT @Sql = @Sql + ' WHERE EmployeeID = ''' + CAST(@EmployeeID as
nvarchar(50)) + ''''
EXEC( @Sql)

Does this idea sound good or bad to you? Any drawbacks/reasons I shouldn't
do it?

Thanks,
Ron

Ronald
USE Northwind
GO

CREATE PROC dbo.Get_Orders
@orderid INT=NULL
AS
SELECT * FROM Orders WHERE orderid=COALESCE(@orderid,orderid)

--usage
EXEC dbo.Get_Orders --All rows
EXEC dbo.Get_Orders 10248 --one row


Note that performance of above query may or may not be soo good. Select only
needed columns and don't use '*'. Consider creating covering indexe on
selected columns
http://www.sql-server-performance.com/covering_indexes.asp

If you are in SQL Server 2005 ,use OPTION(RECOMPILE) that works per
statetment.
http://www.sqlmag.com/Article/ArticleID/94369/sql_server_94369.html






[quoted text, click to view]

Sylvain Lafontaine (sylvain aei ca (fill the blanks, no spam please))
writes:
[quoted text, click to view]

All three will scan the Employees table, and this is a suboptimal
plan, when a explicit id is given. Of course, if there is no index
on EmployeeID, this is not an issue.

Of these three, the first two has a gotcha, which is a good reason to
stay away from them: they do not work with nullable columns. It it is
not likely to matter here, since EmployeeID is likely to be a key and
not nullable. But assume that there is one more condition:

SELECT ...
FROM Employee
WHERE EmployeeID = coalesce(@EmployeeID, EmployeeID)
AND ShoeSize = coalesce(@ShoeSize, ShoeSize)

The user performs enters an employee id, without specifying a shoe size.
To his surprise, no data is returned. To wit, no shoe size had been
entered for this employee. Writing the query as:

SELECT ...
FROM Employee
WHERE (EmployeeID = @EmployeeID OR @EmployeeID IS NULL)
AND (ShoeSize = @ShoeSize OR @ShoeSizeIS NULL)

evades this problem.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Ronald S. Cook (rcook@westinis.com) writes:
[quoted text, click to view]

This situation is far too simple to warrant the complexity of dynamic
SQL. And if you do it, you should of course use sp_executesql with
a parameter:

sp_executesql @sql, N'@EmployeeID uniqueidentifier', @EmployeeID

If you feel that you get too many procedures, you could still
collapse them into one in this way:

IF @EmployeeID IS NULL
SELECT ... FROM Employee
ELSE
SELECT ... FROM Employee WHERE EmployeeID = @EmployeID

Finally, it's bad practice to use SELECT * in production good. Explicitly
list the columns you need.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Take a look at the following examples:

SELECT *
FROM Employee
WHERE EmployeeID = Coalesce (@EmployeeID, EmployeeID)

SELECT *
FROM Employee
WHERE EmployeeID = Case when @EmployeeID is Null then EmployeeId else
@EmployeeID End

SELECT *
FROM Employee
WHERE ((EmployeeID = @EmployeeID) or (@EmployeeID is Null))

The first one is usually the preferred one and should give you the best
execution plan along with the second. I think that the last one might give
a bad execution plan on many occasions but I'm not sure. In all cases,
using dynamic SQL will give you a good execution plan each time but the
reading of your code might become indigest if the query/SP is too
long/complicated.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


[quoted text, click to view]

[quoted text, click to view]

I would stay with separate procs here. Each specialized proc will have the
optimal execution plan that only needs to be generated once. That will be
best for performance.

Dynamic SQL can have performance benefits when you have complicated search
criteria that must be implemented in a single proc. However, there are
important security considerations. If you must resort to dynamic SQL, you
should either parameterize the query string or enclose string literal values
using QUOTENAME. Examples:

--parameterized query example snippet
IF @EmployeeID IS NOT NULL
BEGIN
SELECT @Sql = @Sql + ' WHERE EmployeeID = @P_EmployeeID'
END
EXEC sp_executesql @Sql, '@EmployeeID nvarchar(50)', @P_EmployeeID =
@EmployeeID

--literal query string example snippet
IF @EmployeeID IS NOT NULL
BEGIN
SELECT @Sql = @Sql + ' WHERE EmployeeID = ' + QUOTENAME(CAST(@EmployeeID
as nvarchar(50)), '''')
END
EXEC sp_executesql @Sql

In any case, see http://www.sommarskog.se/dynamic_sql.html for a thorough
discussion on dynamic SQL considerations.

--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]