CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
Cogslave
Cogslave

Reputation: 2643

Can you set a stored procedures parameters from the output of a query?

I have a stored procedure that generates a list of product sequence numbers.

exec GenerateSequenceList @Min = 10, @Max = 25, @Prefix = N'Toaster'

I have to now generate a list of sequences for a report. Is there a way I can feed this stored procedure from the output of a query? For example generate a sequence list for all toasters, ladles and pans.

select min, max, prefix from products where prefix in ('Toaster', 'Ladle', 'Pan')

Upvotes: 1

Views: 81

Answers (1)

gbn
gbn

Reputation: 432421

In a loop, yes. Once per row and you'd have multiple resultsets (one stored proc output repeated per loop iteration

The best way is using APPLY and udfs if you can re-write to use UDFs

select P.prefix , T.* 
from 
   products P
   CROSS APPLY -- or OUTER APPLY maybe
   dbo.SomeUDF (P.min, P.max, P.prefix) T
where 
   P.prefix in ('Toaster', 'Ladle', 'Pan')

The udf can be called in the stored procedure of course to maintain the "API"

Upvotes: 4

Related Questions