Calling a stored procedure and pass parameter one by one

Hi,

I need to create a batch process which calls a stored procedure.

Here's the scenario.

I have 3 tables

Theater - TheaterId, TheaterName, Revenues,locationid, stateid
State - StateId, StateName
Location - LocationId, LocationName, StateId

There is a stored procedure spoc_updateTheater that accepts the state and location id and runs a set of sql statements against the theater table. However i want this to run for all the locations in a state one by one. There are some 700 locations in 45 states. How do i pass the location and state id one by one to the stored proc. Can i call this from a commandline or run it as batch process?

vidkshi

You will have iterate through the filtered data of the first procedure and execute the procedure one by one, the is no command to do this you will have to do that manually. Would look like the following pseudo code.

TableContent = YouSelectquery

Loop throught the data and pick one row per iteration
{

Execute your procedure with the values from the picked rows.

}

You can either do this using a cursor (preferable fast forward / read ony if you do not want to change anything) or use a tamp table.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi,

I will appreciate if someone could tell me the sql for it :

Here's the Theater table and the data. I want to loop through every state and location in the state/location tables and pass that stateid locationid to the stored proc.

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Theater]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Theater](
[TheaterId] [int] NULL,
[TheaterName] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Revenues] [bigint] NULL,
[StateId] [int] NULL,
[LocationId] [int] NULL
)
END
GO
INSERT [dbo].[Theater] ([TheaterId], [TheaterName], [Revenues], [StateId], [LocationId]) VALUES (1, N'T1 ', 10000, 1, 1)
INSERT [dbo].[Theater] ([TheaterId], [TheaterName], [Revenues], [StateId], [LocationId]) VALUES (2, N'T2 ', 2333, 1, 1)
INSERT [dbo].[Theater] ([TheaterId], [TheaterName], [Revenues], [StateId], [LocationId]) VALUES (3, N'T3 ', 234234, 1, 1)
INSERT [dbo].[Theater] ([TheaterId], [TheaterName], [Revenues], [StateId], [LocationId]) VALUES (4, N'T4 ', 43454, 1, 2)
INSERT [dbo].[Theater] ([TheaterId], [TheaterName], [Revenues], [StateId], [LocationId]) VALUES (5, N'T5 ', 4454, 1, 2)
INSERT [dbo].[Theater] ([TheaterId], [TheaterName], [Revenues], [StateId], [LocationId]) VALUES (6, N'T6 ', 11132, 2, 1)
INSERT [dbo].[Theater] ([TheaterId], [TheaterName], [Revenues], [StateId], [LocationId]) VALUES (7, N'T7 ', 345, 2, 1)
INSERT [dbo].[Theater] ([TheaterId], [TheaterName], [Revenues], [StateId], [LocationId]) VALUES (8, N'T8 ', 675445, 2, 2)
INSERT [dbo].[Theater] ([TheaterId], [TheaterName], [Revenues], [StateId], [LocationId]) VALUES (9, N'T9 ', 344556, 3, 1)
INSERT [dbo].[Theater] ([TheaterId], [TheaterName], [Revenues], [StateId], [LocationId]) VALUES (10, N'T10 ', 23234, 3, 1)

vidkshi|||

What about:

Code Snippet

CREATE PROCEDURE spUpdateTheater

/*

Written by Jens K. Suessmeyer (c) http://www.sqlserver2005.de

*/

AS

BEGIN

SET NOCOUNT ON

DECLARE @.TheaterData Table

(

COUNTER INT IDENTITY(1,1),

Theaterid INT NULL,

StateId INT NULL

)

DECLARE @.ROWCOUNT INT

DECLARE @.I INT

SET @.I = 1

DECLARE @.Theaterid INT

DECLARE @.StateId INT

INSERT INTO @.TheaterData

(

Theaterid,

StateId

)

SELECT DISTINCT

TheaterId,

Stateid

FROM Theather

SET @.ROWCOUNT = @.@.ROWCOUNT

WHILE @.I <= @.ROWCOUNT

BEGIN

SELECT

@.Theaterid = TheaterId,

@.StateId = Stateid

FROM @.TheaterData

WHERE COUNTER = @.ROWCOUNT

EXEC spExecuteYourUpdateProcedure @.Theaterid,@.StateId

SET @.I = @.I +1

END

END

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi,

Thanks for the code. I was wondering why you added the theater id and stateid in the temp table whereas it had to be stateid and locationid. Anyways i changd your query to look like this. I also tried running your stored proc as is but with no results.

Code Snippet

ALTER PROCEDURE [dbo].[spUpdateTheater]
/*

Written by Jens K. Suessmeyer (c) http://www.sqlserver2005.de

*/

AS

BEGIN

SET NOCOUNT ON

DECLARE @.TheaterData Table

(

COUNTER INT IDENTITY(1,1),

Locationid INT NULL,

StateId INT NULL

)

DECLARE @.ROWCOUNT INT

DECLARE @.I INT

SET @.I = 1

DECLARE @.Locationid INT

DECLARE @.StateId INT

INSERT INTO @.TheaterData

(

Locationid,

StateId

)

SELECT DISTINCT

LocationId,

Stateid

FROM Theater

select * from @.TheaterData

SET @.ROWCOUNT = @.@.ROWCOUNT

WHILE @.I <= @.ROWCOUNT

BEGIN

SELECT

@.Locationid = LocationId,

@.StateId = Stateid

FROM @.TheaterData

WHERE COUNTER = @.ROWCOUNT

EXEC spoc_Theater @.StateId, @.Locationid

SET @.I = @.I +1

END

END

My store proc spoc_updatetheater looks like this

Code Snippet

ALTER proc [dbo].[spoc_Theater]
@.stateid int,
@.locid int
as
update theater
set revenues = 20000
where stateid = @.stateid and locationid = @.locid
and revenues > 10000

I tried running it however I see there is no change in the Theater table after the query executes.

Any reasons?

Vids

|||

Yes, you are right and in addition you should change the

Code Snippet

WHERE COUNTER = @.ROWCOUNT

to

Code Snippet

WHERE COUNTER = @.I

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Cool. thanks.

you also mentioned that the same query could be written using a cursor. What advantage will i get.

In this query we are creating a temporary table, filling data and looping through it. I expect around 5000 different combinations of state and location id in this temporary table. Will it be ok from a performance point to use the temporary table?

Cant i just use the location table as it will contain a distinct location and state combination. So instead of the temp table, i just loop through the location table and execute the proc for each combination of location and state. How will we do this using a cursor and how will it improve performance?

Vidkshi.

|||

I guess you won′t recognize the difference with 5000 rows. You can sure switch over to the locations tables.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||I don't have an answer to your question but you might want to consider removing the stateid from your Theater table as it violates 3rd normal form.

Theater - TheaterId, TheaterName, Revenues,locationid, stateid
State - StateId, StateName
Location - LocationId, LocationName, StateId