|
what are advantige and disAdvantige of using the
cursor
|
|
|
|
|
|
Cursors are having its own pros and cons.
+Ve : Row wise operation which helps us to avoid lot of complex queries
-Ve : VERY IMPORTANT, needs huge memory of SQL Server. So be careful while using cursor. It is always good if you avoid cursors as much. We have a product which has around 7500 stored procedure and we have written without any cursors  )
There are number of ways to avoid cursor....
1) Use While Loop
2) Use Update with FROM Clause
3) Use Joins, use subqueries..
4) Store data is views etc...
5) Last and least do good database desiging.....
Amit
|
|
|
|
|
I think u r right we should avoid cursors as far as possible...I have a query of Dynamic sql how can I use it in a while loop?please help
|
|
|
|
|
hi, i am using MSSQL as my backend, i want to fetch top 3 students from the Student table.Plz help me
Senthil
|
|
|
|
|
This was already answered yesterday in other forum have you checked that?
Best Regards,
Apurva Kaushal
|
|
|
|
|
use top operator. SELECT TOP 3 from table [order by field]. Please note order by clause is optional and if require use it.
Amit
|
|
|
|
|
Hello everybody
I have a problem with SQL Server 2005 Express. Here are description:
In a database, there is a table, name TEST with three columns: a(int), b(int), c(char).
I created a view as the following:
<br />
CREATE VIEW [dbo].[view_test]<br />
AS<br />
SELECT TOP (100) PERCENT A, B, C<br />
FROM dbo.Test<br />
ORDER BY A, B<br />
Clearly, I want the result set ordered by column A and B.
Then I executed the folowing query:
<br />
SELECT * FROM view_test<br />
But the result is not sorted.
In SQL Server 2000, the problem does not happend.
Is there any comment?
Thanks so much.
|
|
|
|
|
Sorting inside a view is not a SQL standard and its not suppose to work in SQL2K5. It worked in SQL2K but it was not a good choice.
Farhan Noor Qureshi
|
|
|
|
|
What datatypes are you using for columns A and B?
Order By in a SQLExpress view works fine for me.
If you hit F1 in "Microsoft SQL Server Management Studio Express" while in the View Designer, you are taken to:
http://msdn2.microsoft.com/en-us/library/ms172014.aspx[^]
which explains Sort Type and Sort Order for views.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
how do u do looping in T-SQL?
|
|
|
|
|
I like WHILE loop
Farhan Noor Qureshi
|
|
|
|
|
It depends on what you are trying to do. If you can do the operation using a set based operation (what SQL is optimised for) then you are better doing it that way.
If you are looping over a counter then a WHILE loop is useful. If you are looping over rows in a database then you might want to look at CURSORs. However, if you are tempted to use cursors then I would really suggest trying to find a set based method for the same thing first and use CURSORs as a last resort.
|
|
|
|
|
Hi.
Is there a "ignore null's" property, or a way to implement it, in the Constraint or the UniqueConstraint of a DataTable?
I have in the server (postgres 8.1.4) a field with a unique contraint, and this constraint can ignore nulls.
How can i define such a thing in ADO.NET?
Thanks.
|
|
|
|
|
If the field allows null then you can have a unique key constraint on it. BTW: aggregate functions ignore NULLs by design.
Farhan Noor Qureshi
|
|
|
|
|
no...
I have a UniqueConstraint on a field called 'cve_unidad', this field has .AllowDBNull = true, and i put DefaultValue = null in this field, but, there is still a the problem... when i put two rows in then datagrid, the second null value raises the error message:
"Value Column 'cve_unidad' is constrained to be unique. Value '' is already present. Do you want to correct this value?"
this is a example:
cve_unidad | nom_unidad
(null) | 'un nombre' <-- here is ok
(null) | 'another nombre' <-- here raises the "already present" error
This is an "autonumeric-like" scenario, i want to send some values at once, then clear an refill the datagrid with the data (with de autonumeric updated). I don't want to use the .AutoIncrement properte beacuse the value displayed will not neccesary equals the autonumeric value in the database.
thanks
|
|
|
|
|
Using SQL Server 2000 here and I have a table named "Calendar" that is an auxillary table created using scripts found on the web. Basically, the calendar contains an entry for every day for the next several years that has a datetime field, fields for the day of the month, month of the year, the year, the week of the year, etc. I'm trying to write a query or set of queries to return the recurring dates if the user of a web app decides to schedule something on a recurring basis much like recurring appointments in Outlook Calendar.
The following query is what I am using to attempt to return the every 3rd Sunday starting on 01/01/08.
<br />
SELECT c.dt, c.dayname, c.monthname, c.M, c.D, c.Y, c.W<br />
FROM dbo.Calendar c <br />
WHERE c.dayname IN ('Sunday')<br />
AND c.dt >= '01/01/08' <br />
AND c.Y IN (2008, 2009) <br />
AND 0 = ((SELECT COUNT(*) FROM <br />
dbo.Calendar c2 <br />
WHERE c.dt >= c2.dt<br />
AND c.dayname = c2.dayname) % 3) <br />
ORDER BY c.dt<br />
GO
The query is returning every 3 sunday's but it's starting with Jan. 13th (the 2nd sunday) instead of Jan. 20th (the 3rd Sunday). After that, it is returning every 3rd Sunday but it is off by 1 week because of where it's starting.
What am I doing wrong? Also, what kind of modifications might I need to make to this to support the other recurring event patterns that can be found in Outlook? I've searched the web a lot for recurring events and all I can seem to find is how to create the auxillary calendar table that I created but no info on how to retrieve the dates that a recurring event should fall on.
|
|
|
|
|
T-Smooth wrote: Using SQL Server 2000 here and I have a table named "Calendar" that is an auxillary table created using scripts found on the web.
Please post the script for creating the Calendar table.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I generated the calendar using scripts taken from this FAQ. Search the pdf for "calendar table" and it should take you to the right FAQ question. There are scripts to add in all of the holidays as well. You first need to create a numbers table with a sufficient amount of numbers in there as well.
http://www.aspfaq.com/downloads/ASPFAQ-2006-04-18.pdf[^]
Here's the scripts if you don't want to look at the pdf. It will give you a table consisting of 30 years starting with 01/01/2000:
<br />
CREATE TABLE dbo.Numbers<br />
(<br />
Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED<br />
)<br />
WHILE COALESCE(SCOPE_IDENTITY(), 0) <= 20000<br />
BEGIN<br />
INSERT dbo.Numbers DEFAULT VALUES<br />
END<br />
GO<br />
<br />
<br />
<br />
CREATE TABLE dbo.Calendar<br />
(<br />
dt SMALLDATETIME NOT NULL<br />
PRIMARY KEY CLUSTERED,<br />
isWeekday BIT,<br />
isHoliday BIT,<br />
Y SMALLINT,<br />
FY SMALLINT,<br />
Q TINYINT,<br />
M TINYINT,<br />
D TINYINT,<br />
DW TINYINT,<br />
monthname VARCHAR(9),<br />
dayname VARCHAR(9),<br />
W TINYINT<br />
)<br />
GO<br />
<br />
INSERT Calendar(dt)<br />
SELECT DATEADD(DAY, Number, '20000101')<br />
FROM dbo.Numbers<br />
WHERE Number <= 10957<br />
ORDER BY Number<br />
GO<br />
<br />
UPDATE dbo.Calendar SET<br />
isWeekday = CASE<br />
WHEN DATEPART(DW, dt) IN (1,7)<br />
THEN 0<br />
ELSE 1 END,<br />
isHoliday = 0,<br />
Y = YEAR(dt),<br />
FY = YEAR(dt),<br />
<br />
Q = CASE<br />
WHEN MONTH(dt) <= 3 THEN 1<br />
WHEN MONTH(dt) <= 6 THEN 2<br />
WHEN MONTH(dt) <= 9 THEN 3<br />
ELSE 4 END,<br />
M = MONTH(dt),<br />
D = DAY(dt),<br />
DW = DATEPART(DW, dt),<br />
monthname = DATENAME(MONTH, dt),<br />
dayname = DATENAME(DW, dt),<br />
W = DATEPART(WK, dt)<br />
GO<br />
<br />
ALTER TABLE dbo.Calendar ADD UTCOffset TINYINT NULL<br />
GO<br />
<br />
SET NOCOUNT ON<br />
DECLARE @dt SMALLDATETIME<br />
DECLARE @offset TINYINT<br />
SET @offset = 5<br />
DECLARE c CURSOR<br />
LOCAL STATIC READ_ONLY FOR<br />
SELECT dt FROM dbo.Calendar ORDER BY dt<br />
OPEN c<br />
FETCH NEXT FROM c INTO @dt<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN<br />
IF DATENAME(dw, @dt)='Sunday'<br />
AND DATEPART(DAY, @dt) <= 7<br />
AND DATENAME(MONTH, @dt) = 'April'<br />
SET @offset = 4<br />
IF DATENAME(dw, @dt)='Sunday'<br />
AND DATEPART(DAY, @dt) >= 25<br />
AND DATENAME(MONTH, @dt) = 'October'<br />
SET @offset = 5<br />
UPDATE dbo.Calendar SET UTCOffset = @offset WHERE dt = @dt<br />
FETCH NEXT FROM c INTO @dt<br />
END<br />
CLOSE c<br />
DEALLOCATE c<br />
GO<br />
<br />
CREATE FUNCTION dbo.ISOWeek<br />
(<br />
@dt SMALLDATETIME<br />
)<br />
RETURNS TINYINT<br />
AS<br />
BEGIN<br />
DECLARE @ISOweek TINYINT<br />
SET @ISOweek = DATEPART(WEEK,@dt)+1<br />
-DATEPART(WEEK,RTRIM(YEAR(@dt))+'0104')<br />
IF @ISOweek = 0<br />
BEGIN<br />
SET @ISOweek = dbo.ISOweek<br />
(<br />
RTRIM(YEAR(@dt)-1)+'12'+RTRIM(24+DAY(@dt))<br />
) + 1<br />
END<br />
IF MONTH(@dt) = 12 AND DAY(@dt)-DATEPART(DW,@dt) >= 28<br />
BEGIN<br />
SET @ISOweek=1<br />
END<br />
RETURN(@ISOweek)<br />
END<br />
GO<br />
<br />
UPDATE Calendar SET W = dbo.ISOWeek(dt)<br />
GO<br />
<br />
ALTER TABLE Calendar ADD HolidayDescription VARCHAR(32)<br />
GO<br />
<br />
-- New Year's Day - easy<br />
UPDATE Calendar<br />
SET<br />
isHoliday = 1,<br />
HolidayDescription = 'New Years'' Day'<br />
WHERE M = 1<br />
AND D = 1<br />
GO<br />
<br />
-- Memorial Day - last Monday in May<br />
UPDATE Calendar<br />
SET<br />
isHoliday = 1,<br />
HolidayDescription = 'Memorial Day'<br />
FROM Calendar c1<br />
WHERE M = 5<br />
AND DW = 2<br />
AND NOT EXISTS (SELECT 1 FROM Calendar c2<br />
WHERE M = 5 AND DW = 2<br />
AND c2.Y = c1.Y<br />
AND c2.dt > c1.dt)<br />
GO<br />
<br />
-- Labor Day - first Monday in September<br />
UPDATE Calendar<br />
SET<br />
isHoliday = 1,<br />
HolidayDescription = 'Labor Day'<br />
FROM Calendar c1<br />
WHERE M = 9<br />
AND DW = 2<br />
AND NOT EXISTS (SELECT 1 FROM Calendar c2<br />
WHERE M = 9 AND DW = 2<br />
AND c2.Y = c1.Y<br />
AND c2.dt < c1.dt)<br />
GO<br />
<br />
-- Thanksgiving Thursday - 4th Thursday in November<br />
UPDATE Calendar<br />
SET<br />
isHoliday = 1,<br />
HolidayDescription = 'Thanksgiving Thursday'<br />
FROM Calendar c1<br />
WHERE M = 11<br />
AND DW = 5<br />
AND (SELECT COUNT(*) FROM Calendar c2<br />
WHERE M = 11 AND DW = 5<br />
AND c2.Y = c1.Y<br />
AND c2.dt < c1.dt) = 3<br />
GO<br />
<br />
-- Traditionally, Thanksgiving Friday, as well<br />
-- as long as you haven't pre-configured any<br />
-- other Thursday in November to be isHoliday<br />
UPDATE Calendar<br />
SET<br />
isHoliday = 1,<br />
HolidayDescription = 'Thanksgiving Friday'<br />
FROM Calendar c1<br />
WHERE M = 11<br />
AND DW = 6<br />
AND EXISTS (SELECT 1 FROM Calendar c2<br />
WHERE M = 11 AND DW = 5<br />
AND c2.dt = (c1.dt - 1)<br />
AND c2.Y = c1.Y<br />
AND isHoliday = 1)<br />
GO<br />
<br />
-- Veterans' Day - easy<br />
-- however do this AFTER Thanksgiving calculation<br />
-- in case it happens to fall on a Thursday<br />
UPDATE Calendar<br />
SET<br />
isHoliday = 1,<br />
HolidayDescription = 'Veterans'' Day'<br />
WHERE M = 11 AND D = 11<br />
GO<br />
<br />
-- Christmas Day - easy<br />
UPDATE Calendar<br />
SET<br />
isHoliday = 1,<br />
HolidayDescription = 'Christmas Day'<br />
WHERE M = 12<br />
AND D = 25<br />
GO<br />
<br />
CREATE FUNCTION dbo.GetEasterSunday<br />
(<br />
@Y INT<br />
)<br />
RETURNS SMALLDATETIME<br />
AS<br />
BEGIN<br />
DECLARE @EpactCalc INT,<br />
@PaschalDaysCalc INT,<br />
@NumOfDaysToSunday INT,<br />
@EasterMonth INT,<br />
@EasterDay INT<br />
SET @EpactCalc = (24 + 19 * (@Y % 19)) % 30<br />
SET @PaschalDaysCalc = @EpactCalc - (@EpactCalc / 28)<br />
SET @NumOfDaysToSunday = @PaschalDaysCalc - (<br />
(@Y + @Y / 4 + @PaschalDaysCalc - 13) % 7<br />
)<br />
SET @EasterMonth = 3 + (@NumOfDaysToSunday + 40) / 44<br />
SET @EasterDay = @NumOfDaysToSunday + 28 - (<br />
31 * (@EasterMonth / 4)<br />
)<br />
RETURN<br />
(<br />
SELECT CONVERT<br />
(<br />
SMALLDATETIME,<br />
RTRIM(@Y)<br />
+ RIGHT('0'+RTRIM(@EasterMonth), 2)<br />
+ RIGHT('0'+RTRIM(@EasterDay), 2)<br />
)<br />
)<br />
END<br />
GO<br />
<br />
CREATE FUNCTION dbo.GetEasterMonday<br />
(<br />
@Y INT<br />
)<br />
RETURNS SMALLDATETIME<br />
AS<br />
BEGIN<br />
RETURN (SELECT dbo.GetEasterSunday(@Y) + 1)<br />
END<br />
GO<br />
CREATE FUNCTION dbo.GetGoodFriday<br />
(<br />
@Y INT<br />
)<br />
RETURNS SMALLDATETIME<br />
AS<br />
BEGIN<br />
RETURN (SELECT dbo.GetEasterSunday(@Y) - 2)<br />
END<br />
GO<br />
<br />
UPDATE Calendar<br />
SET<br />
isHoliday = 1,<br />
HolidayDescription = 'Good Friday'<br />
WHERE dt = dbo.GetGoodFriday(Y)<br />
GO<br />
<br />
UPDATE Calendar<br />
SET<br />
isHoliday = 1,<br />
HolidayDescription = 'Easter Monday'<br />
WHERE dt = dbo.GetEasterMonday(Y)<br />
GO<br />
|
|
|
|
|
SELECT c.dt, c.dayname, c.monthname, c.M, c.D, c.Y, c.W
FROM dbo.Calendar c
WHERE c.dayname IN ('Sunday')
AND c.dt >= '01/01/08'
AND c.Y IN (2008, 2009)
AND 2 = (SELECT COUNT(*) FROM dbo.Calendar c2
WHERE c.y = c2.y
AND c.m = c2.m
AND c2.dt < c.dt
AND c.dayname = c2.dayname)
ORDER BY c.dt
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
This only gets me the 2nd sunday of each month, not necessarily every 2 weeks from a certain start date.
|
|
|
|
|
Oh, I guess I misunderstood. Sorry.
SELECT c.dt, c.dayname, c.monthname, c.M, c.D, c.Y, c.W
FROM dbo.Calendar c,
(select top 1 dt + 14 as dt
from calendar c3
where dayname = 'Sunday' and dt >= '01/08/08' order by dt) as c2
WHERE c.dt >= c2.dt
AND c.Y IN (2008, 2009)
AND datediff(dd,c2.dt,c.dt ) % 21 = 0
ORDER BY c.dt
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Not sure why you would want to set up a table containing each date when the calculations are just as easy to write.
Have you considered adding a few more fields to your table:
1) DayNameIndexForMonth (i.e. 1st Sunday, 2nd Sunday, 3rd Sunday)
2) DayOfYear (i.e. This is the 74th day of the year)
3) Quarter (i.e. This day is in the 3rd Quarter)
This might make your queries a lot easier.
On the other hand, you can always calculate on the fly. With only 365 iterations per year, loops should execute quickly enough. Knowing that the 3rd 'Day Name' of every month will fall between the 15th and 21st day you can code it like this:
CREATE PROCEDURE RecurringDates_ThirdSunday
(
@Start DATETIME,
@End DATETIME
)
AS
DECLARE @curDt DATETIME
SET @curDt = @Start
DECLARE @t TABLE (GoodDate DATETIME NOT NULL)
WHILE @curDt <= @End
BEGIN
IF DATEPART(dw,@curDt) = 1
BEGIN
IF DATEPART(dd,@curDt) BETWEEN 15 AND 21
BEGIN
INSERT INTO @t (GoodDate) VALUES (@curDt)
END
END
SET @curDt = DATEADD(dd,1,@CurDt)
END
SELECT *
FROM @t
|
|
|
|
|
I guess he wants every third Sunday from the start date, not every third Sunday of the month. I had the same misunderstanding.
But, I like your method better than creating a table with every date between now and some arbitrary future date.
CREATE PROCEDURE RecurringDates_ThirdSunday
(
@Start DATETIME,
@End DATETIME
)
AS
DECLARE @curDt DATETIME
SET @curDt = @Start
DECLARE @t TABLE (GoodDate DATETIME NOT NULL)
WHILE DATEPART(dw,@curDt) != 1
BEGIN
SET @curDt = DATEADD(dd,1,@CurDt)
END
SET @curDt = DATEADD(dd,14,@CurDt)
WHILE @curDt <= @End
BEGIN
INSERT INTO @t (GoodDate) VALUES (@curDt)
SET @curDt = DATEADD(dd,21,@CurDt)
END
SELECT *
FROM @t
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Is there anyway to take the whole condition part of WHERE clause as a parameter of a stored procedure ?
The result I want to gain is something that would be achieved by executing this stored procedure.Although this is wrong , I want to achieve the result.Is there anyway get this result ?
CREATE PROCEDURE sp_GetList
@condition varchar(50)
AS
SELECT * FROM Table_X WHERE @condition
-- modified at 5:53 Monday 11th September, 2006
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
