looping through table to exec an SP many times

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

If I understand you correctly, you want to execute mySP for every row in the myTblParams table. To do this, you could write another sp that has a cursor to loop through the myTblParams rows.

...declare variables, etc...

Declare crsMyTblParams Cursor For
Select param_field_name
from myTblParams

Open crsMyTblParams
Fetch Next From crsMyTblParams Into @.Param

While @.@.Fetch_Status = 0 Begin
Exec mySP @.Param
Fetch Next From crsMyTblParams Into @.Param
End

Close and deallocate the cursor|||

thanks a lot man. looks more complicated than i thought. is that the simplest way to do it

thanks

|||

This is actually very easy and something you do with stored procedures all the time. If you want to iterate through a result and do something for each row in the result, this is the way you do it (using a cursor).

You have another post that mentioned looping through all the database in an instance, and you would basically do the same thing. Capture the results of the exec sp_databases command (in a cursor) and loop through the results executing the select * from information_schema command for each database. You can prefix information_schema with the database name (i.e. select * from pubs.information_schema.tables. You can capture the database name from the cursor (results from the sp_database command) and then replace the database name in the information_schema select statement to find your keywords. In this case, it would be more complicated because you would have nested cursors. One that read your table (with words to search for) and one to loop through all the databases.

Cursors sound complicated when you first start using them (I know I was clueless), but once you do it a few times, they really are relatively simple and very powerful..

|||

u mean this one:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1048114&SiteID=1

cool u saw that:)

|||Can you explain the logic of your SP? Of course, the cursor logic is complicated, slow and hard to maintain. You can just write one SP that does all the operations in a set-based manner. It is hard to suggest the approach though without knowing more details. Please post some sample DDL, data (how the parameters look etc) and the expected results.|||

I am sure u ve already seen this SP. It searches if a given text as parameter occurs if any of the SPs scripts of the current database, and it outputs the occurence of that seaeched text and the line number. Here is the SP:

USE [mozaikDB]

GO

