• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

How to use a while in a stored procedure?

Hi
 I have the next code that I want to add in a sp to run in a job, what I mainly want to do is to get some records where an ID is equal to something

select distinct SMRR_ID from CR_Units where
Status_ID=2

I am supposed to get different IDs such as 2 and 4

Then I need to navigate through the results of the query and link it with another query that needs to run for each ID obtained

declare @To nvarchar(4000)

set @To=''

select @To = @To + b.EmailAddress + ';'
from CR_DistributionList a, SC_Users b
where a.UserID_ID = b.UserID_ID
and a.SMRR_ID = ?????
and b.EmailAddress<>''

print @To

I think I can do it with a while but honestly I have never used it

Hope you can help me
Thanks
0
negreteo
Asked:
negreteo
  • 2
1 Solution
 
adwisemanCommented:
What you want to do is use a Cursor to step 1 record at a time through a recordset, and fire a SP passing parameters from that recordset

Cursors work like this.  The use of cursors is frowned upon if at all possible to get by without.

DECLARE Records CURSOR SCROLL STATIC
READ_ONLY FOR
SELECT Field1, Field 2
FROM Table
WHERE Condition1 AND Condition2

OPEN Records

FETCH NEXT FROM Records
INTO @Field_var1, @Field_Var2
   
IF @@FETCH_STATUS <> 0
BEGIN
     EXEC My_Proc @Field_var1, @Field_var2
END

CLOSE Records
DEALLOCATE Records
0
 
rafranciscoCommented:
Why not join the 2 queries like this:
select distinct SMRR_ID from CR_Units where
Status_ID=2
declare @To nvarchar(4000)

set @To=''

select @To = @To + b.EmailAddress + ';'
from CR_DistributionList a INNER JOIN SC_Users b
ON a.UserID_ID = b.UserID_ID
WHERE EXISTS (SELECT 'X' FROM CR_Units C WHERE a.SMRR_ID = C.SMRR_ID and C.Status_ID = 1)
and b.EmailAddress<>''

print @To
0
 
rafranciscoCommented:
It should be like this:

declare @To nvarchar(4000)

set @To=''

select @To = @To + b.EmailAddress + ';'
from CR_DistributionList a INNER JOIN SC_Users b
ON a.UserID_ID = b.UserID_ID
WHERE EXISTS (SELECT 'X' FROM CR_Units C WHERE a.SMRR_ID = C.SMRR_ID and C.Status_ID = 1)
and b.EmailAddress<>''

print @To
0
 
andrewbleakleyCommented:
try a cursor.
declare @my_id int
declare @To varchar(8000)
declare id_cursor cursor for
select distinct SMRR_ID from CR_Units where
Status_ID=2
open id_cursor
fetch next from id_cursor into
@my_id
while @@FETCH_STATUS = 0
begin
set @To=''
select @To = @To + b.EmailAddress + ';'
from CR_DistributionList a, SC_Users b
where a.UserID_ID = b.UserID_ID
and a.SMRR_ID = @my_id
and b.EmailAddress<>''
print @To
fetch next from id_cursor into
@job
end
close id_cursor
deallocate id_cursor
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now