Dynamic Where clause

I need to build a dynamic where clause. Somehow I can't get it to work.
Here's the stored procedure. I believe I'm not concat. the
@.WhereOrderByClause parameter correct? Does anybody have any idea's?
Joshua
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- ========================================
=====
-- Author: JBlubaugh
-- Create date: 03/13/2006
-- Description: Gets information for Program
-- Summary report
-- ========================================
=====
ALTER PROCEDURE [cle].[getProgramSummaryRpt]
-- Add the parameters for the stored procedure here
@.ProgName varchar(300),
@.ProgNo int,
@.StartDate datetime,
@.EndDate datetime,
@.ProgCatCode int,
@.ProgTypeName varchar(30),
@.OfficeCode varchar(3),
@.DateCreated datetime,
@.SortOrder varchar(10),
@.WhereOrderByClause varchar(500)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if @.ProgName IS NOT NULL
set @.WhereOrderByClause = ' WHERE p.ProgName IN (' + @.ProgName + ')'
if @.ProgNo IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
@.ProgNo + ')'
if @.StartDate IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.StartDate >= ' +
@.StartDate
if @.EndDate IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.EndDate <= ' +
@.EndDate
if @.ProgCatCode IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgCatCode IN ('
+ @.ProgCatCode + ')'
if @.ProgTypeName IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND t.ProgTypeName IN
(' + @.ProgTypeName + ')'
if @.OfficeCode IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND o.OfficeCode IN (' +
@.OfficeCode + ')'
if @.DateCreated IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' p.CreatedOn >= ' +
@.DateCreated
if @.SortOrder = 'p.ProgNo'
set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.ProgNo ASC'
else
set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.CreatedOn ASC'
-- Insert statements for procedure here
SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
FROM Programs p
LEFT OUTER JOIN ProgramLocations l
ON p.LocationCode = l.LocCode
LEFT OUTER JOIN ProgramCats c
ON p.ProgCatCode = c.ProgCatCode
LEFT OUTER JOIN ProgOffices o
ON p.ProgNo = o.ProgNo
LEFT OUTER JOIN ProgramTypes t
ON p.ProgTypeCode = t.ProgTypeCode
& @.WhereOrderByClause
END> if @.ProgName IS NOT NULL
> set @.WhereOrderByClause = ' WHERE p.ProgName IN (' + @.ProgName + ')'
Eep.
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html|||You need to use EXEC or sp_executesql to run dynamic SQL. You'll have to
store the exec string in a variable and then execute it:
DECLARE @.str varchar (8000)
set @.str = 'SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
FROM Programs p
LEFT OUTER JOIN ProgramLocations l
ON p.LocationCode = l.LocCode
LEFT OUTER JOIN ProgramCats c
ON p.ProgCatCode = c.ProgCatCode
LEFT OUTER JOIN ProgOffices o
ON p.ProgNo = o.ProgNo
LEFT OUTER JOIN ProgramTypes t
ON p.ProgTypeCode = t.ProgTypeCode'
& @.WhereOrderByClause
EXEC (@.str)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"gdjoshua" <gdjoshua@.discussions.microsoft.com> wrote in message
news:2B4A1C0E-6D0F-4EAF-809C-5CAAB2549E48@.microsoft.com...
I need to build a dynamic where clause. Somehow I can't get it to work.
Here's the stored procedure. I believe I'm not concat. the
@.WhereOrderByClause parameter correct? Does anybody have any idea's?
Joshua
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- ========================================
=====
-- Author: JBlubaugh
-- Create date: 03/13/2006
-- Description: Gets information for Program
-- Summary report
-- ========================================
=====
ALTER PROCEDURE [cle].[getProgramSummaryRpt]
-- Add the parameters for the stored procedure here
@.ProgName varchar(300),
@.ProgNo int,
@.StartDate datetime,
@.EndDate datetime,
@.ProgCatCode int,
@.ProgTypeName varchar(30),
@.OfficeCode varchar(3),
@.DateCreated datetime,
@.SortOrder varchar(10),
@.WhereOrderByClause varchar(500)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if @.ProgName IS NOT NULL
set @.WhereOrderByClause = ' WHERE p.ProgName IN (' + @.ProgName + ')'
if @.ProgNo IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
@.ProgNo + ')'
if @.StartDate IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.StartDate >= ' +
@.StartDate
if @.EndDate IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.EndDate <= ' +
@.EndDate
if @.ProgCatCode IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgCatCode IN ('
+ @.ProgCatCode + ')'
if @.ProgTypeName IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND t.ProgTypeName IN
(' + @.ProgTypeName + ')'
if @.OfficeCode IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND o.OfficeCode IN (' +
@.OfficeCode + ')'
if @.DateCreated IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' p.CreatedOn >= ' +
@.DateCreated
if @.SortOrder = 'p.ProgNo'
set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.ProgNo ASC'
else
set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.CreatedOn ASC'
-- Insert statements for procedure here
SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
FROM Programs p
LEFT OUTER JOIN ProgramLocations l
ON p.LocationCode = l.LocCode
LEFT OUTER JOIN ProgramCats c
ON p.ProgCatCode = c.ProgCatCode
LEFT OUTER JOIN ProgOffices o
ON p.ProgNo = o.ProgNo
LEFT OUTER JOIN ProgramTypes t
ON p.ProgTypeCode = t.ProgTypeCode
& @.WhereOrderByClause
END|||Tom,
I get this error message'?
Msg 403, Level 16, State 1, Procedure getProgramSummaryRpt, Line 62
Invalid operator for data type. Operator equals boolean AND, type equals
varchar.
Joshua
"Tom Moreau" wrote:

> You need to use EXEC or sp_executesql to run dynamic SQL. You'll have to
> store the exec string in a variable and then execute it:
> DECLARE @.str varchar (8000)
> set @.str = 'SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
> p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
> t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
> FROM Programs p
> LEFT OUTER JOIN ProgramLocations l
> ON p.LocationCode = l.LocCode
> LEFT OUTER JOIN ProgramCats c
> ON p.ProgCatCode = c.ProgCatCode
> LEFT OUTER JOIN ProgOffices o
> ON p.ProgNo = o.ProgNo
> LEFT OUTER JOIN ProgramTypes t
> ON p.ProgTypeCode = t.ProgTypeCode'
> & @.WhereOrderByClause
> EXEC (@.str)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "gdjoshua" <gdjoshua@.discussions.microsoft.com> wrote in message
> news:2B4A1C0E-6D0F-4EAF-809C-5CAAB2549E48@.microsoft.com...
> I need to build a dynamic where clause. Somehow I can't get it to work.
> Here's the stored procedure. I believe I'm not concat. the
> @.WhereOrderByClause parameter correct? Does anybody have any idea's?
> Joshua
>
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
>
> -- ========================================
=====
> -- Author: JBlubaugh
> -- Create date: 03/13/2006
> -- Description: Gets information for Program
> -- Summary report
> -- ========================================
=====
> ALTER PROCEDURE [cle].[getProgramSummaryRpt]
> -- Add the parameters for the stored procedure here
> @.ProgName varchar(300),
> @.ProgNo int,
> @.StartDate datetime,
> @.EndDate datetime,
> @.ProgCatCode int,
> @.ProgTypeName varchar(30),
> @.OfficeCode varchar(3),
> @.DateCreated datetime,
> @.SortOrder varchar(10),
> @.WhereOrderByClause varchar(500)
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> if @.ProgName IS NOT NULL
> set @.WhereOrderByClause = ' WHERE p.ProgName IN (' + @.ProgName + ')'
> if @.ProgNo IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
> @.ProgNo + ')'
> if @.StartDate IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.StartDate >= ' +
> @.StartDate
> if @.EndDate IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.EndDate <= ' +
> @.EndDate
> if @.ProgCatCode IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgCatCode IN ('
> + @.ProgCatCode + ')'
> if @.ProgTypeName IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND t.ProgTypeName IN
> (' + @.ProgTypeName + ')'
> if @.OfficeCode IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND o.OfficeCode IN (' +
> @.OfficeCode + ')'
> if @.DateCreated IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' p.CreatedOn >= ' +
> @.DateCreated
> if @.SortOrder = 'p.ProgNo'
> set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.ProgNo ASC'
> else
> set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.CreatedOn ASC
'
> -- Insert statements for procedure here
> SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
> p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
> t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
> FROM Programs p
> LEFT OUTER JOIN ProgramLocations l
> ON p.LocationCode = l.LocCode
> LEFT OUTER JOIN ProgramCats c
> ON p.ProgCatCode = c.ProgCatCode
> LEFT OUTER JOIN ProgOffices o
> ON p.ProgNo = o.ProgNo
> LEFT OUTER JOIN ProgramTypes t
> ON p.ProgTypeCode = t.ProgTypeCode
> & @.WhereOrderByClause
> END
>
>|||Try something like this in case because the first one may not exist
set @.WhereOrderByClause = ' WHERE 1=1'
if @.ProgName IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgName IN ('
+ @.ProgName + ')'
if @.ProgNo IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
@.ProgNo + ')'|||I've already fixed the other part... this is what i'm trying:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- ========================================
=====
-- Author: JBlubaugh
-- Create date: 03/13/2006
-- Description: Gets information for Program
-- Summary report
-- ========================================
=====
ALTER PROCEDURE [cle].[getProgramSummaryRpt]
-- Add the parameters for the stored procedure here
@.ProgName varchar(300),
@.ProgNo int,
@.StartDate datetime,
@.EndDate datetime,
@.ProgCatCode int,
@.ProgTypeName varchar(30),
@.OfficeCode varchar(3),
@.DateCreated datetime,
@.SortOrder varchar(10),
@.WhereOrderByClause varchar(500)
AS
BEGIN
Declare @.str varchar (8000)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
set @.WhereOrderByClause = ' WHERE '
if @.ProgName IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + 'p.ProgName IN (' +
@.ProgName + ')'
if @.ProgNo IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
@.ProgNo + ')'
if @.StartDate IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.StartDate >= ' +
@.StartDate
if @.EndDate IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.EndDate <= ' +
@.EndDate
if @.ProgCatCode IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgCatCode IN ('
+ @.ProgCatCode + ')'
if @.ProgTypeName IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND t.ProgTypeName IN
(' + @.ProgTypeName + ')'
if @.OfficeCode IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND o.OfficeCode IN (' +
@.OfficeCode + ')'
if @.DateCreated IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' p.CreatedOn >= ' +
@.DateCreated
if @.SortOrder = 'p.ProgNo'
set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.ProgNo ASC'
else
set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.CreatedOn ASC'
set @.str = 'SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
FROM Programs p
LEFT OUTER JOIN ProgramLocations l
ON p.LocationCode = l.LocCode
LEFT OUTER JOIN ProgramCats c
ON p.ProgCatCode = c.ProgCatCode
LEFT OUTER JOIN ProgOffices o
ON p.ProgNo = o.ProgNo
LEFT OUTER JOIN ProgramTypes t
ON p.ProgTypeCode = t.ProgTypeCode'
& @.WhereOrderByClause
-- Insert statements for procedure here
EXEC (@.str)
END
"JeffB" wrote:

> Try something like this in case because the first one may not exist
> set @.WhereOrderByClause = ' WHERE 1=1'
> if @.ProgName IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgName IN ('
> + @.ProgName + ')'
> if @.ProgNo IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
> @.ProgNo + ')'
>|||& is not for concatenation (someone's been playing with VB/VBScript). You
should use + instead of &
"gdjoshua" <gdjoshua@.discussions.microsoft.com> wrote in message
news:363E0952-AC3F-44FF-959E-B822DFAD7EEB@.microsoft.com...
> Tom,
> I get this error message'?
> Msg 403, Level 16, State 1, Procedure getProgramSummaryRpt, Line 62
> Invalid operator for data type. Operator equals boolean AND, type equals
> varchar.|||What if @.ProgName is NULL and @.ProgNo is 3? Then the sql generated
will be 'WHERE AND p.ProgNo IN (3)' which won't work. The initial
setting should be 'WHERE 1 = 1
set @.WhereOrderByClause = ' WHERE '
if @.ProgName IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause +
'p.ProgName IN (' +
@.ProgName + ')'
if @.ProgNo IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND
p.ProgNo IN (' +
@.ProgNo + ')'|||Doh! I do that a lot - jumping back and forth between VB and T-SQL. :-S
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eReETs6RGHA.1948@.TK2MSFTNGP09.phx.gbl...
& is not for concatenation (someone's been playing with VB/VBScript). You
should use + instead of &
"gdjoshua" <gdjoshua@.discussions.microsoft.com> wrote in message
news:363E0952-AC3F-44FF-959E-B822DFAD7EEB@.microsoft.com...
> Tom,
> I get this error message'?
> Msg 403, Level 16, State 1, Procedure getProgramSummaryRpt, Line 62
> Invalid operator for data type. Operator equals boolean AND, type equals
> varchar.|||Use = instead of in, unless you are actually dealing with a list of values
contained within a string. It works either way, but is easier to understand
with the =.
When concatenating the string together, you need to place quotes around your
values, or pass them explicitly as parameters with sp_executesql. Two
single quotes are used to represent single quotes within quotes.
i.e.
set @.ProgName = 'Test'
set @.WhereOrderByClause = ' WHERE p.ProgName = ''' + @.ProgName + ''''
the resulting strign is
WHERE p.ProgName = 'Test'
or, pass the parameters to the sp_executesql:
set @.ProgName = 'Test'
set @.WhereOrderByClause = ' WHERE p.ProgName = @.ProgName'
set @.SelectString = 'Select * from SomeTable ' + @.WhereOrderByClause
SET @.ParmDefinition = '@.ProgName varchar(300)'
/* Execute the string with the first parameter value. */
EXECUTE sp_executesql @.SelectString, @.ParmDefinition, @.ProgName =
@.ProgName
"gdjoshua" <gdjoshua@.discussions.microsoft.com> wrote in message
news:2B4A1C0E-6D0F-4EAF-809C-5CAAB2549E48@.microsoft.com...
> I need to build a dynamic where clause. Somehow I can't get it to work.
> Here's the stored procedure. I believe I'm not concat. the
> @.WhereOrderByClause parameter correct? Does anybody have any idea's?
> Joshua
>
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
>
> -- ========================================
=====
> -- Author: JBlubaugh
> -- Create date: 03/13/2006
> -- Description: Gets information for Program
> -- Summary report
> -- ========================================
=====
> ALTER PROCEDURE [cle].[getProgramSummaryRpt]
> -- Add the parameters for the stored procedure here
> @.ProgName varchar(300),
> @.ProgNo int,
> @.StartDate datetime,
> @.EndDate datetime,
> @.ProgCatCode int,
> @.ProgTypeName varchar(30),
> @.OfficeCode varchar(3),
> @.DateCreated datetime,
> @.SortOrder varchar(10),
> @.WhereOrderByClause varchar(500)
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> if @.ProgName IS NOT NULL
> set @.WhereOrderByClause = ' WHERE p.ProgName IN (' + @.ProgName + ')'
> if @.ProgNo IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
> @.ProgNo + ')'
> if @.StartDate IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.StartDate >= ' +
> @.StartDate
> if @.EndDate IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.EndDate <= ' +
> @.EndDate
> if @.ProgCatCode IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgCatCode IN ('
> + @.ProgCatCode + ')'
> if @.ProgTypeName IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND t.ProgTypeName IN
> (' + @.ProgTypeName + ')'
> if @.OfficeCode IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND o.OfficeCode IN (' +
> @.OfficeCode + ')'
> if @.DateCreated IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' p.CreatedOn >= ' +
> @.DateCreated
> if @.SortOrder = 'p.ProgNo'
> set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.ProgNo ASC'
> else
> set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.CreatedOn
ASC'
> -- Insert statements for procedure here
> SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
> p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
> t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
> FROM Programs p
> LEFT OUTER JOIN ProgramLocations l
> ON p.LocationCode = l.LocCode
> LEFT OUTER JOIN ProgramCats c
> ON p.ProgCatCode = c.ProgCatCode
> LEFT OUTER JOIN ProgOffices o
> ON p.ProgNo = o.ProgNo
> LEFT OUTER JOIN ProgramTypes t
> ON p.ProgTypeCode = t.ProgTypeCode
> & @.WhereOrderByClause
> END
>