>>>Thanks! But that's what I do now. I am thinking of its equivalent SQLServer SP syntax and see if it has an advantage when it comes to speed.
>>
>>You could use CURSOR, but keep in mind that cursors are performance killers. Check DECLARE CURSOR and FETCH commands in BOL
>>There is another way but it depends of what you want to do and how your Data is organized.
>
>I already have a separate data layer using MM.Net business objects but I want to try an SP execution for comparison. But if you're saying that the SP method kills performance, it only defeats the purpose.

within your sp create a fake cursor. Below is one to grant rights to all my views for a webuser.


set nocount on

SELECT
'GRANT SELECT ON '
+ table_schema + '.' + table_name
+ ' TO [PEwebUser]' name
into #t
FROM
information_schema.views



declare @name varchar(200), @count int
set @count = (select count(*) from #t)

-- Start of fake cursor here:
while @count >0
begin

set @name = (select top 1 name from #t)
print @name -- view text for verification only

-- Do what is needed
exec( @name )

-- Remove the row
delete #t where name = @name

-- Reset the while flag if necessary
set @count = (select count(*) from #t)
end