SQLTeam.com | Weblogs | Forums

Execcute Expression from table

sql2008

#1

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


#2

assign all of your dynamic queries to a @sqlcommand variable and then do
PRINT (@sqlcommand)

guaranteed cure :slight_smile:


#3

you cannot insert into from the following line

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'

you need to do (I would avoid dynamic script like plague in the case where it can be exploited by sql injection)

set @lunchtime = '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'

exec (@lunchtime)