Case statement Error

Hi,
I don't know what i am doing wrong here, I am always getting this error
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '>'.
When I am running the following query
SELECT title, price,
Budget = CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END
FROM titles
Could anyone put some light on it.
Thanks
J S"John Smith" <John@.nospam.yahoo.com> wrote in news:ebqEpwfOFHA.1500
@.TK2MSFTNGP09.phx.gbl:

> Hi,
> I don't know what i am doing wrong here, I am always getting this error
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near '>'.
> When I am running the following query
> SELECT title, price,
> Budget = CASE price
> WHEN price > 20.00 THEN 'Expensive'
> WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
> WHEN price < 10.00 THEN 'Inexpensive'
> ELSE 'Unknown'
> END
> FROM titles
> Could anyone put some light on it.
> Thanks
> J S
>
>
Leave out the field name between the CASE and WHEN keywords.
Rumble
"Write something worth reading, or do something worth writing."
-- Benjamin Franklin|||There are two versions of Case Syntax, and you are "mixing" them...
Ver 1
Case <Expression>
When <ExpressionValue1> Then <OutValue1>
When <ExpressionValue2> Then <OutValue2>
When <ExpressionValue3> Then <OutValue3>
Else <ElseOutValue> End
Version 2
Case
When <BooleanExpression1> Then <OutValue1>
When <BooleanExpression2> Then <OutValue2>
When <BooleanExpression3> Then <OutValue3>
Else <ElseOutValue> End
So you need to eliminate the "price" right after the Case...
SELECT title, price,
Budget = CASE
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END
FROM titles
"John Smith" wrote:

> Hi,
> I don't know what i am doing wrong here, I am always getting this error
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near '>'.
> When I am running the following query
> SELECT title, price,
> Budget = CASE price
> WHEN price > 20.00 THEN 'Expensive'
> WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
> WHEN price < 10.00 THEN 'Inexpensive'
> ELSE 'Unknown'
> END
> FROM titles
> Could anyone put some light on it.
> Thanks
> J S
>
>|||this should work:
SELECT title, price,
Budget = CASE --price <- removed
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END
FROM titles
dean
"John Smith" <John@.nospam.yahoo.com> wrote in message
news:ebqEpwfOFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I don't know what i am doing wrong here, I am always getting this error
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near '>'.
> When I am running the following query
> SELECT title, price,
> Budget = CASE price
> WHEN price > 20.00 THEN 'Expensive'
> WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
> WHEN price < 10.00 THEN 'Inexpensive'
> ELSE 'Unknown'
> END
> FROM titles
> Could anyone put some light on it.
> Thanks
> J S
>|||Thanks a lot guys, it is working....
J S
"John Smith" <John@.nospam.yahoo.com> wrote in message
news:ebqEpwfOFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I don't know what i am doing wrong here, I am always getting this error
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near '>'.
> When I am running the following query
> SELECT title, price,
> Budget = CASE price
> WHEN price > 20.00 THEN 'Expensive'
> WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
> WHEN price < 10.00 THEN 'Inexpensive'
> ELSE 'Unknown'
> END
> FROM titles
> Could anyone put some light on it.
> Thanks
> J S
>|||I removed price from CASE. This passed syntax check for me:
SELECT title, price,
Budget = CASE
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END
FROM titles

CASE Issue

