Dynamic sp reuse from table

• Anand BI Developer

  SSCommitted

  Points: 1772

  More actions

  September 23, 2016 at 1:12 am

  #321683

  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 (idint identity(1,1),cc_key varchar(50),country_code varchar(2),Created_Datedate 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](50) NULL,

  [validation_id] [int] NULL,

  [rows_affected] [bigint] NULL,

  [status] [varchar](50) NULL,

  [validated_date] [datetime] default getdate()

  ) ON [PRIMARY]

  -- table temp2:

  CREATE TABLE [dbo].[temp2](

  [id] [int] IDENTITY(1,1) NOT NULL,

  [Validation] [varchar](50) NOT NULL,

  [created_date] [datetime] default getdate(),

  [validation_query] [nvarchar](max) 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