We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

Medium Priority
601 Views
Last Modified: 2012-05-11
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?
Comment
Watch Question

Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

How big is the table?
You could use a cursor to iterate the records
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.
Rajkumar GsSoftware Engineer
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Rajkumar GsSoftware Engineer

Commented:
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

Author

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


Software Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Rajkumar GsSoftware Engineer

Commented:
Considering the disadvantage of cursor, I prefer using some loop - some logic like I suggested.

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.