When I set @.reorderfav = 1 or @.reorderfav = 3, it works perfect. However,
when i set @.reorderfav = 2, it throws this error:
Server: Msg 241, Level 16, State 1, Line 7
Syntax error converting datetime from character string.
I have also put the ProjectMain.ProjectName in the ORDER BY and ran it
manually, and it works. This is why I'm so . Any help would be
greatly appreciated.
Thanks,
JJ
CREATE PROCEDURE [usp_getindexFav]
(
@.userid varchar(6),
@.reorderfav int
)
AS
SELECT AppUserPref.UserID, ProjectMain.ProjectName,
ProjectMain.ProjectID, ProjectMain.CreatedPostDate
FROM AppUserPref INNER JOIN ProjectMain ON AppUserPref.ProjectID =
ProjectMain.ProjectID
WHERE AppUserPref.ID = @.userid
ORDER BY CASE
WHEN @.reorderfav = 1 THEN ProjectMain.ProjectID
WHEN @.reorderfav = 2 THEN ProjectMain.ProjectName
WHEN @.reorderfav = 3 THEN ProjectMain.CreatedPostDate
ELSE ProjectMain.ProjectName
END
GO
Manually (this works):
SELECT AppUserPref.UserID, ProjectMain.ProjectName,
ProjectMain.ProjectID, ProjectMain.CreatedPostDate
FROM AppUserPref INNER JOIN ProjectMain ON AppUserPref.ProjectID =
ProjectMain.ProjectID
WHERE AppUserPref.ID = @.userid
ORDER BY ProjectMain.ProjectNameCASE is an expression that can only return a value of the same data type.
You will need to break it out so that each potential data type is handled by
a separate CASE expression.
http://www.aspfaq.com/2501
http://www.aspfaq.com/
(Reverse address to reply.)
"Josh Jones" <myaddress@.thidress.com> wrote in message
news:uhvG0RrGFHA.3724@.TK2MSFTNGP10.phx.gbl...
> When I set @.reorderfav = 1 or @.reorderfav = 3, it works perfect. However,
> when i set @.reorderfav = 2, it throws this error:
> Server: Msg 241, Level 16, State 1, Line 7
> Syntax error converting datetime from character string.
> I have also put the ProjectMain.ProjectName in the ORDER BY and ran it
> manually, and it works. This is why I'm so . Any help would be
> greatly appreciated.
> Thanks,
> JJ
> CREATE PROCEDURE [usp_getindexFav]
> (
> @.userid varchar(6),
> @.reorderfav int
> )
> AS
>
> SELECT AppUserPref.UserID, ProjectMain.ProjectName,
> ProjectMain.ProjectID, ProjectMain.CreatedPostDate
> FROM AppUserPref INNER JOIN ProjectMain ON AppUserPref.ProjectID =
> ProjectMain.ProjectID
> WHERE AppUserPref.ID = @.userid
> ORDER BY CASE
> WHEN @.reorderfav = 1 THEN ProjectMain.ProjectID
> WHEN @.reorderfav = 2 THEN ProjectMain.ProjectName
> WHEN @.reorderfav = 3 THEN ProjectMain.CreatedPostDate
> ELSE ProjectMain.ProjectName
> END
> GO
>
> Manually (this works):
> SELECT AppUserPref.UserID, ProjectMain.ProjectName,
> ProjectMain.ProjectID, ProjectMain.CreatedPostDate
> FROM AppUserPref INNER JOIN ProjectMain ON AppUserPref.ProjectID =
> ProjectMain.ProjectID
> WHERE AppUserPref.ID = @.userid
> ORDER BY ProjectMain.ProjectName
>|||"Josh Jones" <myaddress@.thidress.com> wrote in message
news:uhvG0RrGFHA.3724@.TK2MSFTNGP10.phx.gbl...

> CREATE PROCEDURE [usp_getindexFav]
> (
> @.userid varchar(6),
> @.reorderfav int
> )
> AS
>
> SELECT AppUserPref.UserID, ProjectMain.ProjectName,
> ProjectMain.ProjectID, ProjectMain.CreatedPostDate
> FROM AppUserPref INNER JOIN ProjectMain ON AppUserPref.ProjectID =
> ProjectMain.ProjectID
> WHERE AppUserPref.ID = @.userid
> ORDER BY CASE
> WHEN @.reorderfav = 1 THEN ProjectMain.ProjectID
> WHEN @.reorderfav = 2 THEN ProjectMain.ProjectName
> WHEN @.reorderfav = 3 THEN ProjectMain.CreatedPostDate
> ELSE ProjectMain.ProjectName
> END
Case requires all output values to be of the same type and tries to convert
if not. Since projectName can't be converted to a date it breaks. Try this
for the order by
ORDER BY
CASE WHEN @.reorderfav = 1 then ProjectMain.ProjectID END,
CASE WHEN @.reorderfav = 2 then ProjectMain.ProjectName END,
CASE WHEN @.reorderfav = 3 then ProjectMain.CreatedPostDate END,
ProjectMain.ProjectName
Good Luck,
Jim|||Jim--good quick answer!!
Thanks!!!
"Josh Jones" <myaddress@.thidress.com> wrote in message
news:uhvG0RrGFHA.3724@.TK2MSFTNGP10.phx.gbl...
> When I set @.reorderfav = 1 or @.reorderfav = 3, it works perfect. However,
> when i set @.reorderfav = 2, it throws this error:
> Server: Msg 241, Level 16, State 1, Line 7
> Syntax error converting datetime from character string.
> I have also put the ProjectMain.ProjectName in the ORDER BY and ran it
> manually, and it works. This is why I'm so . Any help would be
> greatly appreciated.
> Thanks,
> JJ
> CREATE PROCEDURE [usp_getindexFav]
> (
> @.userid varchar(6),
> @.reorderfav int
> )
> AS
>
> SELECT AppUserPref.UserID, ProjectMain.ProjectName,
> ProjectMain.ProjectID, ProjectMain.CreatedPostDate
> FROM AppUserPref INNER JOIN ProjectMain ON AppUserPref.ProjectID =
> ProjectMain.ProjectID
> WHERE AppUserPref.ID = @.userid
> ORDER BY CASE
> WHEN @.reorderfav = 1 THEN ProjectMain.ProjectID
> WHEN @.reorderfav = 2 THEN ProjectMain.ProjectName
> WHEN @.reorderfav = 3 THEN ProjectMain.CreatedPostDate
> ELSE ProjectMain.ProjectName
> END
> GO
>
> Manually (this works):
> SELECT AppUserPref.UserID, ProjectMain.ProjectName,
> ProjectMain.ProjectID, ProjectMain.CreatedPostDate
> FROM AppUserPref INNER JOIN ProjectMain ON AppUserPref.ProjectID =
> ProjectMain.ProjectID
> WHERE AppUserPref.ID = @.userid
> ORDER BY ProjectMain.ProjectName
>

