Click here to Skip to main content
15,353,432 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I'm having Range table with values
Eg:
Sno Start End
1 1 25000
2 25001 49999
3 50000 74990
4 74991 99985

I'm using dynamic query to Execute stored procedure based on range table values.
Eg:
select 'Exec MainScript '''+convert(varchar(10),Eomonth(GETDATE()-1),120)+''','''+convert(varchar(20),Sno)+''','+convert(varchar(20),Start)+','+convert(varchar(20),End)+''
from Range_table order by Sno


Result will be
Exec MainScript '2022-03-31','1',1,25000
Exec MainScript '2022-03-31','2',25001,49999
Exec MainScript '2022-03-31','3',50000,74990
Exec MainScript '2022-03-31','4',74991,99985


My requirement is to automatically run output query without copy and paste in another query window to get the desired output

What I have tried:

CREATE TYPE TableType AS TABLE(QueryList nvarchar(500))

CREATE PROCEDURE spInsert @QueryList TableType READONLY
AS
BEGIN
select * from QueryTab
END

DECLARE @QueryList TableType
EXECUTE spInsert @QueryList
Posted
Updated 11-Mar-22 6:08am

1 solution

This is one instance where a cursor[^] is probably the best option.
SQL
DECLARE @d date = EOMonth(DateAdd(day, -1, GetDate()));
DECLARE @SNo varchar(20), @Start int, @End int;

DECLARE range_cursor CURSOR
    LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR SELECT SNo, [Start], [End] FROM Range_Table ORDER BY SNo;

OPEN range_cursor;

FETCH NEXT range_cursor
INTO @SNo, @Start, @End;

WHILE @@FETCH_STATUS = 0 
BEGIN
    EXEC MainScript @d, @SNo, @Start, @End;
    
    FETCH NEXT range_cursor
    INTO @SNo, @Start, @End;
END;

CLOSE range_cursor;
DEALLOCATE range_cursor;
   
Comments
Maciej Los 11-Mar-22 12:48pm
   
How you did understand this question - i have no idea...
5ed!
kirthiga S 14-Mar-22 2:22am
   
Cursor code perfectly working for my scenario. Is there any other option other then cursor.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900