looping through table to exec an SP many times

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

looping through table to exec an SP many times

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-12-31 : 13:34:02

Hi,
If i have an SP called mySP that accepts one parameter @param
If I have a table of paramaters with only one column like this:
Param1
Param2
..
ParamN

How do I do if I want to execute the SP on all the table fields:
some thing like this:
Exec my SP 'Param1'
Exec mySP 'Param2'
...
Exec mySP 'ParamN'
I want that automatically since the parameters are going to be in a table called myTblParams
Notice that I don t want to pass all the parameters to the SP just once but only one value each time I execute the SP since mySP ccepts only one parameter.

Thanks a lot for guidelines

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-31 : 14:40:16

something like this....

declare @currentparam varchar(100), @maxparam varchar(100)

select @currentparam = min(param), @maxparam = max(param) from <YourTableNameHere>

do while @currentparam <= @maxparam
begin
exec mysp @currentparam

select @currentparam = min(param) from <YourTableNameHere> where param > @currentparam
end

Peter Larsson
Helsingborg, Sweden

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-12-31 : 19:38:42

param field is not numeric. is that a problem for the code suggested above

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-31 : 20:12:06

No.
If you read the code carefully, you would notice that param variables are declared VARCHAR.

There is one caveat though. If there are duplicates of one parameter, only one is caught and run.

Peter Larsson
Helsingborg, Sweden

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-12-31 : 20:20:31

man here is the SP and then I run your code, but i got no results. But when I run just the SP I get results
USE [mozaikDB]
GO
/****** Object: StoredProcedure [dbo].[sp_FindText] Script Date: 12/31/2006 17:22:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_FindText] @text varchar(8000), @findtype varchar(1)='P'
AS
SET NOCOUNT ON
IF @findtype='P'
SELECT DISTINCT Len(SubString(text,1, PatIndex('%' + @text + '%', text)))-Len(Replace(SubString(text,1, PatIndex
('%' + @text + '%', text)),char(13),''))+1 AS Line,
PatIndex('%' + @text + '%', text) AS Position,
OBJECT_NAME(id) AS ProcName
FROM dbo.syscomments
WHERE text like '%' + @text + '%'
ORDER BY ProcName, Line

then I run your code
declare @currentparam varchar(100), @maxparam varchar(100)

select @currentparam = min(FileName), @maxparam = max(FileName) from mozaikDB.dbo.filelist

do while @currentparam <= @maxparam
begin
exec dbo.sp_FindText @currentparam

select @currentparam = min(FileName) from mozaikDB.dbo.filelist where FileName > @currentparam
end

i got nothing although in my table i have a field value of 'isvirtual' and that values appears i one of the SPs. Actually the code of the SP aboves does a search of a given text in all the SPs of the current DB.
If I run the SP alone with the input 'isvirtual' I get results but when runing your code I get nothing any explanantion pls.
Thanks a lot.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-31 : 23:22:21

do While?

It is only while
Effect of Front end loop?

Madhivanan

Failing to plan is Planning to fail

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-01 : 03:01:15

sorry Mr madhivanan
can u explain pls

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-01 : 05:23:35

What I meant is in the suggested code do is not neccessary. You need only while and not do while which is front end syntax

Madhivanan

Failing to plan is Planning to fail

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-02 : 01:09:07

I think I know why the code didn t run. Just because the value of the row 'isvirtual' included extra spaces like this: 'isvirtual '
I ll try the code again.
Tell me, is there a way to combine both codes in the same SP or function or so instead of spliting the search in one SP and the execution of the SP for a table of values in a second SP.
Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources