How to write T-SQL For Each to replace VB.NET

I have a VB.NET service application that gets a list of email addresses based and a T-SQL command.

Using the .NET application, I then loop through each email address passing it as a parameter to an SQL stored procedure that does something based on the email address parameter I pass in.

I created the .NET service application so I could loop through emails and have it run at a specific time.  I have learned since, that I can probably just do this in SQL only.

I would like to eliminate the .NET application.  I would like to just do everything in one SPROC and then schedule a job to run and execute the SPROC.  

Question I have is, can I assign a variable datatype (like an array in VB.NET) and then loop through each variable (for each)?  What T-SQL can I use?
handyjayAsked:
Who is Participating?
 
Ephraim WangoyaCommented:

This should be fine, but just test for 0 not -1

WHILE (@@FETCH_STATUS = 0)
BEGIN
  ........


The one disadvantage I can think of is that cursors are deemed to be slow
0
 
Ephraim WangoyaCommented:

How big is the table?
You could use a cursor to iterate the records
0
 
radcaesarCommented:
Send ur .NEt code which loops to get the IDs.

But You can do in TSQL what every you had done in .NET code,  But in different way.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Rajkumar GsSoftware EngineerCommented:
One suggestion:-
Pass emailid as comma-separated parameters to the stored procedure.
For eg:- @emails = 'user1@gmail.com,user2@yahoo.com,user3@sify.com'

Create a new Stored Procedure and use this code inside that new Stored Procedure

First test this logic by commenting the EXEC stored procedure and uncommenting SELECT EMAILID line

DECLARE @EMAILS VARCHAR(MAX)
SET @EMAILS = 'user1@gmail.com,user2@yahoo.com,user3@sify.com'
DECLARE @CUREMAILID VARCHAR(100)

WHILE CHARINDEX(',', @EMAILS) > 0
BEGIN
	SET @CUREMAILID = SUBSTRING(@EMAILS, 1, CHARINDEX(',', @EMAILS) - 1)
	SET @EMAILS = SUBSTRING(@EMAILS, LEN(@CUREMAILID)+2, LEN(@EMAILS))
	
	SELECT @CUREMAILID AS EMAIL

	-- T-SQL CODE TO PASS @CUREMAILID TO YOUR STORED PROCEDURE
	EXEC YourStoredProcedure @CUREMAILID
	-- SELECT @CUREMAILID AS EMAILID
END

-- T-SQL CODE TO PASS @@EMAILS TO YOUR STORED PROCEDURE
EXEC YourStoredProcedure @EMAILS
-- SELECT @CUREMAILID AS EMAILID

Open in new window


Inside the loop, extracting the email ids one by one and pass it to your stored procedure.
finally outside the loop, last emailid remains that execute outside the loop

Raj
0
 
Rajkumar GsSoftware EngineerCommented:
This is one logic, that can be used. Here is query that you can run directly and test
DECLARE @EMAILS VARCHAR(MAX)
SET @EMAILS = 'user1@gmail.com,user2@yahoo.com,user3@sify.com'
SELECT @EMAILS AS PARAMETER
DECLARE @CUREMAILID VARCHAR(100)

WHILE CHARINDEX(',', @EMAILS) > 0
BEGIN
	SET @CUREMAILID = SUBSTRING(@EMAILS, 1, CHARINDEX(',', @EMAILS) - 1)
	SET @EMAILS = SUBSTRING(@EMAILS, LEN(@CUREMAILID)+2, LEN(@EMAILS))
	

	-- T-SQL CODE TO PASS @CUREMAILID TO YOUR STORED PROCEDURE
	SELECT @CUREMAILID AS EMAILID
	-- EXEC YourStoredProcedure @CUREMAILID
END

-- T-SQL CODE TO PASS @@EMAILS TO YOUR STORED PROCEDURE
-- EXEC YourStoredProcedure @EMAILS
SELECT @CUREMAILID AS EMAILID

Open in new window


Raj
0
 
handyjayAuthor Commented:
I am unfamiliar with CURSOR, so I researched it more.   I came up with this and it works.  It seems simpler than CSV, is there a negative to using CURSOR over CSV?



DECLARE @email varchar(50)

DECLARE rsUsers CURSOR FOR
SELECT DISTINCT email from sales_jobentry s join employees  
on accountexec = repcode
where(Convert(varchar(10), DueDate, 101) = Convert(varchar(10), getdate(), 101))
and s.jobnumber <> 0
and s.jobvoid <> 1
and (havedata = 0 or havematerials = 0)
and jobdescriptions not like '%test%'
group by email

OPEN rsUsers

FETCH NEXT FROM rsUsers INTO @email
WHILE (@@FETCH_STATUS <> -1)
BEGIN

-- DO WORK



FETCH NEXT FROM rsUsers INTO @email

END

CLOSE rsUsers
DEALLOCATE rsUsers


0
 
Rajkumar GsSoftware EngineerCommented:
Considering the disadvantage of cursor, I prefer using some loop - some logic like I suggested.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.