/****** Object: StoredProcedure [dbo].[sp_FindText] Script Date: 12/31/2006 16:54:35 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[sp_FindText] @.text varchar(8000), @.findtype varchar(1)='P', @.DBvar varchar(30) 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 DBvar.dbo.syscomments

WHERE text like '%' + @.text + '%'

ORDER BY ProcName, Line

So this scripts searches within the SPs of the current database but me I want to search withing all the instance databases and not only the current one.

Thanks.

|||Your SP logic is slightly flawed. If a SP definition spans multiple entries and a word gets split then your search will not find it. It is easier to use OBJECT_DEFINTION function and search the nvarchar(max) value directly. This might however be slower but better than a temporary table based approach for example (similar to sp_helptext). Lastly, the technique to find line number may not be accurate also given that lot of constructs are optional in TSQL (like statement separators, CRLFs etc). And if the text occurs multiple times then you will miss it also. So why don't you to just use the find mechanism in your source code control system instead rather than writing your own SP. The find mechanism in other tools are more robust and easier to use.|||

I tried this code as per your suggestion but i didn t get the result, i got nothing returned:

declare crsMyTblParams2 cursor for

declare @.param as varchar(8000)

open crsMyTblParams2

select FileName from mozaikDB.dbo.filelist

fetch next from crsMyTblParams2 into @.param

while @.@.fetch_status=0 begin

exec dbo.sp_FindText @.param

fetch next from crsMyTblParams2 into @.param

end

When i try just:

exec dbo.sp_FindText 'isvirtual'

I get that the searched text: 'isvirtual' exists in one SP at line 10. But when i try the code above, and the table filelist has a row field containing the word: 'isvirtual'

I only get 8 rows with empyty values

any explanantion pls

Thanks

|||

I am not sure what modification you made. Can you post a working sample of your code that demonstrates the issue? If you modified the SP to use OBJECT_DEFINITION then you can just do something like:

select o.name

from sys.objects as o

where o.type = 'P'

and OBJECT_DEFINITION(o.object_id) like '%your_text%'

The above query will return all SPs that contains 'your_text' at least once. And I am not sure what you are doing by passing filename to the SP. Does it use BULK INSERT to read the text from the file? And when I said that you should use the source code control system, I meant the tools that come with it also. For example, if you are using Visual SourceSafe then you can use the find option within the VSS IDE itself. Other source code control systems have similar functionality. You can also use Visual Studio IDE to search through the files.

Lastly, below is a SP that does what you are looking for but the logic uses older technique that will work in any version of SQL Server. You can simplify it in SQL Server 2005.

http://umachandar.com/technical/SQL6x70Scripts/UtilitySPs(6x)/Main11.htm

|||

Sorry, i meant i used the code from DannoCoy mentioned above that uses the cursor to loop through the table where my keywords re located: param1, param2...

i don t use any file

filename is just the name of the table column so that I can get the searched keywords, then i call the SP that seraches the kayword using the cursor methid mentioned by DannoCoy

I haven t modified the code yet to use OBJECT_DEFINITION but i ll do that after everything works first.

Thanks

|||

Here is the SP code I used:

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 the table mozaikDB.dbo.filelist has the values: Param1, Param2,....isVirtual...,PramN

Then I used the script suggested by DannoCoy like this:

declare @.param as varchar(8000)

declare crsMyTblParams cursor for

select FileName from mozaikDB.dbo.filelist

open crsMyTblParams

fetch next from crsMyTblParams into @.param

while @.@.fetch_status=0 begin

exec dbo.sp_FindText @.param

fetch next from crsMyTblParams into @.param

end

The problem:

When I execute just the above SP like this;

exec dbo.sp_FindText 'isvirtual'

I get a result showing that the searched word 'isVirtual' is part of the SP CategorySave_sp in line 10

but when I run the script

declare crsMyTblParams cursor for

......etc

suggested by DannoCoy I get no result

Thanks

|||

R.Tutus, not sure what you are doing, but I created the sp_findtext and a filelist table (and added some character strings to search for), ran the code for the cursor and it worked fine. Question: do you have any rows (strings to search for) in your table mozaikDB.dbo.filelist?

Now that I see what you are attempting to do, I totally agree with Umachandar. The logic is flawed. This is going to produce questionable results at best. I have my name in a proc three different times, and sp_findtext is only indicating the first occurance. It is also returning function names and views, (not just stored procedures).

PS. When you use a cursor, you should finish with

close cursorname
deallocate cursorname

|||

yeah guys u re so right (obviously:) ) .

any way in my seached field in the table filelist i had the word: 'isvirtual ' with many spaces since the field is nvarchar(50) instead of 'isvirtual'. that s why the cursor didn t find it.

when i redefined the column in filelist as varchar(9) instead of 50 the cursor worked perfectly.

Now I am gonna try what our friend Umachandar s suggested and i ll let u know if any difficulty.

Thanks for tutoring me guys:)

|||

1/

Now I replaced the code in my seacrh procedure findText mentioned above with this one suggested by:

Create PROCEDURE [dbo].[sp_FindText2] @.text varchar(8000), @.findtype varchar(1)='P'

AS

SET NOCOUNT ON

IF @.findtype='P'

select @.text, o.name AS ProcName,@.text ,Len(SubString(object_definition(o.object_id),1, PatIndex('%' + @.text + '%', object_definition(o.object_id))))-Len(Replace(SubString(object_definition(o.object_id),1, PatIndex

('%' + @.text + '%', object_definition(o.object_id))),char(13),''))+1 AS Line,

PatIndex('%' + @.text + '%', object_definition(o.object_id)) AS Position

from sys.objects as o

where o.type='P' and object_definition(o.object_id) like '%' + @.text + '%'

ORDER BY ProcName, Line

It s working so far. I understand that this might not generate the desired results in case the searched keyword: @.text occurs many times in the same SP.

Is this fine at least in your opinion. Few more questions pls:

2/ Umachandar suggested: to use OBJECT_DEFINTION function and search the nvarchar(max)value directly. How that fit in my code above and what s the use of nvarchar(max) in this context

3/ also he suggested "You can just write one SP that does all the operations in a set-based manner" which is to use one logic in the original Findtext SP to both search the keyword and loop through the table filelist containing the list of keywords to be searched. He meant that there d be no need to use both one SP called FindText for the search and another SP using cursor to execute the first SP for al the the listFile rows. Would you please be able to suggest a way for doing that.

4/ One last thing, the code above searches only in the SPs of the current Database. I want to customize the code to look up the SPs in all the databases of the current instance and not only the current DB.

Thanks a lot

Thanks