Executing a procedure in a loop?

Transact-SQL
1

Hi there, how do I run the below: Any suggestions? Loop? Cursor? A sample code would be great.

select [FROM], [TO], [Date] FROM dbo.[table] --(QuoteNumber int,
From varchar(10), To varchar(10), Date datetime)
group by quotenumber,[Date], [FROM], [TO]

--I need the procedure below to run for each quotenumber individually with the given parameters
Exec dbo.csp_TestProcedure @From, @To, @Date
GO
So basically the procedure should run for each unique QuoteNumber. In the example below, QuoteNo 1 runs 2 times for the 2 rows represented and then 2, 7, 8 and 11.
image

2

Eyob,

Please always provide dml and ddl in this fashion?

create table #sampledata(QuoteNumber int, FromCity varchar(10), ToCity varchar(10), Date datetime)

insert into #sampledata
select 1, 'DC', 'LA', '2018-09-01 8:00'

select * from #sampledata

drop table #sampledata

So please provide the full source data set this way ^^
then show us the final result you would like to see

Thanks

3

ok, so let's use this as the dataset to work with:
create table #sampledata(QuoteNumber int, FromCity varchar(10), ToCity varchar(10), Date datetime)

insert into #sampledata
select 1, 'DC', 'LA', '2018-09-01 8:00'
select 1, 'LA', 'MA', '9/4/2018 10:00'
select 2, 'SB', 'CH', '9/1/2018 8:00'
select 2, 'CH', ',SB', '9/4/2018 10:00'
select 7, 'BS', 'SA', '9/1/2018 8:00'
select 7, 'SA', 'BS', '9/4/2018 10:00'
select 8, 'LX', 'VA', '9/1/2018 8:00'
select 8, 'VA', 'LX', '9/4/2018 10:00'
select 11, 'IS', 'WY', '9/1/2018 8:00'
select 11, 'WY', 'QA', '9/4/2018 10:00'
select 11, 'QA', 'IS', '9/5/2018 10:00'

select * from #sampledata

Now, I want to pull FromCity, ToCity and Date columns for each QuoteNumber and run it in a stored procedure that has a UserDefinedTableType as a parameter:

The UserDefinedTableType looks like this:

CREATE TYPE [dbo].[UserDefinedTableType] AS TABLE(
[FromCity] varchar NULL,
[ToCity] varchar NULL,
[Date] NULL
)

Declare @UserDefinedTableType As dbo.UserDefinedTableType
Insert Into @UserDefinedTableType
Select FromCity, ToCity, Date
From #sampledata
where QuoteNumber = 1 --This should loop into the table's QuoteNumber column and should run for 2, 7, 8 and 11 independently.

--And the stored procedure looks like this:
Exec dbo.csp_TestProcedure @UserDefinedTableType

drop table #sampledata

How do I do this?

Thanks for your help!

4

gobez ayeh ahun tiyakeh arif new, anbessa!

why should it run for 2,7,8 and 11? what does the mean? what is the relationship of those rows to QuoteNumber 1?

5

Oh, I was trying to be as explicit as possible. Those numbers are extracted from #sampledata and they represent the remaining quotenumbers from the quotenumber column. So, I want the stored procedure to run for QuoteNumber 1 first and then loop back to find the next number in line (2, 7,8 and 11.....)

When the procedure runs for QuoteNumber = 1, the user defined table (@UserDefinedTableType) should have these rows:
'DC', 'LA', '2018-09-01 8:00'
'LA', 'MA', '9/4/2018 10:00'

And when the procedure runs for QuoteNumber = 11, @UserDefinedTableType should have the following rows in it:
'IS', 'WY', '9/1/2018 8:00'
'WY', 'QA', '9/4/2018 10:00'
'QA', 'IS', '9/5/2018 10:00'

6

Why do you need to run the code per quote? You can already get the data for all quotes - so why run individually for each quote?

I see no reason to use a cursor here.

7

Hi Jeff, I am trying to recreate all the quotes again. So, it's intentional.

8

I understand it is intentional - I still don't understand the requirement. What is in the stored procedure that requires it to be run for each quote?

Wouldn't it be better to create the stored procedure to do everything you need for the full set of data?

If you must run the procedure once for each row - then use a cursor...but it would be much easier and faster to perform the work as a set.

9

It's a must that I run the procedure for each quote because the result I am expecting pretty much depends on the date and time factor and the sequence of rows in each quote. And the procedure creates another quotenumber for me that I could compare with previously created quotes. The purpose of all of these is to trying come up with a testing script where I would reproduce the quotes and compare them with previous results. I have an application that could do it individually but it will take forever to do it that way.

10

no idea what you are attempting here but take a look at this fetch/loop

DECLARE @LastName varchar(50), @FirstName varchar(50);  
create table #sampledata(QuoteNumber int, 
FromCity varchar(10), ToCity varchar(10), QuoteDate datetime)

insert into #sampledata
select 1, 'DC', 'LA', '2018-09-01 8:00'
select 1, 'LA', 'MA',	'9/4/2018 10:00'
select 2,	'SB',	'CH',	'9/1/2018 8:00'
select 2,	'CH',	',SB',	'9/4/2018 10:00'
select 7,	'BS',	'SA',	'9/1/2018 8:00'
select 7,	'SA',	'BS',	'9/4/2018 10:00'
select 8,	'LX',	'VA',	'9/1/2018 8:00'
select 8,	'VA',	'LX',	'9/4/2018 10:00'
select 11,	'IS',	'WY',	'9/1/2018 8:00'
select 11,	'WY',	'QA',	'9/4/2018 10:00'
select 11,	'QA',	'IS',	'9/5/2018 10:00'

declare @QuoteNumber int

DECLARE quote_cursor CURSOR FOR  
SELECT QuoteNumber 
  FROM #sampledata   
ORDER BY QuoteDate ;  

OPEN quote_cursor;  

-- Perform the first fetch and store the values in variables.  
-- Note: The variables are in the same order as the columns  
-- in the SELECT statement.   

FETCH NEXT FROM quote_cursor  
INTO @QuoteNumber;  

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.  
WHILE @@FETCH_STATUS = 0  
BEGIN  

   -- Concatenate and display the current values in the variables.  
   PRINT 'Contact Name: ' + @QuoteNumber 

   -- This is executed as long as the previous fetch succeeds.  
   FETCH NEXT FROM quote_cursor  
   INTO @QuoteNumber;  
END  

CLOSE quote_cursor;  
DEALLOCATE quote_cursor;  
GO
1 Like
11

hi

maybe

CROSS APPLY can help

or

SUBQUERY
with join

Instead of PROCEDURE

12

I think this does it for me. Passing in the quotenumber and grabbing the details within the cursor per quotenumber works. Thanks a lot!

13

Thanks Harish! Good ole cursor did it!:slight_smile: