October 10, 2011 at 3:17 am
I want to use while loop for this..............
DECLARE Total_salary CURSOR
FOR
SELECT
e.Emp_Id,FirstName,(Basic+HRA+Allowance)AS Total_Salary
from Salary Inner Join Emp_Detail e
on e.Emp_ID=Salary.Emp_Id
OPEN Total_salary
FETCH NEXT FROM Total_salary
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM Total_salary
END
CLOSE Total_salary
DEALLOCATE Total_salary
October 10, 2011 at 3:28 am
Rakesh.Chaudhary (10/10/2011)
I want to use while loop for this..............DECLARE Total_salary CURSOR
FOR
SELECT
e.Emp_Id,FirstName,(Basic+HRA+Allowance)AS Total_Salary
from Salary Inner Join Emp_Detail e
on e.Emp_ID=Salary.Emp_Id
OPEN Total_salary
FETCH NEXT FROM Total_salary
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM Total_salary
END
CLOSE Total_salary
DEALLOCATE Total_salary
--EDIT--
After you've thought about "why" you want to replace your cursor (I'm guessing performance based 😉 ), go and read this article[/url] which explains the best way to post DDL and sample data. Once you've collected all of this, post back explaining what you want to achieve and I'm sure someone can give you a nice set-base approach that will be much faster than a loop/cursor approach.
October 10, 2011 at 3:35 am
just for faste performance and for learning purposes
October 10, 2011 at 4:46 am
Rakesh.Chaudhary (10/10/2011)
just for faste performance and for learning purposes
Well, golly gosh! It seems that after you went to all the trouble of reading the link in my previous post, your DDL script become detached from your post!!
Unfortunately, in order to help you with your specific query, we still need that DDL and readily consumable test data. So, once more I'll point you at this article[/url] which explains the best way to provide us with working sample data and DDL scripts.
Once you've read this article[/url], you can post with the required information to allow the unpaid volunteers of this site to help.
Thanks!
October 10, 2011 at 5:07 am
Rakesh.Chaudhary (10/10/2011)
I want to use while loop for this..............DECLARE Total_salary CURSOR
FOR
SELECT
e.Emp_Id,FirstName,(Basic+HRA+Allowance)AS Total_Salary
from Salary Inner Join Emp_Detail e
on e.Emp_ID=Salary.Emp_Id
OPEN Total_salary
FETCH NEXT FROM Total_salary
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM Total_salary
END
CLOSE Total_salary
DEALLOCATE Total_salary
The only difference between what you have above and the "While Loop" you're talking about is where you get the data from... either fetched from a Cursor or single row loaded from a table. In fact, if you make the cursor forward only, read only, static, you'll have something with the exact same performance as a "While Loop"... SLOW!
It's a myth that the While Loops you're talking about are any faster than a proper Cursor. Both are RBAR and both are comparatively slow.
Your SELECT statement above is already the beginning of what you need for high performance Set-Based code... what else is it that you need to have done for each row?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 10, 2011 at 5:08 am
Cadavre (10/10/2011)
Rakesh.Chaudhary (10/10/2011)
just for faste performance and for learning purposesWell, golly gosh! It seems that after you went to all the trouble of reading the link in my previous post, your DDL script become detached from your post!!
Unfortunately, in order to help you with your specific query, we still need that DDL and readily consumable test data. So, once more I'll point you at this article[/url] which explains the best way to provide us with working sample data and DDL scripts.
Once you've read this article[/url], you can post with the required information to allow the unpaid volunteers of this site to help.
Thanks!
He's a newbie... forgive him for he knows not what he does. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 10, 2011 at 5:19 am
Jeff Moden (10/10/2011)
Cadavre (10/10/2011)
Rakesh.Chaudhary (10/10/2011)
just for faste performance and for learning purposesWell, golly gosh! It seems that after you went to all the trouble of reading the link in my previous post, your DDL script become detached from your post!!
Unfortunately, in order to help you with your specific query, we still need that DDL and readily consumable test data. So, once more I'll point you at this article[/url] which explains the best way to provide us with working sample data and DDL scripts.
Once you've read this article[/url], you can post with the required information to allow the unpaid volunteers of this site to help.
Thanks!
He's a newbie... forgive him for he knows not what he does. 😉
Hell, me too!! Just pointed out that the forum must've eaten his DDL script 🙂
October 12, 2011 at 10:50 am
so how would we email each employee his base pay with a set based SQL statement? I did something similar with a 'while' loop, so I'm curious how the set based solution would look.
October 13, 2011 at 9:10 am
The OP made no mention of sending an email per each result, which is why a set based solution was recommended.
Unfortunately, I don't think there currently is a purely set-based way to mass-email a large number of recipients from SQL server. I'm thinking the best way to handle that would be to use a set-based query to load the data into a temp table or table variable, and loop through each row of that.
October 13, 2011 at 10:12 am
I agree... I don't currently know of a method to send emails using an "effictive" set-based method. Sure, there are set-based methods to generate other code in a set-based method but that generated code still calls the send-mail procs one email at a time.
What I have seen, though, are systems that "send" the email addressing and content to a staging table and either have a job that sweeps through the table on a scheduled basis (once a minute, for example) or have built some managed code to do the job. The advantage there is that if the email engine is "down" for any reason, it doesn't stop processing as the emails are still "sent" to the staging table. When the email engine comes back up, the code starts the sweeps and continues until the staging or "queue" table has been emptied. It also allows for more than one "send job" to operate in parallel.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply