Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2011-04-28
7
Medium Priority
?
591 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?
0
Comment
Question by:handyjay
7 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35483605

How big is the table?
You could use a cursor to iterate the records
0
 
LVL 9

Expert Comment

by:radcaesar
ID: 35483648
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
 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 1000 total points
ID: 35483749
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35483783
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
 

Author Comment

by:handyjay
ID: 35483967
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
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 1000 total points
ID: 35484060

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
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35484096
Considering the disadvantage of cursor, I prefer using some loop - some logic like I suggested.

0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question