Case in Where Clause

Can you put a Case statement in a Where clause?
I have the following Cursor I am setting up that is giving me the error:
Server: Msg 170, Level 15, State 1, Line 26
Line 26: Incorrect syntax near '>'.
Declare @.SearchCursor Cursor
Set @.SearchCursor = Cursor for Select
CommandText,WhereClause,SearchID,UserID
from CandidateSearches where SearchAgent=1 and SearchAgentActive=1 and
(CASE WHEN NotifyFrequency = 'D' THEN
(DATEDIFF(DAY,SearchAgentLastRun,GetDate
()) > 0)
WHEN NotifyFrequency = 'W' THEN
(DATEDIFF(DAY,SearchAgentLastRun,GetDate
()) >= 7)
WHEN NotifyFrequency = 'M' THEN
(DATEDIFF(DAY,SEarchAgentLastRun,GetDate
()) >= 30) END)
Line 26 is the line the Case Statement is on.
What is the problem with this line?
Thanks,
TomHi Tom
The most important thing to keep in mind is that there is no case STATEMENT
in Transact-SQL. There is a case EXPRESSION, which can be used anywhere you
use an expression. So, you can use a case EXPRESSION in a WHERE, in place of
a value.
Without any more details like the DDL, or a description of what you're
trying to accomplish, my guess is that you want to compare
DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) to one of several different
values, depending on the value of NotifyFrequency. If that is a correct
understanding, you might try something like this in your WHERE clause:
where SearchAgent=1 and SearchAgentActive=1 and
DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= CASE NotifyFrequency
WHEN 'D' THEN 0
WHEN 'W' THEN 7
WHEN 'M' THEN 30
END
Also make sure to consider the case where NotifyFrequency is not one of (D,
W, M)
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:eeyXs5cMGHA.3960@.TK2MSFTNGP09.phx.gbl...
> Can you put a Case statement in a Where clause?
> I have the following Cursor I am setting up that is giving me the error:
> Server: Msg 170, Level 15, State 1, Line 26
> Line 26: Incorrect syntax near '>'.
> Declare @.SearchCursor Cursor
> Set @.SearchCursor = Cursor for Select
> CommandText,WhereClause,SearchID,UserID
> from CandidateSearches where SearchAgent=1 and SearchAgentActive=1 and
> (CASE WHEN NotifyFrequency = 'D' THEN
> (DATEDIFF(DAY,SearchAgentLastRun,GetDate
()) > 0)
> WHEN NotifyFrequency = 'W' THEN
> (DATEDIFF(DAY,SearchAgentLastRun,GetDate
()) >= 7)
> WHEN NotifyFrequency = 'M' THEN
> (DATEDIFF(DAY,SEarchAgentLastRun,GetDate
()) >= 30) END)
> Line 26 is the line the Case Statement is on.
> What is the problem with this line?
> Thanks,
> Tom
>|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OQDXBGdMGHA.500@.TK2MSFTNGP15.phx.gbl...
> Hi Tom
> The most important thing to keep in mind is that there is no case
> STATEMENT in Transact-SQL. There is a case EXPRESSION, which can be used
> anywhere you use an expression. So, you can use a case EXPRESSION in a
> WHERE, in place of a value.
> Without any more details like the DDL, or a description of what you're
> trying to accomplish, my guess is that you want to compare
> DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) to one of several different
> values, depending on the value of NotifyFrequency. If that is a correct
> understanding, you might try something like this in your WHERE clause:
Exactly, but how would I do the instance where the case of 'D' is > 0 and
not >= 0?
Thanks,
Tom

