Hi SQL Experts ,
i want to store dynamic expression query in table ., then i need to call one by one.
use tempdb
-- cc_model_range_2_1 table :
create table cc_model_range_2_1 (id int identity(1,1),cc_key varchar(50),country_code varchar(2),Created_Date date default getdate())
insert into cc_model_range_2_1 (cc_key ,country_code,Created_Date) values ('67472468f9719675670bf91d816ec023','es','2016-09-22')
insert into cc_model_range_2_1 (cc_key ,country_code,Created_Date) values ('67472468f9719675670bf91d816ec023','es','2016-09-22')
insert into cc_model_range_2_1 (cc_key ,country_code,Created_Date) values ('67472468f9719675670bf91d816ec023','in','2016-09-22')
select * from cc_model_range_2_1
-- table Temp1:
CREATE TABLE [dbo].[temp1](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Model] varchar NULL,
[validation_id] [int] NULL,
[rows_affected] [bigint] NULL,
[status] varchar NULL,
[validated_date] [datetime] default getdate()
) ON [PRIMARY]
-- table temp2:
CREATE TABLE [dbo].[temp2](
[id] [int] IDENTITY(1,1) NOT NULL,
[Validation] varchar NOT NULL,
[created_date] [datetime] default getdate(),
[validation_query] nvarchar NULL
) ON [PRIMARY]
insert into temp2 (validation) values('Duplicate Model in 2_1'),('Invalid Country Code in 2_1')
update temp2 set validation_query = '
Anandselect cc_key ,Anand+ cast(@min_id as varchar(5)) + Anand,count(cc_key)cnt,AnandAnandFAILAnandAnand from cc_model_range_2_1 group by cc_key , created_date
having created_date between AnandAnandAnand+cast(@from_dt as varchar(10))+AnandAnandAnand and AnandAnandAnand+cast(@expt_dt as varchar(10))+AnandAnandAnand
and cc_key = AnandAnandAnand+@cc_key+AnandAnandAnand and count(cc_key) > 1Anand' where id = 1
update temp2 set validation_query = '
Anandselect cc_key,Anand+ @min_id +Anand,count(*)cnt,AnandAnandFAILAnandAnand from cc_model_range_2_1
where created_date between AnandAnandAnand+cast(@from_dt as varchar(10))+AnandAnandAnand and AnandAnandAnand+cast(@expt_dt as varchar(10))+AnandAnandAnand
and cc_key = AnandAnandAnand+@cc_key+AnandAnandAnand and country_code <> AnandAnandesAnandAnand or isnull(country_code ,AnandAnandAnandAnand)= AnandAnandAnandAnandAnand
'where id = 2
update temp2 set validation_query = replace(validation_query,'Anand','''')
select * from cc_model_range_2_1
select * from temp1
select * from temp2
-- Dynamic SP : (code is seems to be ok if i run this print statement speratly it is working well)
declare @min_id int = (select MIN(id) from temp2)
declare @max_id int = (select MAX(id) from temp2)
declare @cc_key varchar(50) = '67472468f9719675670bf91d816ec023'
declare @from_dt date = '2016-09-22'
declare @expt_dt date = '2016-09-22'
declare @validation_query varchar(max)
declare @SQL varchar(max)
while (@min_id <= @max_id )
begin
set @validation_query = (select Validation_query from temp2 where ID = @min_id)
set @sql = 'insert into temp1 (Model,validation_id,rows_affected,[status]) '+ @validation_query
print @validation_query
-- exec @validation_query
print @sql
-- exec (@sql)
set @min_id = @min_id+1
end
-- dynamic sp (while executing there is an error was display)
declare @min_id int = (select MIN(id) from temp2)
declare @max_id int = (select MAX(id) from temp2)
declare @cc_key varchar(50) = '67472468f9719675670bf91d816ec023'
declare @from_dt date = '2016-09-22'
declare @expt_dt date = '2016-09-22'
declare @validation_query varchar(max)
declare @SQL varchar(max)
while (@min_id <= @max_id )
begin
set @validation_query = (select Validation_query from temp2 where ID = @min_id)
set @sql = 'insert into temp1 (Model,validation_id,rows_affected,[status]) '+ @validation_query
-- print @validation_query
exec @validation_query
-- print @sql
exec (@sql)
set @min_id = @min_id+1
end
select * from temp1
-- expected output is :
insert into temp1 (Model,validation_id,rows_affected,[status])
'select cc_key ,'+ cast(@min_id as varchar(5)) + ',count(cc_key)cnt,''FAIL'' from cc_model_range_2_1 group by cc_key , created_date
having created_date between '''+cast(@from_dt as varchar(10))+''' and '''+cast(@expt_dt as varchar(10))+'''
and cc_key = '''+@cc_key+''' and count(cc_key) > 1'
-- hot code format (for understanding):
insert into temp1 (Model,validation_id,rows_affected,[status])
select cc_key ,1,count(cc_key)cnt,'FAIL' from cc_model_range_2_1 group by cc_key , created_date
having created_date between '2016-09-22' and '2016-09-22' and cc_key = '67472468f9719675670bf91d816ec023' and count(cc_key) > 1
insert into temp1 (Model,validation_id,rows_affected,[status])
select cc_key,2,count(country_code)cnt,'FAIL' from cc_model_range_2_1 group by cc_key , country_code ,created_date
having created_date between '2016-09-22' and '2016-09-22' and cc_key = '67472468f9719675670bf91d816ec023' and country_code <> 'es' or isnull(country_code ,'')= ''
select * from temp1
select * from temp2
