dynamic sql search condition in store procedure

[Mogamboo_Khush_Hua]

This is my store procedure but where i am doing wrong

ALTER PROCEDURE [dbo].[Engagement_Get_StandardYears]
(
@clientName NVARCHAR(255)=NULL
)
AS
BEGIN

SET NOCOUNT ON;

SELECT Distinct \[Year\] 
	FROM Engagement\_Standards 
WHERE \[Year\] IS NOT NULL AND \[Client Name\] like 
CASE 
	WHEN @clientName IS NULL THEN %
	ELSE @clientName
END
ORDER BY \[Year\];

END

[Mycroft Holmes]

@clientName NVARCHAR(255)=NULL

Unless you are using unicode characters change all your nvarchar to varchar (non critical but I would do this early)

WHERE [Year] IS NOT NULL

I prefer yo use

WHERE Inull([Year],0) > 0

Also note you are using a reserved word in as a column name - not recommended. You should NEVER need square brackets Your killer though is this one

WHEN @clientName IS NULL THEN %
ELSE @clientName

change the % to '' [edit]No wait that is in the where clause I'm scratching my head trying to understand what you are trying to achieve! Ah got it dynamic where clause, here is one I use all the time

Where (IsNull(@clientName , '') = '' OR clientName = @clientName )

[/edit]

Never underestimate the power of human stupidity RAH

[Mogamboo_Khush_Hua]

I use some thing like this Mycroft Holmes.

USE [INVENTORY_ MANAGEMENT]
GO
/****** Object: StoredProcedure [dbo].[GetEngagementStandardsByCriteria] Script Date: 02/05/2010 15:25:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================

Create PROCEDURE [dbo].[Engagement_Get_StandardsByCriteria ]
-- Add the parameters for the stored procedure here
(
@ClientName NVARCHAR(255)=NULL,
@Year FLOAT=NULL,
@Application NVARCHAR(255) =NULL,
@Database NVARCHAR(255)=NULL,
@OS NVARCHAR(255)=NULL,
@QuickName NVARCHAR(MAX)=NULL

)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT \[Client Name\] AS Client,\[Year\],\[Application\],
	   \[Database\],OS,\[MLP Heading\] As Heading,
	   \[Standard Observation\] AS Observation,\[Standard Impact\]AS Impact,
	   \[Standard Recommendation\] AS Recommendation
FROM Engagement\_Standards
WHERE (@ClientName IS NULL OR \[Client Name\]=@ClientName)
	And
      (@Year IS NULL OR \[Year\]=@Year)
	And
	  (@Application IS NULL OR \[Application\]=@Application)
	And
      (@Database IS NULL OR \[Database\]=@Database)
	And
      (@OS IS NULL OR OS=@OS)
	And
      (@QuickName IS NULL OR \[Quick Name\]=@QuickName);

END

[Giorgi Dalakishvili]

This is the best article I have ever seen about dynamic search in sql: Dynamic Search Conditions in T-SQL[^]

Giorgi Dalakishvili #region signature My Articles Browsing xkcd in a windows 7 way[^] #endregion

[Mycroft Holmes]

Eeuww - how horrible, working with your database would drive me nuts, who decided to use descriptors as column names. However your where construction is fine. I have found that you hit a limit on the number of where filters like this you can use, after about 6 or 7 performance can be affected, I have no idea why is I have only seen it a couple of times.

Never underestimate the power of human stupidity RAH