Solved

Dynamic SQL Stored Procedures

Posted on 2011-03-18
59
218 Views
Last Modified: 2012-05-11
Hi,
 I am using the SQL procedure below to implement a search function.
For the Query part, I am doing an 'and' operation on all of them, so when I do not enter a field in the web page, it is taking null and not giving the results. But if I replace 'or' with 'and' in the string @sql, then it does not display any results because when doing coalesce it is taking 'Null' when the field is empty. How do i make the procedure work even if few fields are not entered int he web page, but other fields match.
For now, just for two criterias I am giving two strings sql and sql1, but i cannot do that for every parameter, because it will be too long and not a good implementation.
Please help me with this.
ALTER PROCEDURE [dbo].[SP_Investigations1]
	@StoreName nvarchar(155) = null ,
	@Criteria1 nvarchar(155) = null ,
	@Criteria2 nvarchar(155) = null ,
	@fromDate DateTime = null ,
	@toDate DateTime = null ,
	@reg nvarchar(5) = null ,
	@cashier nvarchar(50) = null ,
	@type nvarchar(50) = null ,
	@evenodd int = null,
	@cat1 int = null,
	@cat2 int = null,
	@exp nvarchar(10) = null ,
	@value nvarchar(50) = null ,
	@exp1 nvarchar(10) = null ,
	@value1 nvarchar(50) = null ,
	@qty1 int = 0,
	@qty2 int = 0
	
AS
BEGIN
      
DECLARE 
	@tablename nvarchar(max),
	@sql1 nvarchar(max),
	@sql2 nvarchar(max),
	@ParamDefinition nvarchar(500),
	@ParamDefinition1 nvarchar(500),
	@months_list varchar(255),
	@intermediateDate as DateTime,
	@id nvarchar(36)
	
if(@StoreName='')
set @StoreName=null;
if(@Criteria1='')
set @Criteria1=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;

-- get the store GUID from its name	
select 
	@id=cast(sd_store_guid as varchar(36)) 
from 
	StoreDetails
where
	sd_store_name like coalesce(@StoreName,sd_store_name) + '%'
-- check if we got result; if not return with message
if @id is null
begin
	print 'Store name doesn''t exist!'
	select 'Store name doesn''t exist!' as result
	return
end


-- 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,'')+left(datename(month,@intermediateDate),3)+',',
		@intermediateDate=dateadd(month,1,@intermediateDate)
end
	
-- /*
print 'From: '+convert(varchar(23), @fromDate, 121)+';
To  : '+convert(varchar(23), @fromDate, 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 coalesce(nullif('''+isnull(convert(varchar(23),@fromDate,121),'''')+''',''''),td_datetimestamp) and coalesce(nullif('''+isnull(convert(varchar(23),@toDate,121),'''')+''',''''),td_datetimestamp)
		union'
from 
	(select distinct table_name from information_schema.columns where @months_list like '%,'+substring(table_name, 17, 3)+',%' 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 
	tt.tt_descr as type1,
	sd.sd_store_name as StoreName,
	sd.sd_store_guid,
	td_ref as ref1,
	td_device_data as data1,
	td_amount as amount,
	td_user_name as name,
	td_receipt_id as id1,
	td_dept_number as dept,
	td_UPC_CODE as code,
	td_datetimestamp as date, 
	RIGHT(td_device_name,1) as register,
	td_item_description as descr,
	td_store_guid as StoreId,
	td_quantity as qnty 
from
	' + @tablename +'
	join StoreDetails sd 
		on sd.sd_store_guid = td_store_guid  
	join TypeConfig tt 
		on tt.tt_id = td_type
where 
	(((td_item_description like coalesce(@Criteria1,td_item_description) + ''%'' AND @cat1=''1'') OR (td_UPC_CODE like coalesce(@Criteria1,td_UPC_CODE) + ''%'' AND @cat1=''2'') OR (td_dept_number like coalesce(@Criteria1,td_dept_number) + ''%'' AND @cat1=''3'')) 
	and
	 ((td_item_description like coalesce(@Criteria2,td_item_description) + ''%'' AND @cat2=''1'') OR (td_UPC_CODE like coalesce(@Criteria2,td_UPC_CODE) + ''%'' AND @cat2=''2'') OR (td_dept_number like coalesce(@Criteria2,td_dept_number) + ''%'' AND @cat2=''3'')))
	and tt.tt_descr like coalesce(@type, tt.tt_descr) + ''%''
	and RIGHT(td_device_name,1) like coalesce(@reg, td_device_name) + ''%''
	and td_user_name like coalesce(@cashier, td_user_name) + ''%''
	and 
	((
		(td_amount > CONVERT(money,@value) AND @exp=''>'' AND @evenodd=''1'') 
		OR (td_amount < CONVERT(money,@value) AND @exp=''<'' AND @evenodd=''1'')
		OR (td_amount >= CONVERT(money,@value) AND @exp=''>='' AND @evenodd=''1'') 
		OR (td_amount <= CONVERT(money,@value) AND @exp=''<='' AND @evenodd=''1'')
		OR (td_amount = CONVERT(money,@value) AND @exp=''='' AND @evenodd=''1'') 
		OR (td_amount between CONVERT(money,@value) AND CONVERT(money,@value1) AND @exp=''between'' AND @evenodd=''1'')
	)
	OR
	(
		((RIGHT(td_amount,2) = ''00'') AND td_amount > CONVERT(money,@value) AND @exp=''>'' AND @evenodd=''2'') 
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount < CONVERT(money,@value) AND @exp=''<'' AND @evenodd=''2'')
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount >= CONVERT(money,@value) AND @exp=''>='' AND @evenodd=''2'') 
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount <= CONVERT(money,@value) AND @exp=''<='' AND @evenodd=''2'')
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount = CONVERT(money,@value) AND @exp=''='' AND @evenodd=''2'') 
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount between CONVERT(money,@value) AND CONVERT(money,@value1) AND @exp=''between'' AND @evenodd=''2'')
	))	
	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'')
	)
Order By 
	td_datetimestamp
'
set @sql2= N'
select 
	tt.tt_descr as type1,
	sd.sd_store_name as StoreName,
	sd.sd_store_guid,
	td_ref as ref1,
	td_device_data as data1,
	td_amount as amount,
	td_user_name as name,
	td_receipt_id as id1,
	td_dept_number as dept,
	td_UPC_CODE as code,
	td_datetimestamp as date, 
	RIGHT(td_device_name,1) as register,
	td_item_description as descr,
	td_store_guid as StoreId,
	td_quantity as qnty 
from
	' + @tablename +'
	join StoreDetails sd 
		on sd.sd_store_guid = td_store_guid  
	join TypeConfig tt 
		on tt.tt_id = td_type
where 
	((td_item_description like coalesce(@Criteria1,td_item_description) + ''%'' AND @cat1=''1'') OR (td_UPC_CODE like coalesce(@Criteria1,td_UPC_CODE) + ''%'' AND @cat1=''2'') OR (td_dept_number like coalesce(@Criteria1,td_dept_number) + ''%'' AND @cat1=''3'')) 
	and
	(tt.tt_descr like coalesce(@type, tt.tt_descr) + ''%''
	and RIGHT(td_device_name,1) like coalesce(@reg, td_device_name) + ''%''
	and td_user_name like coalesce(@cashier, td_user_name) + ''%''
	and 
	((
		(td_amount > CONVERT(money,@value) AND @exp=''>'' AND @evenodd=''1'') 
		OR (td_amount < CONVERT(money,@value) AND @exp=''<'' AND @evenodd=''1'')
		OR (td_amount >= CONVERT(money,@value) AND @exp=''>='' AND @evenodd=''1'') 
		OR (td_amount <= CONVERT(money,@value) AND @exp=''<='' AND @evenodd=''1'')
		OR (td_amount = CONVERT(money,@value) AND @exp=''='' AND @evenodd=''1'') 
		OR (td_amount between CONVERT(money,@value) AND CONVERT(money,@value1) AND @exp=''between'' AND @evenodd=''1'')
	)
	OR
	(
		((RIGHT(td_amount,2) = ''00'') AND td_amount > CONVERT(money,@value) AND @exp=''>'' AND @evenodd=''2'') 
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount < CONVERT(money,@value) AND @exp=''<'' AND @evenodd=''2'')
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount >= CONVERT(money,@value) AND @exp=''>='' AND @evenodd=''2'') 
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount <= CONVERT(money,@value) AND @exp=''<='' AND @evenodd=''2'')
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount = CONVERT(money,@value) AND @exp=''='' AND @evenodd=''2'') 
		OR ((RIGHT(td_amount,2) = ''00'') AND td_amount between CONVERT(money,@value) AND CONVERT(money,@value1) AND @exp=''between'' AND @evenodd=''2'')
	))	
	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'')
	))
Order By 
	td_datetimestamp
'
 --print @sql	-- 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
'
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
'
-- 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
	END
	else
	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
	END
END

Open in new window

0
Comment
Question by:WebRules
  • 27
  • 26
  • 2
  • +2
59 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 35168520
Just a thought ... for "like" fields, instead of setting the parameters to null, set them to '%'.  This will let you use these parameters in every query:

  if(isNull(@reg,'')='')
  set @reg='%';

but note that doing this for every parameter may slow things down considerably...
0
 

Author Comment

by:WebRules
ID: 35168622
@knightEknight: That did not work. I tried..:(
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35169000
basically, when you have dynamic sql anyhow, you shall build the sql to add the where conditions only if you have a value you want to filter.

in short:
 ...
declare @sql nvarchar(max)
declare @params nvarchar(max) 

set @params = ''
set @sql = ' select ... from table where 1 = 1 '

set @params = @params + ' @param1 int,'
if @param1 is not null
  set @sql = @sql + ' and col1 = @param1 '

set @params = @params + ' @param2 varchar(100),'
if @param1 is not null
  set @sql = @sql + ' and col2 = @param2 '

... etc

set @params = @params + ' @dummy int' 

exec sp_executesql @sql, @params, @param1, @param2 ... , null  --- the null is for the "dummy"

Open in new window


you will see you have no issues with data types, quotes etc.

if you don't go dynamic sql, you can do:
select ...
  from  ....
 where ( @param1 is null OR col1 = @param1 )
   and ( @param2 is null OR col2 = @param2 )
  ... etc 

Open in new window


choose your poison


0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 35171503
Try bellow:
ALTER PROCEDURE [dbo].[SP_Investigations1]
	@StoreName nvarchar(155) = null ,
	@Criteria1 nvarchar(155) = null ,
	@Criteria2 nvarchar(155) = null ,
	@fromDate DateTime = null ,
	@toDate DateTime = null ,
	@reg nvarchar(5) = null ,
	@cashier nvarchar(50) = null ,
	@type nvarchar(50) = null ,
	@evenodd int = null,
	@cat1 int = null,
	@cat2 int = null,
	@exp nvarchar(10) = null ,
	@value nvarchar(50) = null ,
	@exp1 nvarchar(10) = null ,
	@value1 nvarchar(50) = null ,
	@qty1 int = 0,
	@qty2 int = 0
	
AS
BEGIN
      
DECLARE 
	@tablename nvarchar(max),
	@sql1 nvarchar(max),
	@sql2 nvarchar(max),
	@ParamDefinition nvarchar(500),
	@ParamDefinition1 nvarchar(500),
	@months_list varchar(255),
	@intermediateDate as DateTime,
	@id nvarchar(36)
	
if(@StoreName='')
set @StoreName=null;
if(@Criteria1='')
set @Criteria1=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;

-- get the store GUID from its name	
select 
	@id=cast(sd_store_guid as varchar(36)) 
from 
	StoreDetails
where
	sd_store_name like coalesce(@StoreName,sd_store_name) + '%'
-- check if we got result; if not return with message
if @id is null
begin
	print 'Store name doesn''t exist!'
	select 'Store name doesn''t exist!' as result
	return
end


-- 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,'')+left(datename(month,@intermediateDate),3)+',',
		@intermediateDate=dateadd(month,1,@intermediateDate)
end
	
-- /*
print 'From: '+convert(varchar(23), @fromDate, 121)+';
To  : '+convert(varchar(23), @fromDate, 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 coalesce(nullif('''+isnull(convert(varchar(23),@fromDate,121),'''')+''',''''),td_datetimestamp) and coalesce(nullif('''+isnull(convert(varchar(23),@toDate,121),'''')+''',''''),td_datetimestamp)
		union'
from 
	(select distinct table_name from information_schema.columns where @months_list like '%,'+substring(table_name, 17, 3)+',%' 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 
	tt.tt_descr as type1,
	sd.sd_store_name as StoreName,
	sd.sd_store_guid,
	td_ref as ref1,
	td_device_data as data1,
	td_amount as amount,
	td_user_name as name,
	td_receipt_id as id1,
	td_dept_number as dept,
	td_UPC_CODE as code,
	td_datetimestamp as date, 
	RIGHT(td_device_name,1) as register,
	td_item_description as descr,
	td_store_guid as StoreId,
	td_quantity as qnty 
from
	' + @tablename +'
	join StoreDetails sd 
		on sd.sd_store_guid = td_store_guid  
	join TypeConfig tt 
		on tt.tt_id = td_type
where 
	(((td_item_description like coalesce(@Criteria1,td_item_description) + ''%'' AND @cat1=''1'') OR (td_UPC_CODE like coalesce(@Criteria1,td_UPC_CODE) + ''%'' AND @cat1=''2'') OR (td_dept_number like coalesce(@Criteria1,td_dept_number) + ''%'' AND @cat1=''3'')) 
	and
	 ((td_item_description like coalesce(@Criteria2,td_item_description) + ''%'' AND @cat2=''1'') OR (td_UPC_CODE like coalesce(@Criteria2,td_UPC_CODE) + ''%'' AND @cat2=''2'') OR (td_dept_number like coalesce(@Criteria2,td_dept_number) + ''%'' AND @cat2=''3'')))
	and tt.tt_descr like coalesce(@type, tt.tt_descr) + ''%''
	and RIGHT(td_device_name,1) like coalesce(@reg, td_device_name) + ''%''
	and td_user_name like coalesce(@cashier, td_user_name) + ''%''
	and 
	((
		(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'')
	))	
	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'')
	)
Order By 
	td_datetimestamp
'
set @sql2= N'
select 
	tt.tt_descr as type1,
	sd.sd_store_name as StoreName,
	sd.sd_store_guid,
	td_ref as ref1,
	td_device_data as data1,
	td_amount as amount,
	td_user_name as name,
	td_receipt_id as id1,
	td_dept_number as dept,
	td_UPC_CODE as code,
	td_datetimestamp as date, 
	RIGHT(td_device_name,1) as register,
	td_item_description as descr,
	td_store_guid as StoreId,
	td_quantity as qnty 
from
	' + @tablename +'
	join StoreDetails sd 
		on sd.sd_store_guid = td_store_guid  
	join TypeConfig tt 
		on tt.tt_id = td_type
where 
	(
		(td_item_description like coalesce(@Criteria1,td_item_description) + ''%'' AND coalesce(@cat1,''1'')=''1'') 
		OR 
		(td_UPC_CODE like coalesce(@Criteria1,td_UPC_CODE) + ''%'' AND coalesce(@cat1,''2'')=''2'') 
		OR 
		(td_dept_number like coalesce(@Criteria1,td_dept_number) + ''%'' AND coalesce(@cat1,''3'')=''3'')
	) 
	and
	(tt.tt_descr like coalesce(@type, tt.tt_descr) + ''%''
	and RIGHT(td_device_name,1) like coalesce(@reg, td_device_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 @sql	-- 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
'
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
'
-- 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
	END
	else
	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
	END
END

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35171513
I only modified @sql2.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35171527
I think I missed a bracket:
ALTER PROCEDURE [dbo].[SP_Investigations1]
	@StoreName nvarchar(155) = null ,
	@Criteria1 nvarchar(155) = null ,
	@Criteria2 nvarchar(155) = null ,
	@fromDate DateTime = null ,
	@toDate DateTime = null ,
	@reg nvarchar(5) = null ,
	@cashier nvarchar(50) = null ,
	@type nvarchar(50) = null ,
	@evenodd int = null,
	@cat1 int = null,
	@cat2 int = null,
	@exp nvarchar(10) = null ,
	@value nvarchar(50) = null ,
	@exp1 nvarchar(10) = null ,
	@value1 nvarchar(50) = null ,
	@qty1 int = 0,
	@qty2 int = 0
	
AS
BEGIN
      
DECLARE 
	@tablename nvarchar(max),
	@sql1 nvarchar(max),
	@sql2 nvarchar(max),
	@ParamDefinition nvarchar(500),
	@ParamDefinition1 nvarchar(500),
	@months_list varchar(255),
	@intermediateDate as DateTime,
	@id nvarchar(36)
	
if(@StoreName='')
set @StoreName=null;
if(@Criteria1='')
set @Criteria1=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;

-- get the store GUID from its name	
select 
	@id=cast(sd_store_guid as varchar(36)) 
from 
	StoreDetails
where
	sd_store_name like coalesce(@StoreName,sd_store_name) + '%'
-- check if we got result; if not return with message
if @id is null
begin
	print 'Store name doesn''t exist!'
	select 'Store name doesn''t exist!' as result
	return
end


-- 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,'')+left(datename(month,@intermediateDate),3)+',',
		@intermediateDate=dateadd(month,1,@intermediateDate)
end
	
-- /*
print 'From: '+convert(varchar(23), @fromDate, 121)+';
To  : '+convert(varchar(23), @fromDate, 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 coalesce(nullif('''+isnull(convert(varchar(23),@fromDate,121),'''')+''',''''),td_datetimestamp) and coalesce(nullif('''+isnull(convert(varchar(23),@toDate,121),'''')+''',''''),td_datetimestamp)
		union'
from 
	(select distinct table_name from information_schema.columns where @months_list like '%,'+substring(table_name, 17, 3)+',%' 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 
	tt.tt_descr as type1,
	sd.sd_store_name as StoreName,
	sd.sd_store_guid,
	td_ref as ref1,
	td_device_data as data1,
	td_amount as amount,
	td_user_name as name,
	td_receipt_id as id1,
	td_dept_number as dept,
	td_UPC_CODE as code,
	td_datetimestamp as date, 
	RIGHT(td_device_name,1) as register,
	td_item_description as descr,
	td_store_guid as StoreId,
	td_quantity as qnty 
from
	' + @tablename +'
	join StoreDetails sd 
		on sd.sd_store_guid = td_store_guid  
	join TypeConfig tt 
		on tt.tt_id = td_type
where 
	(((td_item_description like coalesce(@Criteria1,td_item_description) + ''%'' AND @cat1=''1'') OR (td_UPC_CODE like coalesce(@Criteria1,td_UPC_CODE) + ''%'' AND @cat1=''2'') OR (td_dept_number like coalesce(@Criteria1,td_dept_number) + ''%'' AND @cat1=''3'')) 
	and
	 ((td_item_description like coalesce(@Criteria2,td_item_description) + ''%'' AND @cat2=''1'') OR (td_UPC_CODE like coalesce(@Criteria2,td_UPC_CODE) + ''%'' AND @cat2=''2'') OR (td_dept_number like coalesce(@Criteria2,td_dept_number) + ''%'' AND @cat2=''3'')))
	and tt.tt_descr like coalesce(@type, tt.tt_descr) + ''%''
	and RIGHT(td_device_name,1) like coalesce(@reg, td_device_name) + ''%''
	and td_user_name like coalesce(@cashier, td_user_name) + ''%''
	and 
	((
		(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'')
	))	
	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'')
	)
Order By 
	td_datetimestamp
'
set @sql2= N'
select 
	tt.tt_descr as type1,
	sd.sd_store_name as StoreName,
	sd.sd_store_guid,
	td_ref as ref1,
	td_device_data as data1,
	td_amount as amount,
	td_user_name as name,
	td_receipt_id as id1,
	td_dept_number as dept,
	td_UPC_CODE as code,
	td_datetimestamp as date, 
	RIGHT(td_device_name,1) as register,
	td_item_description as descr,
	td_store_guid as StoreId,
	td_quantity as qnty 
from
	' + @tablename +'
	join StoreDetails sd 
		on sd.sd_store_guid = td_store_guid  
	join TypeConfig tt 
		on tt.tt_id = td_type
where 
	(
		(td_item_description like coalesce(@Criteria1,td_item_description) + ''%'' AND coalesce(@cat1,''1'')=''1'') 
		OR 
		(td_UPC_CODE like coalesce(@Criteria1,td_UPC_CODE) + ''%'' AND coalesce(@cat1,''2'')=''2'') 
		OR 
		(td_dept_number like coalesce(@Criteria1,td_dept_number) + ''%'' AND coalesce(@cat1,''3'')=''3'')
	) 
	and
	(tt.tt_descr like coalesce(@type, tt.tt_descr) + ''%''
	and RIGHT(td_device_name,1) like coalesce(@reg, td_device_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 @sql	-- 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
'
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
'
-- 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
	END
	else
	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
	END
END

Open in new window

0
 

Author Comment

by:WebRules
ID: 35184159
This is not working completely.
Like if I give the storename, fromdate, todate, criteria1, then it does not display the results.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35184733
Please debug it using prints. You shouldn't see what comes out depending on what parameter values you use. I wrote the modification without testing anything. It is possible that I missed something. You can post the results here.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35184736
I meant "You should see..."
0
 

Author Comment

by:WebRules
ID: 35192365
Will print it and paste the results here..
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35193234
I ran a print test only with the parameters you used with some mockup values:

exec [dbo].[SP_Investigations1] @StoreName='StoreName', @fromDate='2011-03-01', @toDate='2011-03-20', @Criteria1='Criteria1'

The query came out right. The reason you don't get results is because there are no rows to satisfy the query as it is built.

You can try the queries directly by declaring some variable with the names like the parameters and then initialize them with your values.

below are the queries I got but you can't use them because of my mock-up values.


select 
	tt.tt_descr as type1,
	sd.sd_store_name as StoreName,
	sd.sd_store_guid,
	td_ref as ref1,
	td_device_data as data1,
	td_amount as amount,
	td_user_name as name,
	td_receipt_id as id1,
	td_dept_number as dept,
	td_UPC_CODE as code,
	td_datetimestamp as date, 
	RIGHT(td_device_name,1) as register,
	td_item_description as descr,
	td_store_guid as StoreId,
	td_quantity as qnty 
from
	Store_name_table
	join StoreDetails sd 
		on sd.sd_store_guid = td_store_guid  
	join TypeConfig tt 
		on tt.tt_id = td_type
where 
	(((td_item_description like coalesce(@Criteria1,td_item_description) + '%' AND @cat1='1') OR (td_UPC_CODE like coalesce(@Criteria1,td_UPC_CODE) + '%' AND @cat1='2') OR (td_dept_number like coalesce(@Criteria1,td_dept_number) + '%' AND @cat1='3')) 
	and
	 ((td_item_description like coalesce(@Criteria2,td_item_description) + '%' AND @cat2='1') OR (td_UPC_CODE like coalesce(@Criteria2,td_UPC_CODE) + '%' AND @cat2='2') OR (td_dept_number like coalesce(@Criteria2,td_dept_number) + '%' AND @cat2='3')))
	and tt.tt_descr like coalesce(@type, tt.tt_descr) + '%'
	and RIGHT(td_device_name,1) like coalesce(@reg, td_device_name) + '%'
	and td_user_name like coalesce(@cashier, td_user_name) + '%'
	and 
	((
		(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')
	))	
	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')
	)
Order By 
	td_datetimestamp
@sql2: 


select 
	tt.tt_descr as type1,
	sd.sd_store_name as StoreName,
	sd.sd_store_guid,
	td_ref as ref1,
	td_device_data as data1,
	td_amount as amount,
	td_user_name as name,
	td_receipt_id as id1,
	td_dept_number as dept,
	td_UPC_CODE as code,
	td_datetimestamp as date, 
	RIGHT(td_device_name,1) as register,
	td_item_description as descr,
	td_store_guid as StoreId,
	td_quantity as qnty 
from
	Store_name_table
	join StoreDetails sd 
		on sd.sd_store_guid = td_store_guid  
	join TypeConfig tt 
		on tt.tt_id = td_type
where 
	(
		(td_item_description like coalesce(@Criteria1,td_item_description) + '%' AND coalesce(@cat1,'1')='1') 
		OR 
		(td_UPC_CODE like coalesce(@Criteria1,td_UPC_CODE) + '%' AND coalesce(@cat1,'2')='2') 
		OR 
		(td_dept_number like coalesce(@Criteria1,td_dept_number) + '%' AND coalesce(@cat1,'3')='3')
	) 
	and
	(tt.tt_descr like coalesce(@type, tt.tt_descr) + '%'
	and RIGHT(td_device_name,1) like coalesce(@reg, td_device_name) + '%'
	and td_user_name like coalesce(@cashier, td_user_name) + '%'
	and 
	(
1=1
	)
 and 1=1)
Order By 
	td_datetimestamp

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35193246
Once again to be more clear:
@sql1: 

select 
	tt.tt_descr as type1,
	sd.sd_store_name as StoreName,
	sd.sd_store_guid,
	td_ref as ref1,
	td_device_data as data1,
	td_amount as amount,
	td_user_name as name,
	td_receipt_id as id1,
	td_dept_number as dept,
	td_UPC_CODE as code,
	td_datetimestamp as date, 
	RIGHT(td_device_name,1) as register,
	td_item_description as descr,
	td_store_guid as StoreId,
	td_quantity as qnty 
from
	Store_name_table
	join StoreDetails sd 
		on sd.sd_store_guid = td_store_guid  
	join TypeConfig tt 
		on tt.tt_id = td_type
where 
	(((td_item_description like coalesce(@Criteria1,td_item_description) + '%' AND @cat1='1') OR (td_UPC_CODE like coalesce(@Criteria1,td_UPC_CODE) + '%' AND @cat1='2') OR (td_dept_number like coalesce(@Criteria1,td_dept_number) + '%' AND @cat1='3')) 
	and
	 ((td_item_description like coalesce(@Criteria2,td_item_description) + '%' AND @cat2='1') OR (td_UPC_CODE like coalesce(@Criteria2,td_UPC_CODE) + '%' AND @cat2='2') OR (td_dept_number like coalesce(@Criteria2,td_dept_number) + '%' AND @cat2='3')))
	and tt.tt_descr like coalesce(@type, tt.tt_descr) + '%'
	and RIGHT(td_device_name,1) like coalesce(@reg, td_device_name) + '%'
	and td_user_name like coalesce(@cashier, td_user_name) + '%'
	and 
	((
		(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')
	))	
	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')
	)
Order By 
	td_datetimestamp



@sql2: 

select 
	tt.tt_descr as type1,
	sd.sd_store_name as StoreName,
	sd.sd_store_guid,
	td_ref as ref1,
	td_device_data as data1,
	td_amount as amount,
	td_user_name as name,
	td_receipt_id as id1,
	td_dept_number as dept,
	td_UPC_CODE as code,
	td_datetimestamp as date, 
	RIGHT(td_device_name,1) as register,
	td_item_description as descr,
	td_store_guid as StoreId,
	td_quantity as qnty 
from
	Store_name_table
	join StoreDetails sd 
		on sd.sd_store_guid = td_store_guid  
	join TypeConfig tt 
		on tt.tt_id = td_type
where 
	(
		(td_item_description like coalesce(@Criteria1,td_item_description) + '%' AND coalesce(@cat1,'1')='1') 
		OR 
		(td_UPC_CODE like coalesce(@Criteria1,td_UPC_CODE) + '%' AND coalesce(@cat1,'2')='2') 
		OR 
		(td_dept_number like coalesce(@Criteria1,td_dept_number) + '%' AND coalesce(@cat1,'3')='3')
	) 
	and
	(tt.tt_descr like coalesce(@type, tt.tt_descr) + '%'
	and RIGHT(td_device_name,1) like coalesce(@reg, td_device_name) + '%'
	and td_user_name like coalesce(@cashier, td_user_name) + '%'
	and 
	(
1=1
	)
 and 1=1)
Order By 
	td_datetimestamp

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35193256
It is also possible that the criteria you used doesn't match to what is in the table or maybe is wrong.

The procedure is fine but you have to make sure that the queries and the parameters you pass are correct.
0
 

Author Comment

by:WebRules
ID: 35193981
alright. Thanks .

do you know why this exception occurs?

"The number of parameters does not match number of values for stored procedure" system.Exception.Invalidoperation

I checked the parameters, i am passing all of them right.
But still i get this.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35194005
Use a call with parameter names. There are 2 advantages:

1. You don't have to use all of them (if they have default values and they do here)
2. You don't have to worry about the order.

Example below.
exec [dbo].[SP_Investigations1] 
	@StoreName='StoreName', 
	@fromDate='2011-03-01', 
	@toDate='2011-03-20', 
	@Criteria1='Criteria1'

Open in new window

0
 

Author Comment

by:WebRules
ID: 35194086
This is an error when i run from the web application.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35194213
Then you must have forgotten a parameter or maybe the definition of the stored procedure changed when you tested. You need to look in the code and make sure everything is right.

The error says it clearly what the problem is.
0
 

Author Comment

by:WebRules
ID: 35194666
hey i am confused with the multiple codes you posted above...i want to confirm once..
which one is the correct one which has worked for you with those parameters?
can you give me the id of the post above?
just for confirmation..
thanks
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35197504
The last one is always the good one, in this case ID: 35193246

I only posted it twice vbecaus in the first I forgot the "@sql1:" part and the 2 queries were not separated by enough space and I wanted to make sure that they are clear.

O got those 2 queries by executed the procedure as in ID: 35194005 post after I enabled the print statements in the procedure. I also had to improvise because I don't have the actual store tables that are queried in the procedure but the purpose here was to see how the SQL statements are built so the name of the tables are irrelevant.

You can do the same in your code and debug it and please do that. This is something that you don't need the halp from here, which is also limited by the fact that we don't know exactly what the design and the intention is.

My point is that the procedure is fine and the only thing that needs working is the dynamic query and/or the parameters that are passed in. If you don't get result doesn't necessarily mean that the procedure is wrong. In my opinion you already got all the help you need to get this done.
0
 

Author Comment

by:WebRules
ID: 35200748
These are the parameters I am passing:
exec SP_Investigations1 @StoreName=N'mur',@Criteria1=N'fuji',@Criteria2=N'',@fromDate='2010-05-25 00:00:00',@toDate='2010-05-29 00:00:00',@reg=N'1',@cashier=N'Elizabeth',@type=N'Tax',@evenodd=1,@cat1=1,@cat2=1,@exp=N'',@value=N'',@exp1=N'',@value1=N'',@qty1=N'',@qty2=N''

It works with the same parameter in the SQL Server.
But when i pass the same ones from the Web App as above, it sayd 'no records'.
I debugged and checked and the paramters being passed are right.
i am using the exact procedure from ID: 35171527
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35202245
>But when i pass the same ones from the Web App as above, it sayd 'no records'.
this normally means that the web app does either not connect to the correct db, or is passing the arguments the wrong way .
0
 

Author Comment

by:WebRules
ID: 35207957
@Zberteoc: Thank you so much. The procedure works for all the parameters.
But there is a problem with parameters cashier and register. If these parameters are null it is not displaying any records even if the tables has them. If these parameters are supplied, the filtering is right.
It is taking null if these two parameters are not passed.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35207979
Glad to here.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35207981
...hear. :)
0
 

Author Comment

by:WebRules
ID: 35208226
if cashier and register parameter paramters are null, it is not filtering. how to do that?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35208266
I don't see the any register parameter. Maybe because you don't initialize it to '' if null?
0
 

Author Comment

by:WebRules
ID: 35208292
it is the paramter @reg.
i am initializing all of them to null.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35208553
Yes but some of them you set them nu '' (empty string) if they are NULL, not these 2 though.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:WebRules
ID: 35208728
I have set that:
if(@reg='')
set @reg=null;
if(@cashier='')
set @cashier=null;
0
 

Author Comment

by:WebRules
ID: 35208739
It works if cashier is null, but when register is null it is not filtering.
0
 

Author Comment

by:WebRules
ID: 35208824
I figured it out.......yayy thanks a lot
0
 

Author Comment

by:WebRules
ID: 35209847
How to select records from same table associated with an id?
for example, how to select the transactions associated with the same receipt id?
0
 

Author Comment

by:WebRules
ID: 35212042
@Zberteoc: One last question..please please..I have a release of this app tomorrow and now I modified the month list to the new date format..and it did not work:((
Please help me:

You asked to replace @monthslist to
@months_list=isnull(@months_list,'')+REPLACE(convert(varchar(7),@intermediateDate, 121),'-','')+',',

if the table name is of format
TransactionData_201005_c82e104a_5011_49a0_9919_93b8b53bc2d0
instead of May it YYYYMM now.

I tried replacing it but it is printing this:
From: 2010-05-25 00:00:00.000;
To  : 2010-05-25 00:00:00.000;
Month list: ,May,;
GUID: C82E104A-5011-49A0-9919-93B8B53BC2D0

Plzzzzzzz help me with this one last thing.

Thanks!
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35213989

Replace this:


            @months_list=isnull(@months_list,'')+left(datename(month,@intermediateDate),3)+',',
            
            with this
            
            @months_list=isnull(@months_list,'')+replace(convert(varchar(7),@intermediateDate,121),'-','')+',',
0
 

Author Comment

by:WebRules
ID: 35214730
I replaced as you said above and gave the start and end dates as:
05/25/2010 and  05/28/2010.
But this is what is being printed:
From: 2010-05-25 00:00:00.000;
To  : 2010-05-25 00:00:00.000;
Month list: ,201005,;
GUID: C82E104A-5011-49A0-9919-93B8B53BC2D0
0
 

Author Comment

by:WebRules
ID: 35214824
This is the exact code I am using.
ALTER PROCEDURE [dbo].[SP_Investigations1]
	@StoreName nvarchar(155) = null ,
	@Criteria1 nvarchar(155) = null ,
	@Criteria2 nvarchar(155) = null ,
	@fromDate DateTime = null ,
	@toDate 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
	
AS
BEGIN
      
DECLARE 
	@tablename nvarchar(max),
	@sql1 nvarchar(max),
	@sql2 nvarchar(max),
	@ParamDefinition nvarchar(500),
	@ParamDefinition1 nvarchar(500),
	@months_list varchar(255),
	@intermediateDate as DateTime,
	@id nvarchar(36)
	
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='')
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='')
set @type=null;

-- get the store GUID from its name	
select 
	@id=cast(sd_store_guid as varchar(36)) 
from 
	StoreDetails
where
	sd_store_name like coalesce(@StoreName,sd_store_name) + '%'
-- check if we got result; if not return with message
if @id is null
begin
	print 'Store name doesn''t exist!'
	select 'Store name doesn''t exist!' as result
	return
end


-- 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), @fromDate, 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 coalesce(nullif('''+isnull(convert(varchar(23),@fromDate,121),'''')+''',''''),td_datetimestamp) and coalesce(nullif('''+isnull(convert(varchar(23),@toDate,121),'''')+''',''''),td_datetimestamp)
		union'
from 
	(select distinct table_name from information_schema.columns where @months_list like '%,'+substring(table_name, 17, 3)+',%' 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 
	tt.tt_descr as type1,
	sd.sd_store_name as StoreName,
	sd.sd_store_guid,
	td_ref as ref1,
	td_device_data as data1,
	td_amount as amount,
	td_user_name as name,
	td_receipt_id as id1,
	td_dept_number as dept,
	td_UPC_CODE as code,
	td_datetimestamp as date, 
	RIGHT(td_device_name,1) as register,
	td_item_description as descr,
	td_store_guid as StoreId,
	td_quantity as qnty 
from
	' + @tablename +'
	join StoreDetails sd 
		on sd.sd_store_guid = td_store_guid  
	join TypeConfig tt 
		on tt.tt_id = td_type
where 
	(((td_item_description like coalesce(@Criteria1,td_item_description) + ''%'' AND @cat1=''1'') OR (td_UPC_CODE like coalesce(@Criteria1,td_UPC_CODE) + ''%'' AND @cat1=''2'') OR (td_dept_number like coalesce(@Criteria1,td_dept_number) + ''%'' AND @cat1=''3'')) 
	and
	 ((td_item_description like coalesce(@Criteria2,td_item_description) + ''%'' AND @cat2=''1'') OR (td_UPC_CODE like coalesce(@Criteria2,td_UPC_CODE) + ''%'' AND @cat2=''2'') OR (td_dept_number like coalesce(@Criteria2,td_dept_number) + ''%'' AND @cat2=''3'')))
	and tt.tt_descr like coalesce(@type, tt.tt_descr) + ''%''
	and RIGHT(td_device_name,1) like coalesce(@reg, td_device_name) + ''%''
	and td_user_name like coalesce(@cashier, td_user_name) + ''%''
	and 
	((
		(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'')
	))	
	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'')
	)
Order By 
	td_datetimestamp
'
set @sql2= N'
select 
	tt.tt_descr as type1,
	sd.sd_store_name as StoreName,
	sd.sd_store_guid,
	td_ref as ref1,
	td_device_data as data1,
	td_amount as amount,
	td_user_name as name,
	td_receipt_id as id1,
	td_dept_number as dept,
	td_UPC_CODE as code,
	td_datetimestamp as date, 
	RIGHT(td_device_name,1) as register,
	td_item_description as descr,
	td_store_guid as StoreId,
	td_quantity as qnty 
from
	' + @tablename +'
	join StoreDetails sd 
		on sd.sd_store_guid = td_store_guid  
	join TypeConfig tt 
		on tt.tt_id = td_type
where 
	(
		(td_item_description like coalesce(@Criteria1,td_item_description) + ''%'' AND coalesce(@cat1,''1'')=''1'') 
		OR 
		(td_UPC_CODE like coalesce(@Criteria1,td_UPC_CODE) + ''%'' AND coalesce(@cat1,''2'')=''2'') 
		OR 
		(td_dept_number like coalesce(@Criteria1,td_dept_number) + ''%'' AND coalesce(@cat1,''3'')=''3'')
	) 
	and
	(tt.tt_descr like coalesce(@type, tt.tt_descr) + ''%''
	and RIGHT(td_device_name,1) like coalesce(@reg, RIGHT(td_device_name,1)) + ''%''
	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 @sql	-- 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
'
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
'
-- 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
	END
	else
	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
	END
END

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35215247
But that is what you need, no? You said that the table names are like:

TransactionData_201005_c82e104a_5011_49a0_9919_93b8b53bc2d0

so the code has to look for the bales that have names like in the "Month list", which is now ,201005, instead of ,May,

and this will match the file name above. That is the purpose of the @months_list variable.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35215278
Sorry, you need to make another change. The length for that month name is now 6 instead of 3.

Replace this:


      (select distinct table_name from information_schema.columns where @months_list like '%,'+substring(table_name, 17, 3)+',%' and replace(table_name,'_','*') like '%'+replace(@id,'-','*')) tbls

      with this:
      
      (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
0
 

Author Comment

by:WebRules
ID: 35215486
got it..thanks a ton
0
 

Author Comment

by:WebRules
ID: 35217482
hey do i have to modify this line?
td_datetimestamp between coalesce(nullif('''+isnull(convert(varchar(23),@fromDate,121),'''')+''',''''),td_datetimestamp) and coalesce(nullif('''+isnull(convert(varchar(23),@toDate,121),'''')+''',''''),td_datetimestamp)

because it is pulling out thw wrong dates in the webapp.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35217528
No but keep in mind if the fromdate and todate are exactly the same then you will only find something if there are transaction for that exact date in the table. Beside that the date in your tests has no time component,(is 00:00:00.000), so it will look for transactions at midnight. So if the dates td_datetimestamp have a time component as well in order to find transactions for a day you should use:

from date  2010-05-25 00:00:00.000
to date      2010-05-25 23:59:59.999
0
 

Author Comment

by:WebRules
ID: 35217537
ignore my above comment.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35230920
Can I ask you something?

You never gave points to any of your questions so far even though they are opened for a long time now. I am referring especially to the ones with your old account. The month will end soon and I need those points to be counted against March, so please go in with your old account and just give the points before 1st of April.

Thanks.
0
 

Author Comment

by:WebRules
ID: 35232998
I do not have access to my old account. How do I assign points but? I would like to give points to his one.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35234590
How come you don't have access? When you create an account it never expires and you should still be able to see your own questions.

Maybe this is a question for the admins.
0
 

Author Comment

by:WebRules
ID: 35242956
That was a trial account. My old one.
0
 

Author Comment

by:WebRules
ID: 35282268
Hi Zbertoc,
Can you look at this question please? modification to the other procedure we worked on earlier.

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_26925183.html
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35285361
Web rules, no offense but you did exactly the same with your first account. I don't care if the admins will notify me but they don't know the details.

You open a question than link to a different question, related, you don't give any points and now you lost access to your old account and I spent hours in endless posts only to help with that problem. And this question  is about the same stored procedure.

Please make order in your question this time and don't open new ones until you finish the old ones.

Did our answers work for you or not?
0
 

Author Comment

by:WebRules
ID: 35318642
@Zberteoc:
I really don;t know what you are saying. I told you I lost access to my old account as it was a trial account. Later I purchased this account. And I am not aware of the method of assigning points, if I knew I would have given you the max.
And about repeating the questions, I created a new one as I have no access to the old one.
Please understand.
Your answers helped a lot, I agree and I thank you for that. If you tell me how to assign points, I will assign to the ones you helped me out with this account.
Thanks,.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35318882
:o)

But that is what you are supposed to do after you get an answer that works for you. Everybody does that and that is why we spend time answering in the first place, for the points. :o)

We don't get paid or anything, we only get points. I regards to how to assign the points there should be a button somewhere for that. I can't tell out t of my mind right now but there is also help you can check.
0
 

Author Comment

by:WebRules
ID: 35336100
I assigned you point wherever I am able to. Did you check?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35336617
I don't have to check. If you do that I will get notified through email. So far nothing.
0
 

Author Comment

by:WebRules
ID: 35336877
I did assign you 500 points.
Can you please look at this question?

http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_26932887.html
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35344684
The question you pointed to was not assigned any points yet. If that is not the question you say you assigned the points then please point me to the one where the points were assigned.

So far I have not received any points from you.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35344687
See my comment above.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35344909
I was under the impression that for some reason  the author asked for closing the question without granting any points. What I don't understand is why WebRules won't simply assign the points himself if the question was answered and why does the question need to be applied an automatic procedure for that.

I have never met this situation in years of EE. WebRules, assign the points already.

Admins, please help WebRules to understand how he can do that.
0
 

Expert Comment

by:ModCorlEEone
ID: 35372981
Restarting auto-close process on WebRules's behalf.
 
ModCorlEEone
Community Support Moderator
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now