Sql query returns single or multiple records?

Within my stored procedure I execute another stored procedure. Here is an example

SELECT DISTINCT ID 
FROM TABLE 
WHERE NAME IS NOT NULL --CAN RETURN MULTIPLE RECORDS

Then

I need to execute my stored procedure and pass parameters ID from the query above

So if the query above returns two IDs I need to execute the stored procedure below 2 times. The way I think is t开发者_运维百科o create a loop but more than sure it's not the most efficient way.

EXEC StoredProcedureName ID

any ideas?

---

if it's possible for you use function instead stored procedure and put it right in select statment

---

You either need to use a loop (whether it explicitly uses DECLARE CURSOR or not is largely irrelevant), or you need to extract the logic in StoredProcedureName such that it can work on a set instead of a single value at a time.

---

DECLARE @value INT
 DECLARE cur CURSOR FOR SELECT value FROM YourTable
 open cur
 FETCH NEXT FROM cur INTO @value
 WHILE @@FETCH_STATUS = 0
 BEGIN
      exec YourProcedure @value
      exec AnotherProcedure @value
      FETCH NEXT FROM cur INTO @value
 END
 CLOSE cur
 DEALLOCATE Cur

---

Can you alter your stored procedure?

You could perhaps incorporate the

SELECT DISTINCT ID FROM TABLE WHERE NAME IS NOT NULL

query into the stored procedure rather than passing in parameters.

---

You need to create a cursor and loop through it and call you procedure.

For example

DECLARE @ID int
DECLARE some_cursor FAST_FORWARD FOR
SELECT DISTINCT ID FROM TABLE WHERE NAME IS NOT NULL

OPEN some_cursor 
FETCH NEXT FROM some_cursor 
INTO @ID 

WHILE @@FETCH_STATUS = 0
BEGIN

    EXEC StoredProcedureName @ID

    FETCH NEXT FROM some_cursor 
     INTO @ID 


END
CLOSE some_cursor ;
DEALLOCATE some_cursor;

As you'll notice its clumsy to write and its SQL can't optimize what ever StoredProcedureName is doing for more than one input.