June 20, 2010 at 5:32 am
declare @dynasql nvarchar(500)
declare @dynawhr nvarchar(500)
declare @dynacon int
declare @msg_userid varchar(25)
declare @msg_status int -- from dropdown: new/read/replied
declare @msg_frdate varchar(10) -- format is 'mm/dd/yyyy'
declare @msg_todate varchar(10) -- format is 'mm/dd/yyyy'
declare @msg_fromyy int
declare @msg_frommm int
declare @msg_fromdd int
declare @msg_uptoyy int
declare @msg_uptomm int
declare @msg_uptodd int
-- parameters to sp_executesql
set @msg_userid = null
set @msg_status = null
set @msg_fromyy = null
set @msg_frommm = null
set @msg_fromdd = null
set @msg_uptoyy = null
set @msg_uptomm = null
set @msg_uptodd = null
-- parameters to the procedure
-- these are the parameters im working with
-- try several combinations, some works, some do not
-- im still debugging
set @msg_userid = '10PPIPHUT2A'
set @msg_status = 1
set @msg_frdate = '04/01/2010' -- format is 'mm/dd/yyyy'
set @msg_todate = '04/13/2010' -- format is 'mm/dd/yyyy'
set @dynacon = 0
set @dynasql = 'select email_id, email_dt, sender, receiver, branch_id, subject,
status, msg, attachment_flag, attached_file, status2,
receiver_deleted, sender_deleted, branch_id_receiver,
email_type
from tbl_email '
set @dynawhr = null
if (@msg_userid is not null)
begin
set @dynawhr = 'where (sender = @msg_userid) '
set @dynacon = 10
end
if (@msg_status is not null) and (@dynawhr is not null)
begin
set @dynawhr = @dynawhr + 'and (status = @msg_status) '
set @dynacon = @dynacon + 1
end
else
begin
if (@msg_status is not null)
begin
set @dynawhr = 'where (status = @msg_status) '
set @dynacon = 20
end
end
if (@msg_frdate is not null) and (@dynawhr is not null)
begin
set @msg_fromyy = datepart(yy, convert(datetime, @msg_frdate))
set @msg_frommm = datepart(mm, convert(datetime, @msg_frdate))
set @msg_fromdd = datepart(dd, convert(datetime, @msg_frdate))
set @dynawhr = @dynawhr + 'and (datepart(yy, email_dt) >= @msg_fromyy) and (datepart(mm, email_dt) >= @msg_frommm) and (datepart(dd, email_dt) >= @msg_fromdd) '
set @dynacon = @dynacon + 2
end
else
begin
if (@msg_frdate is not null)
begin
set @msg_fromyy = datepart(yy, convert(datetime, @msg_frdate))
set @msg_frommm = datepart(mm, convert(datetime, @msg_frdate))
set @msg_fromdd = datepart(dd, convert(datetime, @msg_frdate))
set @dynawhr = 'where (datepart(yy, email_dt) >= @msg_fromyy) and (datepart(mm, email_dt) >= @msg_frommm) and (datepart(dd, email_dt) >= @msg_fromdd) '
set @dynacon = 30
end
end
if (@msg_todate is not null) and (@dynawhr is not null)
begin
set @msg_uptoyy = datepart(yy, convert(datetime, @msg_todate))
set @msg_uptomm = datepart(mm, convert(datetime, @msg_todate))
set @msg_uptodd = datepart(dd, convert(datetime, @msg_todate))
set @dynawhr = @dynawhr + 'and (datepart(yy, email_dt) <= @msg_uptoyy) and (datepart(mm, email_dt) <= @msg_uptomm) and (datepart(dd, email_dt) <= @msg_uptodd) '
set @dynacon = @dynacon + 4
end
else
begin
if (@msg_todate is not null)
begin
set @msg_uptoyy = datepart(yy, convert(datetime, @msg_todate))
set @msg_uptomm = datepart(mm, convert(datetime, @msg_todate))
set @msg_uptodd = datepart(dd, convert(datetime, @msg_todate))
set @dynawhr = 'where (datepart(yy, email_dt) <= @msg_uptoyy) and (datepart(mm, email_dt) <= @msg_uptomm) and (datepart(dd, email_dt) <= @msg_uptodd) '
set @dynacon = 40
end
end
print 'AX ==> ' + @dynasql + @dynawhr
if @dynacon = 0
exec sp_executesql @dynasql
else
begin
set @dynasql = @dynasql + @dynawhr
if @dynacon < 30
begin
if @dynacon < 20
begin
if @dynacon = 10
exec sp_executesql @dynasql, N'@msg_userid varchar(25)', @msg_userid
else
if @dynacon = 11
exec sp_executesql @dynasql, N'@msg_userid varchar(25), @msg_status int', @msg_userid, @msg_status
else
if @dynacon = 13
exec sp_executesql @dynasql, N'@msg_userid varchar(25), @msg_status int, @msg_fromyy int, @msg_frommm int, @msg_fromdd int', @msg_userid, @msg_status, @msg_fromyy, @msg_frommm, @msg_fromdd
else
if @dynacon = 17
exec sp_executesql @dynasql, N'@msg_userid varchar(25), @msg_status int, @msg_fromyy int, @msg_frommm int, @msg_fromdd int, @msg_uptoyy int, @msg_uptomm int, @msg_uptodd int', @msg_userid, @msg_status, @msg_fromyy, @msg_frommm, @msg_fromdd, @msg_uptoyy, @msg_uptomm, @msg_uptodd
else
if @dynacon = 12
exec sp_executesql @dynasql, N'@msg_userid varchar(25), @msg_fromyy int, @msg_frommm int, @msg_fromdd int', @msg_userid, @msg_fromyy, @msg_frommm, @msg_fromdd
else
if @dynacon = 16
exec sp_executesql @dynasql, N'@msg_userid varchar(25), @msg_fromyy int, @msg_frommm int, @msg_fromdd int, @msg_uptoyy int, @msg_uptomm int, @msg_uptodd int', @msg_userid, @msg_fromyy, @msg_frommm, @msg_fromdd, @msg_uptoyy, @msg_uptomm, @msg_uptodd
else
if @dynacon = 14
exec sp_executesql @dynasql, N'@msg_userid varchar(25), @msg_uptoyy int, @msg_uptomm int, @msg_uptodd int', @msg_userid, @msg_uptoyy, @msg_uptomm, @msg_uptodd
end
else
begin
if @dynacon = 20
exec sp_executesql @dynasql, N'@msg_status int', @msg_status
else
if @dynacon = 22
exec sp_executesql @dynasql, N'@msg_status int, @msg_fromyy int, @msg_frommm int, @msg_fromdd int', @msg_status, @msg_fromyy, @msg_frommm, @msg_fromdd
else
if @dynacon = 26
exec sp_executesql @dynasql, N'@msg_status int, @msg_fromyy int, @msg_frommm int, @msg_fromdd int, @msg_uptoyy int, @msg_uptomm int, @msg_uptodd int', @msg_status, @msg_fromyy, @msg_frommm, @msg_fromdd, @msg_uptoyy, @msg_uptomm, @msg_uptodd
else
if @dynacon = 24
exec sp_executesql @dynasql, N'@msg_status int, @msg_uptoyy int, @msg_uptomm int, @msg_uptodd int', @msg_status, @msg_uptoyy, @msg_uptomm, @msg_uptodd
end
end
else
begin
if @dynacon < 40
begin
if @dynacon = 30
exec sp_executesql @dynasql, N'@msg_fromyy int, @msg_frommm int, @msg_fromdd int', @msg_fromyy, @msg_frommm, @msg_fromdd
else
if @dynacon = 34
exec sp_executesql @dynasql, N'@msg_fromyy int, @msg_frommm int, @msg_fromdd int, @msg_uptoyy int, @msg_uptomm int, @msg_uptodd int', @msg_fromyy, @msg_frommm, @msg_fromdd, @msg_uptoyy, @msg_uptomm, @msg_uptodd
end
else
if @dynacon = 40
exec sp_executesql @dynasql, N'@msg_uptoyy int, @msg_uptomm int, @msg_uptodd int', @msg_uptoyy, @msg_uptomm, @msg_uptodd
end
end
June 21, 2010 at 2:12 am
Is there a question here?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 21, 2010 at 2:27 am
I see something on the "description" field, but it doesn't get displayed in the thread view. It gets truncated on the forum view, but I see the first few words of a question...
-- Gianluca Sartori
June 21, 2010 at 2:27 am
Duplicate post, removed.
-- Gianluca Sartori
June 21, 2010 at 6:39 am
1) I too am confused about the actual 'question' here.
2) I don't think you want all those sp_executesqls. Probably just want a single concatenated string and use exec (@sql).
3) avoid the datepart stuff like you have. make it a SARGable WHERE with direct date comparisons. This is almost always possible.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 21, 2010 at 12:27 pm
Your current date comparisons are faulty. The month and day do not have to exceed the from date for the date to be valid for selection.
For example:
from date = 12/31/2009
to date = 01/31/2010
This should include Dec 31 and every day in Jan 2010, but I don't think your current date comparisons will do that.
Scott Pletcher, SQL Server MVP 2008-2010
June 21, 2010 at 10:42 pm
hi there.. thank you for taking the time to analyze my script
1) I too am confused about the actual 'question' here.
=> see, what i am trying to resolve here is the error that is generated whenever, i use the @msg_todate parameter in the dynamic query .. it says syntax error .. have you tried to run the script?..
2) I don't think you want all those sp_executesqls. Probably just want a single concatenated string and use exec (@sql).
=> i see your point, however, to convert the statements into an Exec (@dynasql), i would have to would have to work out the qoutes, and because of that, sp_executesql is a better option..
yes i do admit that those sp_executesql lines are a bit confusing, is there a way to compress it into one sp_executesql call, what about the N'@varlist...' is it ok with mssql to indicate all combinations and specify only those which are used?.. t
3) avoid the datepart stuff like you have. make it a SARGable WHERE with direct date comparisons. This is almost always possible.
=> will try your suggestion, have to admit that the date comparisons are a bit wieldy
June 21, 2010 at 11:50 pm
gorkchow (6/21/2010)
is it ok with mssql to indicate all combinations and specify only those which are used?..
It's fine to declare and pass parameters to sp_executesql that are never used.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 22, 2010 at 10:15 am
you will be much better off by composing those statements on the "client" instead of on the "server"
* Noel
June 22, 2010 at 10:26 am
To avoid having to check for a previous WHERE for every condition, you can use the old trick of coding the original statement with:
WHERE 1 = 1
so that all conditions you add can just be "and ...condition...".
Also, I suggest you just put in the actual value rather than a variable for numeric and date columns. Yes, that means the query won't reuse a previous plan, but that could be a good thing, since specific values may help SQL form a better plan anyway, and the prior plan may be wrong for the current values.
For the varchar data, you may need to use a variable to avoid SQL injection attacks, depending on how the varchar data is being fed.
Scott Pletcher, SQL Server MVP 2008-2010
June 22, 2010 at 10:29 am
hi sscoach, have successfully removed all those if-statements in my sql script
and everything is ok - compile-wise that is..
so now, my dynamic sql script is like this
declare @dynasql nvarchar(500)
declare @dynawhr nvarchar(500)
declare @msg_userid varchar(25)
declare @msg_status int -- from dropdown: new/read/replied
declare @msg_frdate varchar(10) -- format is 'yyyy-mm-dd'
declare @msg_todate varchar(10) -- format is 'yyyy-mm-dd'
-- parameters to sp_executesql
set @msg_userid = null
set @msg_status = null
set @msg_frdate = null
set @msg_todate = null
-- parameters to the procedure
-- these are the parameters im working with
-- try several combinations, some works, some do not
-- im still debugging
set @msg_userid = '10PPIPHUT2A'
--set @msg_status = 0
set @msg_frdate = '2010-04-10' -- format is 'yyyy-mm-dd'
set @msg_todate = '2010-04-13' -- format is 'yyyy-mm-dd'
set @dynasql = 'select email_id, email_dt, sender, receiver, branch_id, subject, status, msg, attachment_flag, attached_file, status2, receiver_deleted, sender_deleted, branch_id_receiver, email_type from tbl_email '
set @dynawhr = null
if (@msg_userid is not null)
set @dynawhr = 'where (sender like @msg_userid) '
if (@msg_status is not null) and (@dynawhr is not null)
set @dynawhr = @dynawhr + 'and (status = @msg_status) '
else
if (@msg_status is not null)
set @dynawhr = 'where (status = @msg_status) '
if (@msg_frdate is not null) and (@dynawhr is not null)
set @dynawhr = @dynawhr + 'and (convert(varchar(8), email_dt, 112) >= @msg_frdate) '
else
if (@msg_frdate is not null)
set @dynawhr = 'where (convert(varchar(8), email_dt, 112) >= @msg_frdate) '
if (@msg_todate is not null) and (@dynawhr is not null)
set @dynawhr = @dynawhr + 'and (convert(varchar(8), email_dt, 112) <= @msg_todate) '
else
if (@msg_todate is not null)
set @dynawhr = 'where (convert(varchar(8), email_dt, 112) <= @msg_todate)'
if @dynawhr is not null
set @dynasql = @dynasql + @dynawhr
print 'Generated SQL ==> ' + @dynasql
print 'Userid => ' + @msg_userid
print 'Status => ' + convert(varchar(10),@msg_status)
print 'FrDate => ' + @msg_frdate
print 'ToDate => ' + @msg_todate
exec sp_executesql @dynasql, N'@msg_userid varchar(25), @msg_status int, @msg_frdate varchar(10), @msg_todate varchar(10)', @msg_userid, @msg_status, @msg_frdate, @msg_todate
now on to my prob: whenever the date vars @msg_frdate and/or @msg_todate is used, it does not return any row but per normal select, date filtering should return some rows because the table has rows that will satisfy the following criteria
=> email_dt >= @msg_frdate
=> email_dt <= @msg_todate
=> email_dt >= @msg_frdate and email_dt <= @msg_todate
but when i do this
where (datepart("yyyy", email_dt) >= @msg_fromyy) and (datepart("mm", email_dt) >= @msg_frommm) and (datepart("dd", email_dt) >= @msg_fromdd)
i get some rows.. anyone got some suggestions? i've been reading on how to get the date-part only of a datetime field column because email_dt contains a timestamp, and tried several suggestions but still no luck
thanks.. =p
June 22, 2010 at 10:52 am
scott.pletcher (6/22/2010)
Also, I suggest you just put in the actual value rather than a variable for numeric and date columns. Yes, that means the query won't reuse a previous plan, but that could be a good thing, since specific values may help SQL form a better plan anyway, and the prior plan may be wrong for the current values.
Scott, I must confess I'm a bit confused.
Do you mean that fixed literals force a recompile, while variables force a plan reuse?
I was under a different impression, but I could be dead wrong.
Maybe I overlooked something in your fellow MVP Erland Sommarskog's article on dynamic search conditions. Can you suggest any pointers?
-- Gianluca Sartori
June 22, 2010 at 12:56 pm
Do you mean that fixed literals force a recompile, while variables force a plan reuse?
Only specifying WITH RECOMPILE or equivalent can force a recompile. AFAIK, there is nothing you can do to force a plan reuse (with the exception maybe now of plan guides).
However, as I understand it, for a single statment, the entire string must match exactly for SQL to reuse the plan; even mixed case in a keyword or extra spacing will force a new plan to be created.
If you use sp_executesql with parameters for all input values, you generally can get plan reuse. The text of the query is, after all, identical every time. So, assuming you include schema names for all tables and follow the other rules, you can re-use the plan. That's the idea behing using sp_executesql :-).
But for complex queries that's a mixed blessing at best. In such cases, the cost of creating a plan can be much less than the overhead of unnecessary I/O from a bad plan. One reallly bad plan could really slam the server (for example, using an index to look up thousands of matches because the first execution that created the plan happened to have only match and so used an index).
MS recommends "CREATE PROCEDURE ... WITH RECOMPILE" be used to mark stored procedures that are called with widely varying parameters, and for which best query plans are highly dependent on parameter values supplied during calls.
If you're going to use WITH RECOMPILE on a proc or statement anyway, you might as well substitute the actual values rather than use a variable(s). When the entire string is static SQL, SQL can up with its very best plan for those specific values.
Scott Pletcher, SQL Server MVP 2008-2010
June 22, 2010 at 1:18 pm
Of course the real answer, as always, is "it depends".
If the parameters will match only a single value on the db, it makes sense to use the same plan for every request with the same parameters: the best way to match a single value is not going to change.
Or say the first one or two parameters only are single-matching, but that 75%+ of the queries use just one or two of those. Again, it makes good sense to use sp_executesql and parameterize those situations.
But if the parameters can match from one to, say, five thousand rows, and/or if each request usually uses several different parameters, you may be better off just using static SQL and letting SQL generate a new plan every time.
Scott Pletcher, SQL Server MVP 2008-2010
June 23, 2010 at 1:17 am
gorkchow (6/22/2010)
now on to my prob: whenever the date vars @msg_frdate and/or @msg_todate is used, it does not return any row but per normal select, date filtering should return some rows because the table has rows that will satisfy the following criteria=> email_dt >= @msg_frdate
=> email_dt <= @msg_todate
=> email_dt >= @msg_frdate and email_dt <= @msg_todate
but when i do this
where (datepart("yyyy", email_dt) >= @msg_fromyy) and (datepart("mm", email_dt) >= @msg_frommm) and (datepart("dd", email_dt) >= @msg_fromdd)
i get some rows..
This is because of the date conversion: convert(varchar(8),getdate(),112) will return a string in the format YYYYMMDD and you tried to compare it to a date string formatted like YYYY-MM-DD.
Try this:
declare @dynasql nvarchar(500)
declare @msg_userid varchar(25)
declare @msg_status int -- from dropdown: new/read/replied
declare @msg_frdate varchar(10) -- format is 'yyyy-mm-dd'
declare @msg_todate varchar(10) -- format is 'yyyy-mm-dd'
-- parameters to sp_executesql
set @msg_userid = null
set @msg_status = null
set @msg_frdate = null
set @msg_todate = null
-- parameters to the procedure
-- these are the parameters im working with
-- try several combinations, some works, some do not
-- im still debugging
set @msg_userid = '10PPIPHUT2A'
--set @msg_status = 0
set @msg_frdate = '2010-04-10' -- format is 'yyyy-mm-dd'
set @msg_todate = '2010-04-13' -- format is 'yyyy-mm-dd'
set @dynasql = '
select email_id, email_dt, sender, receiver, branch_id, subject,
status, msg, attachment_flag, attached_file, status2,
receiver_deleted, sender_deleted, branch_id_receiver, email_type
from tbl_email
where 1 = 1 '
if (@msg_userid is not null)
set @dynasql = @dynasql + ' and (sender like @msg_userid) '
if (@msg_status is not null)
set @dynasql = @dynasql + ' and (status = @msg_status) '
if (@msg_frdate is not null)
set @dynasql = @dynasql + ' and (convert(char(10), email_dt, 121) >= @msg_frdate) '
if (@msg_todate is not null)
set @dynasql = @dynasql + ' and (convert(char(10), email_dt, 121) <= @msg_todate) '
print 'Generated SQL ==> ' + @dynasql
print 'Userid => ' + @msg_userid
print 'Status => ' + convert(varchar(10),@msg_status)
print 'FrDate => ' + @msg_frdate
print 'ToDate => ' + @msg_todate
exec sp_executesql @dynasql,
N'@msg_userid varchar(25), @msg_status int, @msg_frdate char(10), @msg_todate char(10)',
@msg_userid, @msg_status, @msg_frdate, @msg_todate
-- Gianluca Sartori
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply