handyjay
asked on
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?
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?
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.
But You can do in TSQL what every you had done in .NET code, But in different way.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This is one logic, that can be used. Here is query that you can run directly and test
Raj
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 EMAILIDRaj
ASKER
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),
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
DECLARE @email varchar(50)
DECLARE rsUsers CURSOR FOR
SELECT DISTINCT email from sales_jobentry s join employees
on accountexec = repcode
where(Convert(varchar(10),
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Considering the disadvantage of cursor, I prefer using some loop - some logic like I suggested.
How big is the table?
You could use a cursor to iterate the records