Should I use dynamic SQL and combine 2 procs into 1?

rcook349

For dozens of entities, we 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

Manivannan.D.Sekaran

You are on right track but don't use the dynamic sql...

It has lot of issues...

You can achive it without dynamic sql also..

Code Snippet

Create Procedure dbo.SelectEmployee

(@EmployeeID uniqueidentifier= NULL)

as

Select

*

From

Employee

Where

EmployeeID = @EmployeeID

or

@EmployeeID is NULL

GO

--To get the List

Exec dbo.SelectEmployee NULL

--or just call the sp, since param has default value

Exec dbo.SelectEmployee

--To get the One employee

Exec dbo.SelectEmployee 'E7F29B95-A7A7-4145-B13C-EF68CFC5B991'

rh4m1ll3

CREATE PROCDURE dbo.SelectEmployee
(
@EmployeeID int = NULL
)
AS

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

GO

hunchback

That expression is not considered a search argument by SQL Server, avoiding SQL Server from using an "index seek" operation in case there is an index by that column, with high selectivity.

Example:

Code Snippet

create procedure dbo.p1

@customerid nchar(5) = NULL

as

set nocount on

select orderid, orderdate, customerid

from dbo.orders

where customerid = isnull(@customerid, customerid)

return @@error

go

dbcc freeproccache

dbcc dropcleanbuffers

go

set showplan_text on

go

exec dbo.p1 'ALFKI'

go

exec dbo.p1 NULL

go

set showplan_text off

go

drop procedure dbo.p1

go

Result:

exec dbo.p1 'ALFKI'

|--Clustered Index Scan(OBJECT:([northwind].[dbo].[Orders].[PK_Orders]), WHERE:([northwind].[dbo].[Orders].[CustomerID]=isnull([@customerid],[northwind].[dbo].[Orders].[CustomerID])))

exec dbo.p1 NULL

|--Clustered Index Scan(OBJECT:([northwind].[dbo].[Orders].[PK_Orders]), WHERE:([northwind].[dbo].[Orders].[CustomerID]=isnull([@customerid],[northwind].[dbo].[Orders].[CustomerID])))

It is hard to come with a solution that fit all situations, so having different stored procedures is sometime a good option. Here are a couple of articles that can help you with this theme.

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

Dynamic Search Conditions in T-SQL

http://www.sommarskog.se/dyn-search.html

AMB

Chris Howarth

Personally I think that the code below represents the best balance for this particular scenario.

If you have a CLUSTERED or NONCLUSTERED index on the EmployeeID column then the query with the WHERE clause shouldn't result in a table / clustered index scan (although this depends on your data).

Chris

Code Snippet

CREATE PROCEDURE dbo.SelectEmployee

@EmployeeID uniqueidentifier = NULL

AS

IF @EmployeeID IS NULL

BEGIN

SELECT *

FROM Employee

WHERE @EmployeeID = @EmployeeID

END

ELSE

BEGIN

SELECT *

FROM Employee

END

GO

pkr2000

I don't like using IF Blocks like that, you'll suffer from first-to-run caching problems. Sure there at XML plans and the like but you only want to use those in exceptional circumstances.