"Ashton" <garbagecatc***@hotmail.com> wrote in message
news:1157652588.186400.296260@d34g2000cwd.googlegroups.com...
> /************************************************/
> Example Table
> /************************************************/
> create table UserInfoTest
> (
> FirstName varchar(100),
> LastName varchar(100)
> )
>
> /************************************************/
> Example Data
> /************************************************/
> insert into userinfotest (firstname, lastname)
> values ('John', 'Doe')
>
> insert into userinfotest (firstname, lastname)
> values ('John', 'Moe')
>
> insert into userinfotest (firstname, lastname)
> values ('Larry', 'Moe')
>
> insert into userinfotest (firstname, lastname)
> values ('Larry', 'Doe')
>
> /************************************************/
> Stored Proc #1
> /************************************************/
> create Procedure getUserTest1
> @firstname varchar(100),
> @LastName varchar(100)
> WITH RECOMPILE
> as
> select * from UserInfoTest
> where 0=0
> AND (@LastName = '' or LastName like  @LastName )
> AND (@FirstName = '' or FirstName like  @FirstName )
>
> /************************************************/
> Stored Proc #2
> /************************************************/
>
> create Procedure getUserTest2
> @firstname varchar(100),
> @LastName varchar(100)
> as
> Declare @SQL nvarchar(4000)
> set @SQL = ''
> set @SQL = @SQL + '
> select * from UserInfoTest
> where 0=0
> '
> IF @LastName <> '' set @SQL =  @SQL + ' and LastName like  N'''+
> @LastName +''''
> IF @FirstName <> '' set @SQL =  @SQL + ' and FirstName like  N'''+
> @FirstName +''''
> EXEC sp_executesql @SQL
> /************************************************/
> /************************************************/
> To use:
> exec getUserTest1 'Jo%', ''
> exec getUserTest1 '', 'M%'
> exec getUserTest1 'Jo%', 'Doe'
> exec getUserTest1 '', ''
>
> exec getUserTest2 'Jo%', ''
> exec getUserTest2 '', 'M%'
> exec getUserTest2 'Jo%', 'Doe'
> exec getUserTest2 '', ''
>
> /************************************************/
>
> Stored Proc #2 is noticeably faster on a large database; SP#1 could
> take 5 minutes to return a result where SP#2 takes less then 30
> seconds. It seems as if SQL cannot really determine the paths it should
> take for SP#1, even with recompile turned on. Anyone have an idea on
> what a solution would be?
>