Dynamic Where clause

What is the best way to dynamically choose the where clause based on a
variable.
In the following test example, depending on @.i value, WHERE clause could
compare against being null or not null.
Another way to do would be writing ugly sql string like @.select + @.where
Please let me know.
TIA...
set nocount on
go
create table z_test_del
(
c1 int,
c2 int
)
go
insert z_test_del values(1,null)
insert z_test_del values(2,333)
insert z_test_del values(3,null)
insert z_test_del values(4,5555)
go
declare @.i int
set @.i = 0
if (@.i = 0)
select * from z_test_del where c2 is null
else
select * from z_test_del where c2 is not null
go
drop table z_test_del
go>> What is the best way to dynamically choose the where clause based on a
variable. <<
Dynamic is poor choice of words in SQL -- it implies that you are
writing code on the fly.
SELECT *c1, c2 -- never use * in production code!!
FROM Foobar
WHERE (c2 IS NULL AND @.flag = 0)
OR (c2 IS NOT NULL AND @.flag <> 0);|||You can use a CASE or use OR-ed predicates or write two separate statements.
For some alternatives refer to: http://www.sommarskog.se/dyn-search.html
Anith|||Thanks Joe.
"--CELKO--" wrote:

> variable. <<
> Dynamic is poor choice of words in SQL -- it implies that you are
> writing code on the fly.
> SELECT *c1, c2 -- never use * in production code!!
> FROM Foobar
> WHERE (c2 IS NULL AND @.flag = 0)
> OR (c2 IS NOT NULL AND @.flag <> 0);
>

Dynamic WHERE clause

Hi
I have a WHERE clause, based on Params, passed in from a report, however, I
cannot get it to work properly.
I have to return two product codes, when a certain Types are passed in,
however, when it's anything else, I am only to return one product type.
Here's a sample of what I am trying to do:
WHERE
[Product Code] IN
(CASE
WHEN @.Type IN ('MD', 'BI') THEN
'CC', 'PO'
ELSE
'CC'
END)
Kind Regards
RickyTry
WHERE [Product Code] = 'CC' OR ( [Product Code] = 'PO' AND @.Type IN ('MD',
'BI') )
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"ricky" <ricky@.ricky.com> wrote in message
news:u$sWO8fjGHA.4716@.TK2MSFTNGP03.phx.gbl...
> Hi
> I have a WHERE clause, based on Params, passed in from a report, however,
> I
> cannot get it to work properly.
> I have to return two product codes, when a certain Types are passed in,
> however, when it's anything else, I am only to return one product type.
> Here's a sample of what I am trying to do:
>
> WHERE
> [Product Code] IN
> (CASE
> WHEN @.Type IN ('MD', 'BI') THEN
> 'CC', 'PO'
> ELSE
> 'CC'
> END)
>
> Kind Regards
> Ricky
>|||No, you can't do this. You can try something like this though...
WHERE
[Product Code] IN
(CASE
WHEN @.Type IN ('MD', 'BI') THEN
'PO'
ELSE
'CC'
END, 'CC')
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi chaps
Thank you both for the suggestion, couldn't work this out, been stuck for a
few hours. I thought it would be a simple thing to do...anyway, thanks
again.
Kind Regards
Ricky
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:06AFD45A-62AB-49EE-AFED-F6095D3C0C2C@.microsoft.com...
> No, you can't do this. You can try something like this though...
> WHERE
> [Product Code] IN
> (CASE
> WHEN @.Type IN ('MD', 'BI') THEN
> 'PO'
> ELSE
> 'CC'
> END, 'CC')
>
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>

Dynamic where clause

I am trying to write a stored procedure usp_select using dynamic sql to select from a table. The stored procedure will accept the where clause and/or the where clause parameters. I have tried 3 different methods -

Method 1 -

exec usp_select @.whereCondition='col1 like ''abc%'' and col2 = ''xyz'''

In usp_select, I'll build and execute the sql like -

set @.sql = N'select * from table ' + @.whereConition

exec sp_executesql @.sql

(basically @.sql becomes - select * from table where col1 like 'abc%' and col2 = 'xyz')

Method 2 -

exec usp_select @.whereCondition='col1 like @.p1 and col2 = @.p2', @.WhereParams='@.p1=abc%,@.p2=xyz'

In usp_select, I'll parse out the values in @.WhereParams and then build and execute the sql like -

set @.sql = N'declare @.p1 nvarchar(10),

@.p2 nvarchar(10);

set @.p1 = ''' + @.parsedValue1 + ''', @.p2 = ''' + @.parsedValue2 + '''; ' +

N'select col1 from table1 ' + @.whereCondition

exec (@.sql)

(basically @.sql becomes - declare @.p1 nvarchar(10), @.p2 nvarchar(10);

set @.pt = 'abc%', @.p2 = 'xyz';

select col1 from table1 where col1 like @.p1 and col2 = @.p2)

Method 3 -

similar to Method 2 but exec(@.sql) will be structured to become -

exec(declare @.vparam nvarchar(100), @.p1 nvarchar(10), @.p2 nvarchar(10);

set @.vparam='@.p1 nvarchar(10), @.p2 nvarchar(10)'

set @.p1 = 'abc%', @.p2 = 'xyz';

execute sp_executesql N''select col1 from table1 where col1 like @.p1 and col2 = @.p2', @.vparam, @.p, @.p2)

When I run sql profiler on the 3 methods, method 1 and 2 always result in a Cache Miss on the entire sql structure.

On method 3, a Cache Miss always occurs on the first part of the sql, ie, the first 3 lines where I declare and set the variables. Then a Cache Hit will happen on the execute sp_executesql part.

Do I have any performance gain using method 3 with both a Cache Miss and a Cache Hit?

I hope this is not too confusing. Because I do not know the where condition to the select procedure and hardcoding the values as in method 1 always results in a Cache Miss, therefore, I come up with the ideas in Method 2 and 3.

Any advice would be appreciated.

Yes. The Method-3 is recommanded to use.

The BOL says,

Because the actual text of the Transact-SQL statement in the sp_executesql string does not change between executions, the query optimizer will probably match the Transact-SQL statement in the second execution with the execution plan generated for the first execution. Therefore, SQL Server does not have to compile the second statement.

|||

What I don't understand is in Method2, the select part of @.sql is also static with parameters, why does it still result in a cache miss?

Why does sql profiler treat the entire @.sql string in Method2 as one sql statement but in Method3, it seems to treat it as two and result in both a cache miss and a cache hit? I am building @.sql the same way in both Method 2 and 3, the only difference is method 3 uses sp_executesql and the method 2 does not.

|||

LK,

1 - The optimizer could choose to not put the plan in cache, if it is sheap enough to compile it every time. Add event "SP:CacheInsert" to see if it is adding it.

2 - Even if it adds it, the batch is using variables in the expression on the "where" clause, so the query optimizer will not use the histogram (in case you have proper indexes for c1 and c2) from the index statistics to estimate cardinality, instead it will use the value of "All Density" associate with the group of columns. While using method 3, will use the histogram properly.

Statistics Used by the Query Optimizer in Microsoft SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

AMB

|||

Thank you. The articles are quite helpful too.

Dynamic Where clause

Hello everyone,

I want to build a dynamic where clause which makes :

WHERE column1 = (@.parameter1 if @.parameter1 is not null) / (anything if @.parameter1 is null)

Basically I do not know how to set column1 = ANYTHING

Best regards and thanks.

Maybe something like

Code Snippet

Where @.parmameter1 is null

or @.parameter1 is not null and column1 = @.parameter1

Another alternative would be to use IF / ELSE around two distinct select statements rather than this particular WHERE clause. I suspect that this where syntax (and also the COALESCE syntax) will precipitate a SCAN instead of a seek. If your table is very small this won't matter.

You might be able to avoid the SCAN when you pass the parameter by using the IF / ELSE syntax PROVIDED that you have an index on column1.

|||

You can also use the coalesce function to determine which value to use. Here is an example using sys.databases

Code Snippet

DECLARE @.param1 NVARCHAR(20)

SET @.param1 = 'master'

-- SET @.param1 = NULL

SELECT * FROM sys.databases

WHERE name = COALESCE(@.param1, name)

Try it with both parameter settings. One will return just the row for master, the other will return all rows.

|||@. Kent Waldrop Thank you very much for this trick.. It seems simple but does a lot !

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
>

Dynamic WHERE Clause

Hi
I have a query which has a few different Time Period columns:
Half_Year (H1,H2)
Quarters (Q1,Q2,Q3,Q4)
Months (M1,M2,M3,... M12)
These periods are held in three difference columns.
I need to run this query with 2 params. One will be the year and other will
be one of the above three:
i.e
sp_Rating 2005, 'H1'
This is all transactions in months 1=6 for the year 2005.
or sp_Rating 2005, 'Q3' or sp_Rating 2005, 'M7'
How can I dynamically interrogate the correct column, based on the param
supplied (H, Q, M) ?
Kind Regards
Ricky
(WIN2K,SQL2K-SP4)Ricky (ricky@.msn.com) writes:
> I have a query which has a few different Time Period columns:
> Half_Year (H1,H2)
> Quarters (Q1,Q2,Q3,Q4)
> Months (M1,M2,M3,... M12)
> These periods are held in three difference columns.
> I need to run this query with 2 params. One will be the year and other
> will be one of the above three:
> i.e
> sp_Rating 2005, 'H1'
> This is all transactions in months 1=6 for the year 2005.
> or sp_Rating 2005, 'Q3' or sp_Rating 2005, 'M7'
> How can I dynamically interrogate the correct column, based on the param
> supplied (H, Q, M) ?
First of all, don't call your procedures sp_something. That prefix is
reserved for system procedures, and SQL Server first looks for a
procedure with such a name in the master database.
As for the question, I'm afraid that I dno't really understand. Does
this table has three columns? May then I ask the stupid question if
H1 can appear together with M7 to M12? I realise that if periodisation
takes place, this can happen. Then again, could a row really have
H1, Q3 and M10?
If you need all three columns try this:
SELECT ...
FROM tbl
WHERE (Halt_year = @.period AND @.period LIKE 'H%')
OR (Quarter = @.period AND @.period LIKE 'Q%')
OR (Months = @.period AND @.period LIKE 'M%')
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> I have a query which has a few different Time Period columns: <<
Really? Mind showing us? Please post DDL, so that people do not have
to guess what the keys, constraints, Declarative Referential Integrity,
data types, etc. in your schema are. Sample data is also a good idea,
along with clear specifications. It is very hard to debug code when
you do not let us see it.
Why? Are they LOGICALLY DIFFERENT? There is usually only one kind of
time in the universe. Try a proper design:
CREATE TABLE PeriodCalendar
(period_name CHAR(15) NOT NULL PRIMARY KEY
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL);
INSERT INTO PeriodCalendar ('Q1-2006', '2006-01-01', '2006-03-341');
etc. for all the possible fiscal, marketing and calendaral periods you
use.
Again, not a good design; you seem to think that a year should be an
integer, while the ANSI standard say it is a CHAR(4); get a copy of the
8601 standards, too. Now life a JOIN and a BETWEEN predicate.
And you have already been told about not using "sp_" prefixes because
they refer to where something is phycially located as well as having a
special meaning in SQL Server.|||Thanks guys for the reply, I'm ashamed to say that I have been using the
'sp_' prefix, I did no know that this was the case, I shall rename mine to
something else today/tomorrow.
Kind Regards
Ricky
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1149463964.082284.170110@.i39g2000cwa.googlegroups.com...
> Really? Mind showing us? Please post DDL, so that people do not have
> to guess what the keys, constraints, Declarative Referential Integrity,
> data types, etc. in your schema are. Sample data is also a good idea,
> along with clear specifications. It is very hard to debug code when
> you do not let us see it.
>
> Why? Are they LOGICALLY DIFFERENT? There is usually only one kind of
> time in the universe. Try a proper design:
> CREATE TABLE PeriodCalendar
> (period_name CHAR(15) NOT NULL PRIMARY KEY
> start_date DATETIME NOT NULL,
> end_date DATETIME NOT NULL);
> INSERT INTO PeriodCalendar ('Q1-2006', '2006-01-01', '2006-03-341');
> etc. for all the possible fiscal, marketing and calendaral periods you
> use.
>
other will be one of the above three: sp_Rating 2005, 'H1' <<
> Again, not a good design; you seem to think that a year should be an
> integer, while the ANSI standard say it is a CHAR(4); get a copy of the
> 8601 standards, too. Now life a JOIN and a BETWEEN predicate.
> And you have already been told about not using "sp_" prefixes because
> they refer to where something is phycially located as well as having a
> special meaning in SQL Server.
>|||That's a mistake that we all make, until someone points out to us the
dangers.
In addition to what --CELKO-- posted, check out this article on calendar
tables, which goes into much more detail.
http://www.aspfaq.com/show.asp?id=2519
"ricky" <ricky@.ricky.com> wrote in message
news:O5wFxWHiGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Thanks guys for the reply, I'm ashamed to say that I have been using the
> 'sp_' prefix, I did no know that this was the case, I shall rename mine to
> something else today/tomorrow.
> Kind Regards
> Ricky
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1149463964.082284.170110@.i39g2000cwa.googlegroups.com...
> other will be one of the above three: sp_Rating 2005, 'H1' <<
>|||Joe,
Just curious, what is the reason for making year a varchar instead of an
integer (in the standard)?
I have always thought of it as an integer that one might add and subtract
from for various date functions (of course leap year can complicate it).
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1149463964.082284.170110@.i39g2000cwa.googlegroups.com...
> Again, not a good design; you seem to think that a year should be an
> integer, while the ANSI standard say it is a CHAR(4); get a copy of the
> 8601 standards, too. Now life a JOIN and a BETWEEN predicate.|||
--CELKO-- wrote:

>Again, not a good design; you seem to think that a year should be an
>integer, while the ANSI standard say it is a CHAR(4); get a copy of the
>8601 standards, too. Now life a JOIN and a BETWEEN predicate.
>
ANSI 8601 is a standard for the "representation" of dates and times.
It's not a standard for how to store them. If you think the ANSI standard
says that a year *is* a char(4), please quote the relevant section of the
standard.
My copy of the standard says only that "In *expressions* of calendar
dates, year is generally *represented* by four digits..." [emphasis mine]
Steve Kass
Drew University
http://www.stevekass.com|||>> Just curious, what is the reason for making year a varchar instead of an
integer (in the standard)? <<
Mostly history. We based the SQL Standard on the pre-existing the
ISO-8601 Standards which is for display; it says nothing about internal
storage. We wanted to avoid anything to do with internal storage, like
specifying the use of numbers for dates.
You can see this with the specs for "EXTRACT(<temporal unit> FROM
<temporal exp)>" and the fact you have to use strings with "INTERVAL
<exp> <temporal unit>"; that is "INTERVAL '12' YEAR" works and
"INTERVAL 12 YEAR" is an error. What newbies will do is use integers
and force a casting in certain places.
Of course, by the time we got something into SQL, each vendor had a
proprietary library and storage method which was exposed to the users.
Rats!!|||
--CELKO-- wrote:

>Mostly history. We based the SQL Standard on the pre-existing the
>ISO-8601 Standards which is for display; it says nothing about internal
>storage. We wanted to avoid anything to do with internal storage, like
>specifying the use of numbers for dates.
>You can see this with the specs for "EXTRACT(<temporal unit> FROM
><temporal exp)>" and the fact you have to use strings with "INTERVAL
><exp> <temporal unit>"; that is "INTERVAL '12' YEAR" works and
>"INTERVAL 12 YEAR" is an error. What newbies will do is use integers
>and force a casting in certain places.
>
The reason the <interval string> is a string is that its domain is not
<integer>. The domain includes '1-4', for example.
This has nothing whatsoever to do with the issue of whether ANSI 8601
says that
a "year is a char(4)" (it does not).
SK

>Of course, by the time we got something into SQL, each vendor had a
>proprietary library and storage method which was exposed to the users.
> Rats!!
>
>|||I apologise if I have caused a disagreement, but is there an issue, if I do
format YEAR as an INT?
"Steve Kass" <skass@.drew.edu> wrote in message
news:uZknxeMiGHA.4080@.TK2MSFTNGP03.phx.gbl...
>
> --CELKO-- wrote:
>
an integer (in the standard)? <<
> The reason the <interval string> is a string is that its domain is not
> <integer>. The domain includes '1-4', for example.
> This has nothing whatsoever to do with the issue of whether ANSI 8601
> says that
> a "year is a char(4)" (it does not).
> SK
>

Dynamic WHERE & Dates

My dynamic sql correctly returns the top 10 dates with records if @.timeID
equals 1. I need the @.timeID equals 2 part to return records between
@.dtStartDate and @.dtEndDate. (I just set the enddate equal to maxdate to
keep my example simple).
In the below section where @.timeID = 2, I'm getting a syntax error. Can
anyone help with the part below that tests IF @.timeID = 2 ? I just need some
quote help with that part of the where clause.
This runs on Northwind.
CODE ****************************************
***
declare @.SQL varchar(1000), @.typeID int, @.timeID int
declare @.dtStartDate datetime, @.dtEndDate datetime, @.dtMaxDate datetime
set @.typeID = 1
SET @.timeID = 1
set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
set @.dtStartDate = (SELECT DATEADD(day, -20, @.dtMaxDate))
set @.dtEndDate = @.dtMaxDate
SET @.SQL = 'SELECT Orders.OrderDate, '
IF @.typeID = 1
SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
IF @.typeID = 2
SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
SET @.SQL = @.SQL + 'FROM Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID
WHERE '
IF @.timeID = 1 /* Return last 10 days */
SET @.SQL = @.SQL + ' Orders.OrderDate IN (SELECT DISTINCT TOP 10
Orders.OrderDate
FROM Orders WHERE Orders.OrderDate <= ''' + convert(char(8),
@.dtMaxDate, 112) + ''''
IF @.timeID = 2 /* should Return records between start and end date */
PRINT 'BELOW CODE GIVES ERROR'
SET @.SQL = @.SQL + CONVERT (char(8), Orders.OrderDate, 112) >=
@.dtStartDate + ' AND '
+ CONVERT (char(8), Orders.OrderDate, 112) <= @.dtEndDate
SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
SET @.SQL = @.SQL + 'GROUP BY Orders.OrderDate'
EXEC(@.SQL)Basically, I think your dynamic SQL should look like this when done:
=====
'SELECT Orders.OrderDate, SUM(Orders.Freight) AS SumOfFreight FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE CONVERT (char(8), Orders.OrderDate, 112) >= ''' + CONVERT (CHAR(11),
@.dtStartDate) + ''' AND CONVERT (char(8), Orders.OrderDate, 112) <= ''' +
CONVERT(CHAR(11), @.dtEndDate) + ''''
=====
From your example below, it looks like the CONVERT portion needs to be
within the quotes and the @.tdStartDate and @.tdEndDates need to be appended
to the query with their values. Also, what date format are you expecting by
converting into CHAR(8)?
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Scott" <sbailey@.mileslumber.com> wrote in message
news:e3P2oYJ8FHA.2192@.TK2MSFTNGP14.phx.gbl...
> My dynamic sql correctly returns the top 10 dates with records if @.timeID
> equals 1. I need the @.timeID equals 2 part to return records between
> @.dtStartDate and @.dtEndDate. (I just set the enddate equal to maxdate to
> keep my example simple).
> In the below section where @.timeID = 2, I'm getting a syntax error. Can
> anyone help with the part below that tests IF @.timeID = 2 ? I just need
> some quote help with that part of the where clause.
> This runs on Northwind.
> CODE ****************************************
***
> declare @.SQL varchar(1000), @.typeID int, @.timeID int
> declare @.dtStartDate datetime, @.dtEndDate datetime, @.dtMaxDate datetime
> set @.typeID = 1
> SET @.timeID = 1
> set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
> set @.dtStartDate = (SELECT DATEADD(day, -20, @.dtMaxDate))
> set @.dtEndDate = @.dtMaxDate
> SET @.SQL = 'SELECT Orders.OrderDate, '
> IF @.typeID = 1
> SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
> IF @.typeID = 2
> SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
> SET @.SQL = @.SQL + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID
> WHERE '
> IF @.timeID = 1 /* Return last 10 days */
> SET @.SQL = @.SQL + ' Orders.OrderDate IN (SELECT DISTINCT TOP 10
> Orders.OrderDate
> FROM Orders WHERE Orders.OrderDate <= ''' + convert(char(8),
> @.dtMaxDate, 112) + ''''
> IF @.timeID = 2 /* should Return records between start and end date */
> PRINT 'BELOW CODE GIVES ERROR'
> SET @.SQL = @.SQL + CONVERT (char(8), Orders.OrderDate, 112) >=
> @.dtStartDate + ' AND '
> + CONVERT (char(8), Orders.OrderDate, 112) <= @.dtEndDate
> SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
> SET @.SQL = @.SQL + 'GROUP BY Orders.OrderDate'
> EXEC(@.SQL)
>
>|||Scott
I don't understand why would want to use dynamic sql but this is a fixed
code
-- PRINT 'BELOW CODE GIVES ERROR'
SET @.SQL = @.SQL + 'CONVERT (char(8), Orders.OrderDate, 112) >=''' +
convert(char(8),@.dtStartDate ,112)+ ''' AND '
+ 'CONVERT (char(8), Orders.OrderDate, 112) <='''
+convert(char(8),@.dtEndDate,112)+''''
BTW if IF @.timeID = 1, I got this error
Line 28: Incorrect syntax near '>'.
"Scott" <sbailey@.mileslumber.com> wrote in message
news:e3P2oYJ8FHA.2192@.TK2MSFTNGP14.phx.gbl...
> My dynamic sql correctly returns the top 10 dates with records if @.timeID
> equals 1. I need the @.timeID equals 2 part to return records between
> @.dtStartDate and @.dtEndDate. (I just set the enddate equal to maxdate to
> keep my example simple).
> In the below section where @.timeID = 2, I'm getting a syntax error. Can
> anyone help with the part below that tests IF @.timeID = 2 ? I just need
> some quote help with that part of the where clause.
> This runs on Northwind.
> CODE ****************************************
***
> declare @.SQL varchar(1000), @.typeID int, @.timeID int
> declare @.dtStartDate datetime, @.dtEndDate datetime, @.dtMaxDate datetime
> set @.typeID = 1
> SET @.timeID = 1
> set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
> set @.dtStartDate = (SELECT DATEADD(day, -20, @.dtMaxDate))
> set @.dtEndDate = @.dtMaxDate
> SET @.SQL = 'SELECT Orders.OrderDate, '
> IF @.typeID = 1
> SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
> IF @.typeID = 2
> SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
> SET @.SQL = @.SQL + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID
> WHERE '
> IF @.timeID = 1 /* Return last 10 days */
> SET @.SQL = @.SQL + ' Orders.OrderDate IN (SELECT DISTINCT TOP 10
> Orders.OrderDate
> FROM Orders WHERE Orders.OrderDate <= ''' + convert(char(8),
> @.dtMaxDate, 112) + ''''
> IF @.timeID = 2 /* should Return records between start and end date */
> PRINT 'BELOW CODE GIVES ERROR'
> SET @.SQL = @.SQL + CONVERT (char(8), Orders.OrderDate, 112) >=
> @.dtStartDate + ' AND '
> + CONVERT (char(8), Orders.OrderDate, 112) <= @.dtEndDate
> SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
> SET @.SQL = @.SQL + 'GROUP BY Orders.OrderDate'
> EXEC(@.SQL)
>
>|||Scott (sbailey@.mileslumber.com) writes:
> My dynamic sql correctly returns the top 10 dates with records if @.timeID
> equals 1. I need the @.timeID equals 2 part to return records between
> @.dtStartDate and @.dtEndDate. (I just set the enddate equal to maxdate to
> keep my example simple).
Weren't you the guy with time constraints? Why then are you wasting the
time with building complete SQL strings, when using sp_executesql is so
much easier?
And why do you insist on not printing out the generated SQL? You could
at least include the generated SQL in your posts, so that it's easier for
people to spot the syntax errors.

> IF @.timeID = 2 /* should Return records between start and end date */
> PRINT 'BELOW CODE GIVES ERROR'
> SET @.SQL = @.SQL + CONVERT (char(8), Orders.OrderDate, 112) >=
> @.dtStartDate + ' AND '
You have string concatanated with string, and then there is a comparison
operator in the middle of all this. That will obviously give a syntax
error.
And avoid doing things like CONVERT (char(8), Orders.OrderDate, 112) in
an WHERE clause. When you put a column in an expression, this prevents
any index from that column from being used. Thus, this can have a serious
impact on performance.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||1. I'd really appreciate it if you would expand on your "avoid doing things
like CONVERT (char(8)), Orders.OrderDate, 112)". I always have to pass dates
from ASP to my SPROCs as "11/24/2005". How or what syntax can I use on that
date format to avoid a performance hit?
2. As far as sp_executesql, I wanted to use that method, but the syntax is
more confusing that my method. If I good get some good simple examples, I'd
go that way.
3. My reason for going the way I am is if I hard coded every sql statement
with the matrix of variables I'm sending this SPROC, it would be 1,000 lines
or more. Normally they don't get this out of hand, but this one is for a
graph with lots of options.
4. I'll do better at printing the SQL from now on.
Thanks for any pointers on my date performance issue #1 above.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9718610C29F6DYazorman@.127.0.0.1...
> Scott (sbailey@.mileslumber.com) writes:
> Weren't you the guy with time constraints? Why then are you wasting the
> time with building complete SQL strings, when using sp_executesql is so
> much easier?
> And why do you insist on not printing out the generated SQL? You could
> at least include the generated SQL in your posts, so that it's easier for
> people to spot the syntax errors.
>
> You have string concatanated with string, and then there is a comparison
> operator in the middle of all this. That will obviously give a syntax
> error.
> And avoid doing things like CONVERT (char(8), Orders.OrderDate, 112) in
> an WHERE clause. When you put a column in an expression, this prevents
> any index from that column from being used. Thus, this can have a serious
> impact on performance.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the efforts.
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:enDhqcL8FHA.1140@.tk2msftngp13.phx.gbl...
> Basically, I think your dynamic SQL should look like this when done:
> =====
> 'SELECT Orders.OrderDate, SUM(Orders.Freight) AS SumOfFreight FROM
> Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
> WHERE CONVERT (char(8), Orders.OrderDate, 112) >= ''' + CONVERT (CHAR(11),
> @.dtStartDate) + ''' AND CONVERT (char(8), Orders.OrderDate, 112) <= ''' +
> CONVERT(CHAR(11), @.dtEndDate) + ''''
> =====
> From your example below, it looks like the CONVERT portion needs to be
> within the quotes and the @.tdStartDate and @.tdEndDates need to be appended
> to the query with their values. Also, what date format are you expecting
> by converting into CHAR(8)?
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:e3P2oYJ8FHA.2192@.TK2MSFTNGP14.phx.gbl...
>|||Scott (sbailey@.mileslumber.com) writes:
> 1. I'd really appreciate it if you would expand on your "avoid doing
> things like CONVERT (char(8)), Orders.OrderDate, 112)". I always have to
> pass dates from ASP to my SPROCs as "11/24/2005". How or what syntax can
> I use on that date format to avoid a performance hit?
No, you don't have that pass dates that way from ASP. Even more you
shouldn't. That is not even a date as far as I am concerned.
The way to pass data from a client is to use parameters, and let the
the client interpret the date according to the regional setting. The is
then passed to SQL Server as a binary value, as dates are binary values
in SQL Server. (As they are in Windows, by the way.)
I don't do ASP, but in plain Visual Basic it looks something like:
cmd.CommandType = adCmdStoredProcedure
cmd.CommandText = "dbo.some_sp"
cmd.CreateParameter "@.param1", adDBTimeStamp, adParamInput, , DateFld
And obviously you are doing something like that already, as the date
variables in your example were declared as datetime, not as character.

> 2. As far as sp_executesql, I wanted to use that method, but the syntax
> is more confusing that my method. If I good get some good simple
> examples, I'd go that way.
SELECT @.sql = N'SELECT ... FROM tbl WHERE col = @.par1, col = @.par2',
@.params = N'@.par1 int, @.par2 datetime'
EXEC sp_executesql @.sql, @.params, @.par1 = 12, @.par2 = @.dtEndTime
How is this more confusing that trying to get quotes and date formats
correct, something you appear to have big problems with.
As for writing dynamic searches, have you looked at my web site where
I have a longer article on the topic, with both static and dynamic methods?
http://www.sommarskog.se/dyn-search.html. There are also tips for people
that use EXEC().
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The part of sp_executesql that gets confusing is the N' syntax. Does N' mean
it's a text type?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9718E396131E5Yazorman@.127.0.0.1...
> Scott (sbailey@.mileslumber.com) writes:
> No, you don't have that pass dates that way from ASP. Even more you
> shouldn't. That is not even a date as far as I am concerned.
> The way to pass data from a client is to use parameters, and let the
> the client interpret the date according to the regional setting. The is
> then passed to SQL Server as a binary value, as dates are binary values
> in SQL Server. (As they are in Windows, by the way.)
> I don't do ASP, but in plain Visual Basic it looks something like:
> cmd.CommandType = adCmdStoredProcedure
> cmd.CommandText = "dbo.some_sp"
> cmd.CreateParameter "@.param1", adDBTimeStamp, adParamInput, , DateFld
> And obviously you are doing something like that already, as the date
> variables in your example were declared as datetime, not as character.
>
> SELECT @.sql = N'SELECT ... FROM tbl WHERE col = @.par1, col = @.par2',
> @.params = N'@.par1 int, @.par2 datetime'
> EXEC sp_executesql @.sql, @.params, @.par1 = 12, @.par2 = @.dtEndTime
> How is this more confusing that trying to get quotes and date formats
> correct, something you appear to have big problems with.
> As for writing dynamic searches, have you looked at my web site where
> I have a longer article on the topic, with both static and dynamic
> methods?
> http://www.sommarskog.se/dyn-search.html. There are also tips for people
> that use EXEC().
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||on your site in search_orders_1, for example, how does SQL know what
@.xorderid is when you only declare @.orderid at the beginning of the SPROC? I
see @.xorderid listed in the @.paramlist after you use it as a WHERE criteria,
but how does SQL know what @.xorderid is?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9718E396131E5Yazorman@.127.0.0.1...
> Scott (sbailey@.mileslumber.com) writes:
> No, you don't have that pass dates that way from ASP. Even more you
> shouldn't. That is not even a date as far as I am concerned.
> The way to pass data from a client is to use parameters, and let the
> the client interpret the date according to the regional setting. The is
> then passed to SQL Server as a binary value, as dates are binary values
> in SQL Server. (As they are in Windows, by the way.)
> I don't do ASP, but in plain Visual Basic it looks something like:
> cmd.CommandType = adCmdStoredProcedure
> cmd.CommandText = "dbo.some_sp"
> cmd.CreateParameter "@.param1", adDBTimeStamp, adParamInput, , DateFld
> And obviously you are doing something like that already, as the date
> variables in your example were declared as datetime, not as character.
>
> SELECT @.sql = N'SELECT ... FROM tbl WHERE col = @.par1, col = @.par2',
> @.params = N'@.par1 int, @.par2 datetime'
> EXEC sp_executesql @.sql, @.params, @.par1 = 12, @.par2 = @.dtEndTime
> How is this more confusing that trying to get quotes and date formats
> correct, something you appear to have big problems with.
> As for writing dynamic searches, have you looked at my web site where
> I have a longer article on the topic, with both static and dynamic
> methods?
> http://www.sommarskog.se/dyn-search.html. There are also tips for people
> that use EXEC().
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||> The part of sp_executesql that gets confusing is the N' syntax. Does N' me
an it's a text type?
It mean it is Unicode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scott" <sbailey@.mileslumber.com> wrote in message news:ut24UCY8FHA.3952@.TK2MSFTNGP12.phx.g
bl...
> The part of sp_executesql that gets confusing is the N' syntax. Does N' me
an it's a text type?
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9718E396131E5Yazorman@.127.0.0.1...
>