dynamically creating a select statement

I have a stored procedure in my database which will be used to search for records matching given criteria, such as within a date range or containing a keyword. The procedure stub is looking like this at the moment:

ALTER PROCEDURE [dbo].[search]
@.file_id int,
@.title_includes varchar(50),
@.notes_includes varchar(50),
@.updated_after datetime,
@.updated_before datetime,
@.deleted_after datetime,
@.deleted_before datetime,
@.size_bigger_than int,
@.size_smaller_than int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
--SELECT <@.Param1, sysname, @.p1>, <@.Param2, sysname, @.p2>
END

Ideally I would like criteria to only be used in the select statement if the value passed in is not null, but as far as i know it is not possible to place and if-then condition in the middle of a select statement to check if the parameter is null? Does anyone know of an efficient way to achieve this functionality? Any help will be greatly appreciated, thank you.

There's two approaches that I can think of that might help you.

Firstly, it's entirely valid to include criteria such as

WHERE (file_id = @.file_id or @.file_id is null)
AND (title_includes = @.title_includes or @.title_includes is null)
AND ...

in the stored procedure.

However, if you mean that you only want to see the file_id column in the results if the passed parameter @.file_id is not null, then you need to approach it differently, as follows:

declare @.select nvarchar(max)
declare @.where nvarchar(max)
declare @.selectsep nvarchar(5)
declare @.wheresep nvarchar(5)

set @.select = N''
set @.selectsep = N''
set @.where = N''
set @.wheresep = N''

if (@.file_id is not null)
begin
set @.select = @.select + @.selectsep + N'file_id'
set @.where = @.where + @.wheresep + N'file_id = @.file_id'
set @.selectsep = N','
set @.wheresep = N' and '
end

if (@.title_includes is not null)
begin
set @.select = @.select + @.selectsep + N'title_includes'
set @.where = @.where + @.wheresep + N'title_includes = @.title_includes'
set @.selectsep = N','
set @.wheresep = N' and '
end

... and so on for the other parameters ...

set @.select = N'SELECT <list of fields you always want to include>, ' + @.select + N'FROM <from clause>'
if (len(@.where) > 0)
begin
set @.select = @.select + N' WHERE ' + @.where
end

exec dbo.sp_executesql @.select
, N' @.file_id int, @.title_includes varchar(50), @.notes_includes varchar(50), @.updated_after datetime, @.updated_before datetime, @.deleted_after datetime, @.deleted_before datetime, @.size_bigger_than int, @.size_smaller_than int'
, @.file_id = @.file_id
, @.title_includes = @.title_includes
, @.notes_includes = @.notes_includes
, @.updated_after = @.updated_after
, @.updated_before = @.updated_before
, @.deleted_after = @.deleted_after
, @.deleted_before = @.deleted_before
, @.size_bigger_than = @.size_bigger_than
, @.size_smaller_than = @.size_smaller_than

Naturally, as you build up the @.select and @.where variables, you can include the usual range of operators (like, <, >, etc).

Let me know if you need any further assistance with this.

Iain

|||

You can try this:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[SearchCurrency]

@.currencycode nchar(5) = null,

@.currencyname nchar(25) = null

as

begin

declare @.stmt nvarchar(max)

set @.stmt = 'SELECT currencycode, currencyname

FROM currencies where 1=1 '

IF @.currencycode IS NOT NULL

set @.stmt = @.stmt + ' AND currencycode = '''+ @.currencycode + ''''

IF @.currencyname IS NOT NULL

set @.stmt = @.stmt + ' AND currencyname = ''' + @.currencyname +''''

exec(@.stmt)

end

|||

I highly recommend reading Erland's article on Dynamic SQL before going down this path. It may work well for you, but you should be properly informed about the pitfalls.

See:

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html