> where SearchAgent=1 and SearchAgentActive=1 and
> DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= CASE NotifyFrequency
> WHEN 'D' THEN 0
> WHEN 'W' THEN 7
> WHEN 'M' THEN 30
> END
> Also make sure to consider the case where NotifyFrequency is not one of
> (D, W, M)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:eeyXs5cMGHA.3960@.TK2MSFTNGP09.phx.gbl...
>
>|||I think what you are trying to do is this
Declare @.SearchCursor Cursor
Set @.SearchCursor = Cursor for Select
CommandText,WhereClause,SearchID,UserID
from CandidateSearches where SearchAgent=1 and
SearchAgentActive=1 and
((NotifyFrequency = 'D'
and DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) > 0) OR
(NotifyFrequency = 'W'
and DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 7) OR
(NotifyFrequency = 'M'
and DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 30))|||<markc600@.hotmail.com> wrote in message
news:1140022428.091421.209720@.g14g2000cwa.googlegroups.com...
>I think what you are trying to do is this
> Declare @.SearchCursor Cursor
> Set @.SearchCursor = Cursor for Select
> CommandText,WhereClause,SearchID,UserID
> from CandidateSearches where SearchAgent=1 and
> SearchAgentActive=1 and
> ((NotifyFrequency = 'D'
> and DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) > 0) OR
> (NotifyFrequency = 'W'
> and DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 7) OR
> (NotifyFrequency = 'M'
> and DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 30))
That was exactly what I ended up doing. I was just curious as to how to it
(if you can do it) using the Case statement.
Thanks,
Tom|||>> Declare @.SearchCursor Cursor
>That was exactly what I ended up doing. I was just curious as to how to it
>(if you can do it) using the Case statement.
You can do it using CASE, but it doesn't let you do anything that AND,
OR and () don't already let you do. Since it resolves to a values,
you have to use it to set a value that indicates success or failure,
then test that in a comparison.
SELECT CommandText, WhereClause, SearchID, UserID
FROM CandidateSearches
WHERE SearchAgent=1
AND SearchAgentActive=1
AND CASE
WHEN NotifyFrequency = 'D'
AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) > 0
THEN 1
WHEN NotifyFrequency = 'W'
AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 7
THEN 1
WHEN NotifyFrequency = 'M'
AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 30
THEN 1
ELSE 0
END = 1
Roy|||"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:31l7v1dnjf97n234q7ih23mnbq4rtti9u1@.
4ax.com...
> You can do it using CASE, but it doesn't let you do anything that AND,
> OR and () don't already let you do. Since it resolves to a values,
> you have to use it to set a value that indicates success or failure,
> then test that in a comparison.
> SELECT CommandText, WhereClause, SearchID, UserID
> FROM CandidateSearches
> WHERE SearchAgent=1
> AND SearchAgentActive=1
> AND CASE
> WHEN NotifyFrequency = 'D'
> AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) > 0
> THEN 1
> WHEN NotifyFrequency = 'W'
> AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 7
> THEN 1
> WHEN NotifyFrequency = 'M'
> AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 30
> THEN 1
> ELSE 0
> END = 1
>
I was as to what this did at first, but then I realized what it was
doing.
In the "OR" example, I would only get a record if one of the 3 tests (as
well as the SearchAgent and SearchAgentActive) were matched.
In your example, you are testing if the Case statement was "1". It was
clear when I put parens around the case statement. I thought at first you
were setting "End=1". But when I put the parans in, it made sense:
... AND (CASE
WHEN NotifyFrequency = 'D'
AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) > 0
THEN 1
WHEN NotifyFrequency = 'W'
AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 7
THEN 1
WHEN NotifyFrequency = 'M'
AND DATEDIFF(DAY,SearchAgentLastRun,GetDate(
)) >= 30
THEN 1
ELSE 0
END) = 1
I think that is what is happening.
But as you said the OR does the same thing and is a little more clear and
you don't have to do the extra step of setting it to 0 or 1.
Thanks,
Tom
> Roy

Case expressions may only be nested to level 10

I'm running into this issue when I try to query a table (well a view) that was poorly designed and has many fields in the table as YesNo fields instead of a single field that just contains a value. (Fields A-N in the table for example)

So my query looks like:

CASE
WHEN A = 1 THEN 'A'
WHEN B = 1 THEN 'B'
WHEN C = 1 THEN 'C'
WHEN D = 1 THEN 'D'
WHEN E = 1 THEN 'E'
WHEN F = 1 THEN 'F'
WHEN G = 1 THEN 'G'
WHEN H = 1 THEN 'H'
WHEN I = 1 THEN 'I'
WHEN J = 1 THEN 'J'
WHEN K = 1 THEN 'K'
WHEN L = 1 THEN 'L'
WHEN M = 1 THEN 'M'
WHEN N = 1 THEN 'N'
END AS GeneratedField

Is there some other way I can approach this? I can't change the table as it isn't mine, but I need to run a report that looks at those fields and in GeneratedField places the result for the field that was checked.

Thanks.

Yes. Case Expression can be nested upto 10 Level.

But your expression is not looking like a nested expression.

Can you post the exact expression you are using.. Is any one of these A-N columns hold the value as 1 and reset 0.

|||

Ars_maxer,

There is another way of doing it, it's complicated than simpe case statement, though. Use Unpivot.

Here it is.

Code Snippet

declare @.tb table (
id int,
A int,
B int,
C int,
D int
);
insert into @.tb values(1, 1,0,0,0)
insert into @.tb values(2, 0,1,0,0)
insert into @.tb values(3,0,0,1,0)
insert into @.tb values(4,1,0,0,0)

select id, genvalue, vvalue from
( select id, A,B,C,D from @.tb)p
UNPIVOT
(vvalue for genvalue in (A,B,C,D) ) as unpvt
where vvalue =1

id genvalue vvalue
1 A 1
2 B 1
3 C 1
4 A 1

|||

This is a little hoakie, but here we go:

Code Snippet

declare @.a int, @.b int, @.c int, @.d int, @.e int

set @.a = 0

set @.b = 0

set @.c = 1

set @.d = 0

set @.e = 0

selectcoalesce(replace(convert(char(1),nullif(@.a,0)),'1','A'),

replace(convert(char(1),nullif(@.b,0)),'1','B'),

replace(convert(char(1),nullif(@.c,0)),'1','C'),

replace(convert(char(1),nullif(@.d,0)),'1','D'),

replace(convert(char(1),nullif(@.e,0)),'1','E')

)

|||

I'm not doing anything that isn't shown in my SQL statement there (Other than a SELECT and FROM and WHERE). However, nothing unusual.

Now if I run that SQL with say 8 statements then it works just fine.

I've read that this appears to be an issue and that others have run into it before. In some cases they wrapped the whole thing in a stored procedure on the linked server or their server and ran it that way.

In my case I'm executing this against a linked server and can't make a sproc there.

I'll play with the examples provided above and see if those will address the issue for me or not.

Thanks!

cascading parameters with multi-select

In SQL 2005 report server. When viewing a report that uses cascading
parameters with multi-select and one of the lower level cascading
parameters only populates with one item (that's all the query brought
back), the scroll left and right bar (for the parmeter box) covers up
the item when the item is longer in length than the parameter box.
Short parameter names are no problem. Is there any way to make the
parameter box wider or the drop down longer. This seems to be a bug as
it doesn't add any length when displaying the scroll bar.All of that business is out of our control... unfortunately... There are
many times when adjusting the parameter layout and size would be useful...
When it is really necessary, I put an HTML page in front of the report which
gathers the parameters... then calls the report...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Jim" wrote:
> In SQL 2005 report server. When viewing a report that uses cascading
> parameters with multi-select and one of the lower level cascading
> parameters only populates with one item (that's all the query brought
> back), the scroll left and right bar (for the parmeter box) covers up
> the item when the item is longer in length than the parameter box.
> Short parameter names are no problem. Is there any way to make the
> parameter box wider or the drop down longer. This seems to be a bug as
> it doesn't add any length when displaying the scroll bar.
>

Cascading Delete

I use cascading delete on my SQL Server Database. I am experiencing a
long query time on my highest level delete, 10 minutes. If I delete
from each table manually and then delete the parent, I will usually be
done in less than a minute. Any suggestions?Doug Serie (dserie@.charter.net) writes:
> I use cascading delete on my SQL Server Database. I am experiencing a
> long query time on my highest level delete, 10 minutes. If I delete
> from each table manually and then delete the parent, I will usually be
> done in less than a minute. Any suggestions?

Did you implement the cascading delete by triggers or by referential
integrity?

In either case, care to share the relevant code and table definitions,
including indexes?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp