Pass each parameter or one "where" statement ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Pass each parameter or one "where" statement ?

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2007-09-14 : 16:37:58

There are two store procedures. One is passing each parameter and another is passing entire "where" statement. Which one is better?
1)
ALTER PROCEDURE [dbo].[sp_order]
( @strWhere NVARCHAR(300))

AS
DECLARE @strSQL NVARCHAR(800)
BEGIN
SET @strSQL = 'select Orderby, Office, ordernum from ORDER'
SET @strSQL= @strSQL + @strWhere
END
EXEC(@strSQL)

2)
ALTER PROCEDURE [dbo].[sp_order]
@Orderby NVARCHAR(50),
@Office NVARCHAR(50),
@ordernum NVARCHAR(50)

....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-14 : 16:57:17

If both are using dynamic SQL, then neither is better. They both should be rewritten.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-09-14 : 21:54:42

How does the second one handle the WHERE clause?

That's the crux of these general purpose sprocs.

Any old WHERE clause will do for small tables. But for large tables a general purpose WHERE clause is often not handled well by the query optimiser, and all sorts of tricks become necessary to get half decent efficiency.

Performance (but not security!) is often better served at that point by using tight dynamic SQL with, say, sp_ExecuteSQL.

"If both are using dynamic SQL, then neither is better"

You may take issue with my last point then Tara!

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-14 : 23:51:31

quote:
Originally posted by Kristen

Performance (but not security!) is often better served at that point by using tight dynamic SQL with, say, sp_ExecuteSQL.

"If both are using dynamic SQL, then neither is better"

You may take issue with my last point then Tara!

Kristen

But the string is being passed in for both stored procs, so it wouldn't matter which way you went.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2007-09-15 : 07:20:52

Sorry, I forgot adding exec statement:

1) exec sp_order 'where Orderby = John, Office = Home, ordernum = 123'
2) exec sp_order 'John', 'Home', '123'

Kristen
Test

22859 Posts

Posted - 2007-09-16 : 03:47:12

"But the string is being passed in for both stored procs, so it wouldn't matter which way you went"

Sorry Tara, I probably wasn't clear.

I think an Sproc that does something like:


CREATE PROCEDURE MySProc
    @strParam1 varchar(100),
    @intParam2 int,
    ...
    @strorderByColName varchar(100)
AS
SELECT Col1, Col2, ...
FROM MyTable
WHERE    (@strParam1 IS NULL OR MyCol1 = @strParam1)
      OR (@intParam2 IS NULL OR MyCol2 = @intParam2)
      ...
ORDER BY CASE WHEN @strorderByColName = 'MyCol1' THEN MyCol1 ELSE NULL END,
         CASE WHEN @strorderByColName = 'MyCol2' THEN MyCol2 ELSE NULL END,
         ...

is likely [more particularly on large tables / lots of JOINs involved / etc.] to perform better using:


CREATE PROCEDURE MySProc
    @strParam1 varchar(100),
    @intParam2 int,
    ...
    @strorderByColName varchar(100)
AS
DECLARE @strSQL nvarchar(4000)

SELECT @strSQL = 'SELECT Col1, Col2, ...
FROM MyTable
WHERE    1=1 '
    + CASE WHEN @strParam1 IS NULL THEN '' ELSE ' AND MyCol1 = @strParam1' END
    + CASE WHEN @intParam2 IS NULL THEN '' ELSE ' AND MyCol2 = @intParam2' END
      ...
    + ' ORDER BY ' + @strorderByColName

EXEC sp_ExecuteSQL @strSQL, N'@strParam1 varchar(100), @intParam2 int', @strParam1, @intParam2

notwithstanding the fact that the second, Dynamic SQL, solution requires giving the user permissions on the underlying table(s)

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-17 : 01:57:22

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

Madhivanan

Failing to plan is Planning to fail

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-17 : 12:30:47

Thanks for the explanation Kristen. I thought either you or me was going crazy! My vote was on you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-09-17 : 12:32:13

"My vote was on you"

I can guess that without you needing to rub it in!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources