Select records in a table between a time range

Title:
Select records in a table depending on the time range for the dates
Question: Hi,
      I am using a dynamic SQL query which displays transactions between certain dates. But now I am asked to implement within a certain time range too. But if i add a start time and end time and concatenate the date and  time strings from the web app and pass it as the @fromdate and @todate parameters in the code below, it gives results in such a way that the starting from the start date till end time on the end date.
But i want to modify it in such a way that if i enter my dates as 1/12/2008 - 1/13/2008
and times as 12:00 am - 2:00 am...then it should display results only for those two hours for both the days, rather than displaying from 12:00 am on 12th to 2:00 am on 13th.
How can i modify the query or the c# code. Please help me.
'
		select 
			*
		from  
			'+table_name+'
		where
			td_datetimestamp between coalesce(nullif('''+isnull(convert(varchar(23),@fromDate,121),'''')+''',''''),td_datetimestamp) and coalesce(nullif('''+isnull(convert(varchar(23),@toDate,121),'''')+''',''''),td_datetimestamp)
		union'

Open in new window

WebRulesAsked:
Who is Participating?
 
Philippe DamervalConnect With a Mentor Senior Analyst ProgrammerCommented:
For instance, if you wanted to look in the mydate field from @fromdate to @todate for records having their timestamp between @starttime and @endtime, you would write:

WHERE mydate BETWEEN @fromdate AND @todate
AND
CONVERT(varchar, CONVERT(DATETIME, CONVERT(FLOAT, mydate) - FLOOR(CONVERT(FLOAT, mydate))), 108)
BETWEEN
CONVERT(varchar, CONVERT(DATETIME, CONVERT(FLOAT, @starttime) - FLOOR(CONVERT(FLOAT, @starttime))), 108)
AND
CONVERT(varchar, CONVERT(DATETIME, CONVERT(FLOAT, @endtime) - FLOOR(CONVERT(FLOAT, @endtime))), 108)


0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
Its not very complicated but SQL server makes it hard to find one true path to getting what you want done.
To achieve what you need done you would need to split your criteria into two: one part testing for a day range, another testing for an hour range.
Taking advantage of the sort order of strings that leads to "1" being lesser than "2", and based on the knowledge that SQL server date and time values are really floats where the integer part is the number of days elapsed since a beginning date (01/01/1900) and the fractional part is the time, expressed in number of hours elapsed since midnight and divided by 24, we can do conversions to get to the values we want. For instance, this formula extracts the time from any date as a string:


select CONVERT(varchar, CONVERT(DATETIME, CONVERT(FLOAT, GETDATE()) - FLOOR(CONVERT(FLOAT, getDate()))), 108)

From there you can compare it to another time converted to a varchar.

You can quote hard-coded dates and times by using the "dd-MON-yyyy hh:mm:ssam/pm" format, for instance "01-Jan-1900 01:01:01AM)

CONVERT is your friend. Read all about it here:
http://msdn.microsoft.com/en-us/library/ms187928.aspx

HTH

Philippe
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
WebRulesAuthor Commented:
@ radcaescar : hi, the example you mentioned is just for one day. but what if we have to get for two days within the same time range like my example mentioned in the above question.
0
 
WebRulesAuthor Commented:
@damerval: i haven't really understood your solution above.
0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
Bearing in mind that this is just one way of doing it. The "right" way or most elegant way depends on your specific application.
0
 
WebRulesAuthor Commented:
oh, ok ..so you say i need to pass the time as different parameters rather than with date? right?
0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
Right, split the criteria into 1) select date range and 2) select time of day range within those days). To do the time of day selection you need to use CONVERT in some way, like what I have done there (I went for a string comparison).
0
 
WebRulesAuthor Commented:
yeah, right now i have start date. end date, start time, end time. but my sql table has just one column td_datetimestamp...which if type 'datetime'.
0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
That's fine, replace the name "mydate" in my example above with your field td_datetimestamp.
0
 
WebRulesAuthor Commented:
ok will try and let you know.
0
 
WebRulesAuthor Commented:
hi, what should be the datatype pf starttime and endtime paramters?
0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
They should be datetime type parameters. You can specify them in SQL server using the "HH:MM:SSAM/PM" format, for instance: '03:30:00PM". You do not need to specify a date as the expression I've copied converts any datetime value to a comparison value that you can use to delimit time ranges within the day.
0
 
WebRulesAuthor Commented:
i am getting errors. It says..declare scalar variable @fromDate...but i already declared it.

this is the code i am using. and all the four paramters are of type datetime.
'
		select 
			*
		from  
			'+table_name+'
		where
			td_datetimestamp between @fromDate and @toDate
			AND
		CONVERT(varchar, CONVERT(DATETIME, CONVERT(FLOAT, td_datetimestamp) - FLOOR(CONVERT(FLOAT, td_datetimestamp))), 108)
		BETWEEN
		CONVERT(varchar, CONVERT(DATETIME, CONVERT(FLOAT, @starttime) - FLOOR(CONVERT(FLOAT, @starttime))), 108)
		AND
		CONVERT(varchar, CONVERT(DATETIME, CONVERT(FLOAT, @endtime) - FLOOR(CONVERT(FLOAT, @endtime))), 108)
		union'

Open in new window

0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
Where did you declare the variables? the "@" sign precedes a SQL server variable name. You appear to be building a SQL server statement from .Net code (or similar). Perhaps it would be easiest to output the values you need so that they become constants in the SQL statement, perhaps like this:

select
                  *
            from  
                  '+table_name+'
            where
                  td_datetimestamp between '+myFromDate.ToString()+' AND '+myToDate.ToString()+'

... etc , with similar things for each time a value is needed in the SQL statement. Without knowing what your environment/programming language is or your variable names it's hard to recommend an exact expression. Do you understand what I'm trying to convey?
0
 
WebRulesAuthor Commented:
I have the variables declared as:

@fromDate DateTime = null ,
      @toDate DateTime = null ,
      @starttime datetime = null,
      @endtime datetime = null,

in the stored procedure. and i am executing my stored procedure in sql server and testing it and i am getting that error.
0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
Can you please send the exhaustive code for your stored procedure?
Thanks
0
 
WebRulesAuthor Commented:
exhaustive code?
0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
:) I mean the whole code for your stored procedure, including declarations.
0
 
WebRulesAuthor Commented:
The code is huge. I copied below. The part which we are working on, is at the beginning.
ALTER PROCEDURE [dbo].[SP_Investigations1]
	@id nvarchar(36),
	@StoreName nvarchar(155) = null ,
	@Criteria1 nvarchar(155) = null ,
	@Criteria2 nvarchar(155) = null ,
	@fromDate DateTime = null ,
	@toDate DateTime = null ,
	@starttime datetime = null,
	@endtime datetime = null,
	@reg nvarchar(5) = null ,
	@cashier nvarchar(50) = null ,
	@type nvarchar(50) = null ,
	@evenodd int = 0,
	@cat1 int = 0,
	@cat2 int = 0,
	@exp nvarchar(10) = null ,
	@value nvarchar(50) = null,
	@exp1 nvarchar(10) = null ,
	@value1 nvarchar(50) = null,
	@qty1 nvarchar(50) = null,
	@qty2 nvarchar(50) = null,
	@op_and_or int
	
AS
BEGIN
      
DECLARE 
	@tablename nvarchar(max),
	@sql1 nvarchar(max),
	@sql2 nvarchar(max),
	@sql3 nvarchar(max),
	@ParamDefinition nvarchar(500),
	@ParamDefinition1 nvarchar(500),
	@ParamDefinition2 nvarchar(500),
	@months_list varchar(255),
	@intermediateDate as DateTime

	
if(@StoreName='')
set @StoreName=null;
if(@Criteria1='')
set @Criteria1=null;
if(@Criteria2='')
set @Criteria2=null;
if(@value='')
set @value=null;
if(@value1='')
set @value1=null;
if(@exp='')
set @exp=null;
if(@exp1='')
set @exp1=null;
if(@qty1='')
set @qty1=null;
if(@qty2='')
set @qty2=null;
if(@reg='')
set @reg=null;
if(@cashier='--Select--')
set @cashier=null;
if(@cashier = '')
set @cashier = null;
if(@cat1='0')
set @cat1=null;
if(@cat2='0')
set @cat2=null;
if(@type='')
set @type=null;
if(@evenodd='0')
set @evenodd=null;
if(@type='--Select--')
set @type=null;


-- build a comma sepparated month names list
select 
	@intermediateDate=@fromDate,
	@months_list=','
while 
	 @intermediateDate<@toDate or month(@intermediateDate)=month(@toDate)
begin
	select 
		 @months_list=isnull(@months_list,'')+REPLACE(convert(varchar(7),@intermediateDate, 121),'-','')+',',
		@intermediateDate=dateadd(month,1,@intermediateDate)
end
	
-- /*
print 'From: '+convert(varchar(23), @fromDate, 121)+';
To  : '+convert(varchar(23), @toDate, 121)+';
Month list: '+@months_list+';
GUID: '+@id
--*/

-- build a sub query that will have all the rows from the tables for the given store GUID and for the months list built above
select 
@tablename=isnull(@tablename,'')+
'
		select 
			*
		from  
			'+table_name+'
		where
			td_datetimestamp between @fromDate and @toDate
			AND
		CONVERT(varchar, CONVERT(DATETIME, CONVERT(FLOAT, td_datetimestamp) - FLOOR(CONVERT(FLOAT, td_datetimestamp))), 108)
		BETWEEN
		CONVERT(varchar, CONVERT(DATETIME, CONVERT(FLOAT, @starttime) - FLOOR(CONVERT(FLOAT, @starttime))), 108)
		AND
		CONVERT(varchar, CONVERT(DATETIME, CONVERT(FLOAT, @endtime) - FLOOR(CONVERT(FLOAT, @endtime))), 108)
		union'
from 
	(select distinct table_name from information_schema.columns where @months_list like '%,'+substring(table_name, 17, 6)+',%' and replace(table_name,'_','*') like '%'+replace(@id,'-','*')) tbls 

-- prepare a subquery from the union of the tables above to be used in the big query
select 
	@tablename=
'(
		'+replace(@tablename+'#@$','union#@$','')+'
	) sp'

 --print @tablename	-- this is for test; uncomment to chech the built sting
                    
	set @sql1= N'
select TOP 1000
	tt.sale_type_name as type1,
	td_ref as ref1,
	td_device_data as data1,
	td_amount as amount,
	td_user_name as name,
	td_store_guid as guid,
	td_receipt_id as id1,
	td_department as dept,
	td_item_id as code,
	td_datetimestamp as date, 
	td_device_number as register,
	td_item_description as descr,
	td_store_guid as StoreId,
	td_quantity as qnty 
from
	' + @tablename +'
	join TransactionSaleTypes tt 
		on tt.sale_type_value = convert(nvarchar(20),td_record_type)
where 
	((
		(td_device_data like ''%'' + coalesce(@Criteria1,td_device_data) + ''%'' AND coalesce(@cat1,''1'')=''1'') 
		OR 
		(td_item_id like coalesce(@Criteria1,td_item_id) + ''%'' AND coalesce(@cat1,''2'')=''2'') 
		OR 
		(td_department like coalesce(@Criteria1,td_department) + ''%'' AND coalesce(@cat1,''3'')=''3'')
	) 
	or
	(
		(td_device_data like ''%'' + coalesce(@Criteria2,td_device_data) + ''%'' AND coalesce(@cat2,''1'')=''1'') 
		OR 
		(td_item_id like coalesce(@Criteria2,td_item_id) + ''%'' AND coalesce(@cat2,''2'')=''2'') 
		OR 
		(td_department like coalesce(@Criteria2,td_department) + ''%'' AND coalesce(@cat2,''3'')=''3'')
	)) 
	and
	(tt.sale_type_name like coalesce(@type, tt.sale_type_name) + ''%''
	and td_user_name like coalesce(@cashier, td_user_name) + ''%''
	and 
	(
'+
case 
	when isnull(@value,'')<>'' and isnull(@exp,'')<>'' then 
'
	(
		(td_amount > CONVERT(money,@value) AND @exp=''>'' AND coalesce(@evenodd,''1'')=''1'') 
		OR (td_amount < CONVERT(money,@value) AND @exp=''<'' AND coalesce(@evenodd,''1'')=''1'')
		OR (td_amount >= CONVERT(money,@value) AND @exp=''>='' AND coalesce(@evenodd,''1'')=''1'') 
		OR (td_amount <= CONVERT(money,@value) AND @exp=''<='' AND coalesce(@evenodd,''1'')=''1'')
		OR (td_amount = CONVERT(money,@value) AND @exp=''='' AND coalesce(@evenodd,''1'')=''1'') 
		OR (td_amount between CONVERT(money,@value) AND CONVERT(money,@value1) AND @exp=''between'' AND coalesce(@evenodd,''1'')=''1'')
	)
	OR
	(
		((RIGHT(td_amount,2) = ''00'') AND td_amount > CONVERT(money,@value) AND @exp=''>'' AND coalesce(@evenodd,''1'')=''2'') 
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount < CONVERT(money,@value) AND @exp=''<'' AND coalesce(@evenodd,''1'')=''2'')
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount >= CONVERT(money,@value) AND @exp=''>='' AND coalesce(@evenodd,''1'')=''2'') 
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount <= CONVERT(money,@value) AND @exp=''<='' AND coalesce(@evenodd,''1'')=''2'')
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount = CONVERT(money,@value) AND @exp=''='' AND coalesce(@evenodd,''1'')=''2'') 
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount between CONVERT(money,@value) AND CONVERT(money,@value1) AND @exp=''between'' AND coalesce(@evenodd,''1'')=''2'')
	)
'
	else '1=1'
end
+
'
	)
'
+
case 
	when isnull(@qty1,'')<>'' and  isnull(@exp1,'')<>'' then
'	
	and 
	(
		(td_quantity > @qty1 AND @exp1=''>'') 
		OR (td_quantity < @qty1 AND @exp1=''<'')
		OR (td_quantity >= @qty1 AND @exp1=''>='') 
		OR (td_quantity <= @qty1 AND @exp1=''<='')
		OR (td_quantity = @qty1 AND @exp1=''='') 
		OR (td_quantity between @qty1 AND @qty2 AND @exp1=''between'')
	))
'
	else ' and 1=1)'
end
+
'
Order By 
	td_datetimestamp
'
set @sql2= N'
select TOP 1000
	tt.sale_type_name as type1,
	td_ref as ref1,
	td_device_data as data1,
	td_amount as amount,
	td_user_name as name,
	td_receipt_id as id1,
	td_department as dept,
	td_store_guid as guid,
	td_item_id as code,
	td_datetimestamp as date, 
	td_device_number as register,
	td_item_description as descr,
	td_store_guid as StoreId,
	td_quantity as qnty 
from
	' + @tablename +'
	join TransactionSaleTypes tt 
		on tt.sale_type_value = convert(nvarchar(20),td_record_type)
where 
	(
		(td_device_data like ''%'' + coalesce(@Criteria1,td_device_data) + ''%'' AND coalesce(@cat1,''1'')=''1'') 
		OR 
		(td_item_id like coalesce(@Criteria1,td_item_id) + ''%'' AND coalesce(@cat1,''2'')=''2'') 
		OR 
		(td_department like coalesce(@Criteria1,td_department) + ''%'' AND coalesce(@cat1,''3'')=''3'')
	) 
	and
	(tt.sale_type_name like coalesce(@type, tt.sale_type_name) + ''%''
	and td_user_name like coalesce(@cashier, td_user_name) + ''%''
	and 
	(
'+
case 
	when isnull(@value,'')<>'' and isnull(@exp,'')<>'' then 
'
	(
		(td_amount > CONVERT(money,@value) AND @exp=''>'' AND coalesce(@evenodd,''1'')=''1'') 
		OR (td_amount < CONVERT(money,@value) AND @exp=''<'' AND coalesce(@evenodd,''1'')=''1'')
		OR (td_amount >= CONVERT(money,@value) AND @exp=''>='' AND coalesce(@evenodd,''1'')=''1'') 
		OR (td_amount <= CONVERT(money,@value) AND @exp=''<='' AND coalesce(@evenodd,''1'')=''1'')
		OR (td_amount = CONVERT(money,@value) AND @exp=''='' AND coalesce(@evenodd,''1'')=''1'') 
		OR (td_amount between CONVERT(money,@value) AND CONVERT(money,@value1) AND @exp=''between'' AND coalesce(@evenodd,''1'')=''1'')
	)
	OR
	(
		((RIGHT(td_amount,2) = ''00'') AND td_amount > CONVERT(money,@value) AND @exp=''>'' AND coalesce(@evenodd,''1'')=''2'') 
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount < CONVERT(money,@value) AND @exp=''<'' AND coalesce(@evenodd,''1'')=''2'')
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount >= CONVERT(money,@value) AND @exp=''>='' AND coalesce(@evenodd,''1'')=''2'') 
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount <= CONVERT(money,@value) AND @exp=''<='' AND coalesce(@evenodd,''1'')=''2'')
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount = CONVERT(money,@value) AND @exp=''='' AND coalesce(@evenodd,''1'')=''2'') 
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount between CONVERT(money,@value) AND CONVERT(money,@value1) AND @exp=''between'' AND coalesce(@evenodd,''1'')=''2'')
	)
'
	else '1=1'
end
+
'
	)
'
+
case 
	when isnull(@qty1,'')<>'' and  isnull(@exp1,'')<>'' then
'	
	and 
	(
		(td_quantity > @qty1 AND @exp1=''>'') 
		OR (td_quantity < @qty1 AND @exp1=''<'')
		OR (td_quantity >= @qty1 AND @exp1=''>='') 
		OR (td_quantity <= @qty1 AND @exp1=''<='')
		OR (td_quantity = @qty1 AND @exp1=''='') 
		OR (td_quantity between @qty1 AND @qty2 AND @exp1=''between'')
	))
'
	else ' and 1=1)'
end
+
'
Order By 
	td_datetimestamp
'
set @sql3= N'
select TOP 1000
	tt.sale_type_name as type1,
	td_ref as ref1,
	td_device_data as data1,
	td_amount as amount,
	td_user_name as name,
	td_receipt_id as id1,
	td_store_guid as guid,
	td_department as dept,
	td_item_id as code,
	td_datetimestamp as date, 
	td_device_number as register,
	td_item_description as descr,
	td_store_guid as StoreId,
	td_quantity as qnty 
from
	' + @tablename +'
	join TransactionSaleTypes tt 
		on tt.sale_type_value = convert(nvarchar(20),td_record_type)
where 
	(
	((
		(td_device_data like ''%'' + coalesce(@Criteria1,td_device_data) + ''%'' AND coalesce(@cat1,''1'')=''1'') 
		OR 
		(td_item_id like coalesce(@Criteria1,td_item_id) + ''%'' AND coalesce(@cat1,''2'')=''2'') 
		OR 
		(td_department like coalesce(@Criteria1,td_department) + ''%'' AND coalesce(@cat1,''3'')=''3'')
	) 
	and 
	(td_receipt_id = ANY(Select td_receipt_id from ' + @tablename +' where 
	(
		(td_device_data like ''%'' + coalesce(@Criteria2,td_device_data) + ''%'' AND coalesce(@cat2,''1'')=''1'') 
		OR 
		(td_item_id like coalesce(@Criteria2,td_item_id) + ''%'' AND coalesce(@cat2,''2'')=''2'') 
		OR 
		(td_department like coalesce(@Criteria2,td_department) + ''%'' AND coalesce(@cat2,''3'')=''3'')
	)
	)
	))
	or
	((
		(td_device_data like ''%'' + coalesce(@Criteria2,td_device_data) + ''%'' AND coalesce(@cat2,''1'')=''1'') 
		OR 
		(td_item_id like coalesce(@Criteria2,td_item_id) + ''%'' AND coalesce(@cat2,''2'')=''2'') 
		OR 
		(td_department like coalesce(@Criteria2,td_department) + ''%'' AND coalesce(@cat2,''3'')=''3'')
	)
	and
	(td_receipt_id = ANY(Select td_receipt_id from ' + @tablename +' where 
	(
		(td_device_data like ''%'' + coalesce(@Criteria1,td_device_data) + ''%'' AND coalesce(@cat1,''1'')=''1'') 
		OR 
		(td_item_id like coalesce(@Criteria1,td_item_id) + ''%'' AND coalesce(@cat1,''2'')=''2'') 
		OR 
		(td_department like coalesce(@Criteria1,td_department) + ''%'' AND coalesce(@cat1,''3'')=''3'')
	)
	)
	)))
	and
	(tt.sale_type_name like coalesce(@type, tt.sale_type_name) + ''%''
	and td_user_name like coalesce(@cashier, td_user_name) + ''%''
	and 
	(
'+
case 
	when isnull(@value,'')<>'' and isnull(@exp,'')<>'' then 
'
	(
		(td_amount > CONVERT(money,@value) AND @exp=''>'' AND coalesce(@evenodd,''1'')=''1'') 
		OR (td_amount < CONVERT(money,@value) AND @exp=''<'' AND coalesce(@evenodd,''1'')=''1'')
		OR (td_amount >= CONVERT(money,@value) AND @exp=''>='' AND coalesce(@evenodd,''1'')=''1'') 
		OR (td_amount <= CONVERT(money,@value) AND @exp=''<='' AND coalesce(@evenodd,''1'')=''1'')
		OR (td_amount = CONVERT(money,@value) AND @exp=''='' AND coalesce(@evenodd,''1'')=''1'') 
		OR (td_amount between CONVERT(money,@value) AND CONVERT(money,@value1) AND @exp=''between'' AND coalesce(@evenodd,''1'')=''1'')
	)
	OR
	(
		((RIGHT(td_amount,2) = ''00'') AND td_amount > CONVERT(money,@value) AND @exp=''>'' AND coalesce(@evenodd,''1'')=''2'') 
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount < CONVERT(money,@value) AND @exp=''<'' AND coalesce(@evenodd,''1'')=''2'')
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount >= CONVERT(money,@value) AND @exp=''>='' AND coalesce(@evenodd,''1'')=''2'') 
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount <= CONVERT(money,@value) AND @exp=''<='' AND coalesce(@evenodd,''1'')=''2'')
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount = CONVERT(money,@value) AND @exp=''='' AND coalesce(@evenodd,''1'')=''2'') 
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount between CONVERT(money,@value) AND CONVERT(money,@value1) AND @exp=''between'' AND coalesce(@evenodd,''1'')=''2'')
	)
'
	else '1=1'
end
+
'
	)
'
+
case 
	when isnull(@qty1,'')<>'' and  isnull(@exp1,'')<>'' then
'	
	and 
	(
		(td_quantity > @qty1 AND @exp1=''>'') 
		OR (td_quantity < @qty1 AND @exp1=''<'')
		OR (td_quantity >= @qty1 AND @exp1=''>='') 
		OR (td_quantity <= @qty1 AND @exp1=''<='')
		OR (td_quantity = @qty1 AND @exp1=''='') 
		OR (td_quantity between @qty1 AND @qty2 AND @exp1=''between'')
	))
'
	else ' and 1=1)'
end
+
'
Order By 
	td_datetimestamp
'
 print @sql2	-- this is for test; uncomment to chech the built sting

	set @ParamDefinition= N'
@Criteria1 nvarchar(155),
@Criteria2 nvarchar(155),
@reg nvarchar(5),
@cashier nvarchar(50),
@type nvarchar(50),
@evenodd int,
@cat1 int,
@cat2 int,
@exp nvarchar(10),
@value nvarchar(50),
@exp1 nvarchar(10),
@value1 nvarchar(50),
@qty1 int,
@qty2 int,
@op_and_or int
'
set @ParamDefinition1= N'
@Criteria1 nvarchar(155),
@reg nvarchar(5),
@cashier nvarchar(50),
@type nvarchar(50),
@evenodd int,
@cat1 int,
@cat2 int,
@exp nvarchar(10),
@value nvarchar(50),
@exp1 nvarchar(10),
@value1 nvarchar(50),
@qty1 int,
@qty2 int,
@op_and_or int
'
set @ParamDefinition2= N'
@Criteria1 nvarchar(155),
@reg nvarchar(5),
@cashier nvarchar(50),
@type nvarchar(50),
@evenodd int,
@cat1 int,
@cat2 int,
@exp nvarchar(10),
@value nvarchar(50),
@exp1 nvarchar(10),
@value1 nvarchar(50),
@qty1 int,
@qty2 int,
@op_and_or int
'
-- print @ParamDefinition	 --this is for test; uncomment to chech the built sting

	if(@Criteria2 is NULL)
	BEGIN
		EXEC sp_executesql @sql2, @ParamDefinition1, 
			@Criteria1=@Criteria1,
			@reg=@reg,
			@cashier=@cashier,
			@type=@type,
			@evenodd=@evenodd,
			@cat1= @cat1,
			@cat2 = @cat2,
			@exp=@exp,
			@value=@value,
			@exp1=@exp1,
			@value1=@value1,
			@qty1=@qty1,
			@qty2=@qty2,
			@op_and_or= @op_and_or
	END
	else if (@Criteria2 is not null and @op_and_or = '2')
	BEGIN
	EXEC sp_executesql @sql1, @ParamDefinition, 
			@Criteria1=@Criteria1,
			@Criteria2=@Criteria2,
			@reg=@reg,
			@cashier=@cashier,
			@type=@type,
			@evenodd=@evenodd,
			@cat1= @cat1,
			@cat2 = @cat2,
			@exp=@exp,
			@value=@value,
			@exp1=@exp1,
			@value1=@value1,
			@qty1=@qty1,
			@qty2=@qty2,
			@op_and_or= @op_and_or
	END
	else if (@Criteria2 is not null and @op_and_or = '1')
	BEGIN
	EXEC sp_executesql @sql3, @ParamDefinition, 
			@Criteria1=@Criteria1,
			@Criteria2=@Criteria2,
			@reg=@reg,
			@cashier=@cashier,
			@type=@type,
			@evenodd=@evenodd,
			@cat1= @cat1,
			@cat2 = @cat2,
			@exp=@exp,
			@value=@value,
			@exp1=@exp1,
			@value1=@value1,
			@qty1=@qty1,
			@qty2=@qty2,
			@op_and_or= @op_and_or
	END
END

Open in new window

0
 
WebRulesAuthor Commented:
hey you there?
0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
Yes, I'm here. It's the middle of the day here, and I'm at work, so only limited time to look at things.
I see several things about your SP that raise questions. The first one is @tablename. What can you tell me about that variable, what it's for? I need to understand why you are building a SQL expression from within a SQL stored procedure, instead of using it directly.
0
 
WebRulesAuthor Commented:
the tablename is the parameter...and we are choosing the table in the database depending on the paramters entered in the web app. using paramters, storename, fromdat and todate.

but now i just need to figure out the date and time part.
0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
so @tablename is actually a string built to contain a subquery?
0
 
WebRulesAuthor Commented:
yes
0
 
Philippe DamervalConnect With a Mentor Senior Analyst ProgrammerCommented:
gotcha. I don't see parentheses around the subquery in the places where it is defined or the places where they are used. That might be a problem. Also, I notice that in your executeSQL statements you do not pass @fromDate and @toDate, so that would explain why you get a message saying that these parameters need to be declared. By the time you run the statement, they are no longer in context. You would need to pass them to the executeSQL instruction as well.
0
 
Alpesh PatelAssistant ConsultantCommented:
Select * from Table where date between Startdate and EndDate
0
 
WebRulesAuthor Commented:
it worked...thank you so much, yeah i did not declare them in executesql..my bad.
0
 
WebRulesAuthor Commented:
got it worked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.