Execute stored procedure for each value returned in a query

I have a stored procedure that I am trying to execute passing it each value
returned in a query, but I cannot figure how to do it. I am trying to use
sp_executesql. Here is an example
Query:
(select distinct cust from customer_tbl)
Results:
Cust
--
Cust1
Cust2
Cust3
Cust4
Problem:
I need to execute a stored procedure for each Customer returned passing it
the Cust value. (SP_getsales @.cust='Cust1').
I cannot figure out how to do this. I can generate a script with
sp_executesql, but I cannot simply run the output directly back into isql or
sqlcmd as input. Perhaps I need to rewrite my procedure, but I cannot figur
e
it out.
Thanks,
--
JasonJasonDWilson wrote:
> I have a stored procedure that I am trying to execute passing it each valu
e
> returned in a query, but I cannot figure how to do it. I am trying to use
> sp_executesql. Here is an example
> Query:
> (select distinct cust from customer_tbl)
> Results:
> Cust
> --
> Cust1
> Cust2
> Cust3
> Cust4
> Problem:
> I need to execute a stored procedure for each Customer returned passing
it
> the Cust value. (SP_getsales @.cust='Cust1').
> I cannot figure out how to do this. I can generate a script with
> sp_executesql, but I cannot simply run the output directly back into isql
or
> sqlcmd as input. Perhaps I need to rewrite my procedure, but I cannot fig
ure
> it out.
>
If rewriting the procedure is an option and if the procedure just
executes some data manipulation code based on a parameter then just
substitute that parameter with a JOIN or IN operation (join to
customer_tbl in other words).
If you need help, please post DDL, sample data, required results.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I would ask you to take a note before you read this example - try just
about ANY other option before using these, but in this case, it looks
like you would want a cursor:
DECLARE results cursor
FOR SELECT DISTINCT CUST FROM Customer_tbl
OPEN results
DECLARE @.CurrRow varchar(50)
FETCH NEXT FROM Results INTO @.CurrRow
WHILE @.@.FETCH_STATUS = 0
BEGIN
exec sp_getsales @.cust = @.CurrRow
FETCH NEXT FROM Results INTO @.CurrRow
END
close results
DEALLOCATE Results
Cheers
Will|||Use a cursor...
declare blah cursor for
select distinct cust
from customer_tbl
declare @.cust varchar(50)
open blah
fetch next from blah into @.cust
while @.@.fetch_status = 0
begin
exec sp_getsales @.cust = @.cust
fetch next from blah into @.cust
end
deallocate blah
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:45C58B26-57BB-4C6B-B0D1-D73473A970C1@.microsoft.com...
>I have a stored procedure that I am trying to execute passing it each value
> returned in a query, but I cannot figure how to do it. I am trying to use
> sp_executesql. Here is an example
> Query:
> (select distinct cust from customer_tbl)
> Results:
> Cust
> --
> Cust1
> Cust2
> Cust3
> Cust4
> Problem:
> I need to execute a stored procedure for each Customer returned passing
> it
> the Cust value. (SP_getsales @.cust='Cust1').
> I cannot figure out how to do this. I can generate a script with
> sp_executesql, but I cannot simply run the output directly back into isql
> or
> sqlcmd as input. Perhaps I need to rewrite my procedure, but I cannot
> figure
> it out.
> Thanks,
> --
> Jason|||try this
xp_execresultset 'select distinct ''SP_getsales @.cust= '' + cast(Cust1 as
varchar(20)) from customer_tbl'
hope this helps
--
"JasonDWilson" wrote:

> I have a stored procedure that I am trying to execute passing it each valu
e
> returned in a query, but I cannot figure how to do it. I am trying to use
> sp_executesql. Here is an example
> Query:
> (select distinct cust from customer_tbl)
> Results:
> Cust
> --
> Cust1
> Cust2
> Cust3
> Cust4
> Problem:
> I need to execute a stored procedure for each Customer returned passing
it
> the Cust value. (SP_getsales @.cust='Cust1').
> I cannot figure out how to do this. I can generate a script with
> sp_executesql, but I cannot simply run the output directly back into isql
or
> sqlcmd as input. Perhaps I need to rewrite my procedure, but I cannot fig
ure
> it out.
> Thanks,
> --
> Jason|||also, don't prefix your stored procedures sp_
read books online for reasons not to do this.|||Is this 2005 only?
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:6FBD5D4E-B4E2-4ED3-AB4E-FCEC9406990B@.microsoft.com...
> try this
> xp_execresultset 'select distinct ''SP_getsales @.cust= '' + cast(Cust1 as
> varchar(20)) from customer_tbl'
> hope this helps
> --
>
>
> "JasonDWilson" wrote:
>
value
use
passing it
isql or
figure|||small change :)
exec master..xp_execresultset 'select distinct ''SP_getsales @.cust= '' +
cast(Cust1 as varchar(20)) from customer_tbl', <database_name>
database name should be in single quotes
hope this helps
--
"Omnibuzz" wrote:
> try this
> xp_execresultset 'select distinct ''SP_getsales @.cust= '' + cast(Cust1 as
> varchar(20)) from customer_tbl'
> hope this helps
> --
>
>
> "JasonDWilson" wrote:
>|||no its there in 2000. But it doesn't appear in the master database. its
undocumented and hidden :)
And after SP4, its no longer an extended stored proc, it just calls
sp_execresultset
--
"Jim Underwood" wrote:

> Is this 2005 only?
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:6FBD5D4E-B4E2-4ED3-AB4E-FCEC9406990B@.microsoft.com...
> value
> use
> passing it
> isql or
> figure
>
>|||One note of caution. Don't use this proc unless you definitely have to. Cos
its undocumented. Its a definite no-no if you are planning to deploy it in
production. Typical use is for granting permissions to a set of users.
I would say its still better to use cursors if no one else comes with a
better solution.
But my suggestion would be to incorporate the looping logic inside the
stored procedure.
Hope this helps.
--
"Omnibuzz" wrote:
> small change :)
>
> exec master..xp_execresultset 'select distinct ''SP_getsales @.cust= '' +
> cast(Cust1 as varchar(20)) from customer_tbl', <database_name>
> database name should be in single quotes
> hope this helps
> --
>
>
> "Omnibuzz" wrote:
>