question

IDGO-6443 avatar image
0 Votes"
IDGO-6443 asked JingyangLi commented

Need SQL Query which excecutes List of ID's

 declare @rrId uniqueidentifier
 declare @rvId uniqueidentifier
     
 set nocount on
 set @rrId ='<One RRID at a time>'
     
 /* Finding Recognitions of Reportable Result */
 select distinct RecognitionId into #tempRecogs from Recognitions where ReportableResultId = @rrId
     
   
 /* Finding Result Value of the Reportable Result*/
 select @rvId = TestId from ReportableResults where ReportableResultId = @rrId
     
 /* Finding Limits of Result Value of the Reportable Result */
 select distinct TestId  into #tempRL from ResultLimits where ResultValueId = @rvId
     
 /* Finding Converted Result Value of the Reportable Result */
 select distinct TestId into #tempConvRV from  Results where  ResultId = @rrId
     
 /* Finding Converted Result Limit of the Converted Result Value of the Reportable Result */
 select distinct TestId into #tempConvRL from  Limits where  ValueId in (select * from #tempConvRV)
     
 /*Removing ADs that are shared with other reportable results*/
 delete t from 
 #tempAd t join  Results adrr on t.assessmentDataId = adrr.assessmentDataId
 where  ResultId not in(select * from #tempAdrr)
     
 set nocount off
     
 print 'Deletes beginning now'
     
 if exists(select 1 from #tempRecogs)
 begin
        print 'Deleting Recognitions'
        delete from Recognitions where  onId in (select * from #tempRecogs)
 end
     
  
 if exists(select 1 from #tempConvRL)
 begin
        print 'Deleting Converted  Limits'
        delete from  Limits where  LimitId in (select * from #tempConvRL)
 end
     
 if exists(select 1 from #tempConvRV)
 begin
        print 'Deleting Converted Result Values'
        delete from ResultValues where ResultValueId in (select * from #tempConvRV) 
 end
     
 print 'Deleting Result Value'
 delete from  Values where resultValueId = @rvId
     
 /* Drop all Temporary Tables*/
 drop table #tempAdrr  
 drop table #tempRL
 drop table #tempConvRL
 drop table #tempConvRV

Above Code is Working, But Manually we need to change set @rrId ='<One RRID at a time>'


set @rrId ='<One RRID at a time>' this is the Problem Statement where we are manually changing one after another.

Need list of rrId then Excecute one after another.

@rrId =('38F1B368-F65E-4113-987B-A26A28647952','15C2F60C-8757-4194-8993-61993BD0E13F')

Foreach(var variable in rrId)
{
here Existing Querey will run;
}

sql-server-generalsql-server-transact-sqlsql-server-reporting-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RajatGarg-6550 avatar image
0 Votes"
RajatGarg-6550 answered JingyangLi commented

DECLARE @RRIDLIST_Table TABLE (ID INT IDENTITY(1,1), RRID uniqueidentifier);
---insert all your rrid values in table
INSERT INTO @RRIDLIST_Table (RRID) VALUES (........);

DECLARE @maxid INT, @Currid INT, @rrid uniqueidentifier;

SELECT @Currid = MIN(ID), @maxid = MAX(ID) FROM @RRIDLIST_Table;

WHILE @Currid <= @maxid
BEGIN

SELECT @rrid = RRID FROM @RRIDLIST_Table WHERE ID = @Currid;
--use the rrid variable above in your scripts
<your code/scripts here>

SET @Currid = @Currid +1;
END

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @RajatGarg-6550 ...It worked Perfectly

0 Votes 0 ·

This loop pattern is not a good solution but it works.
The better solution should go with SET-based solution and it will make your code better performance.

0 Votes 0 ·
JingyangLi avatar image
0 Votes"
JingyangLi answered IDGO-6443 edited

Working with a SET thinking.
Use a table variable (temp table) or TVP if you need to. It will handle multiple values.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Can you please provide the code for temp table
update the query to declare all the RRID at once to delete it one time,

script as they need to execute the same script multiple times since we are declaring each RRID at a time,



0 Votes 0 ·
untitled.png (76.9 KiB)
 declare @t table   (uid uniqueidentifier)
 insert into @t values('38F1B368-F65E-4113-987B-A26A28647952')
 , ('15C2F60C-8757-4194-8993-61993BD0E13F')

--You can join your tables on this table variable for DELETE.
You may use MERGE to write compact code here as well.


1 Vote 1 ·

Thanks @JingyangLi

0 Votes 0 ·