Thanks for the response. Here is my proc: The case statement is in order by clause. How can I make it more efficient:

CREATE PROCEDURE [dbo].[sp_getClients]

AS

BEGIN

SELECT intID, ClientID

FROM ...

ok here is my updated code:

CASE ClientID WHEN CHARINDEX('-',ClientID) <> 0 THEN

(CASE WHEN ISNUMERIC(SUBSTRING(ClientID ,CHARINDEX('-',ClientID ) + 1, 5)) = 1

THEN CONVERT(INT,SUBSTRING(ClientID ,CHARINDEX('-',ClientID ) + 1, 5))

ELSE ClientID...

So I take it that there is no way to debug in 2005.

Simon,

I have done all of that, but is there a way to debug right into the SP from ssms?

Thanks.

Matt's explanation makes sense now, should have read his previous posting properly. Grant mentioned about generated code, but this code is certainly not generated through any code...

Hi Matt,

But how long does it take to type the word "WHERE" ? Im I missing something? The queries are as simple as:

SELECT * FROM tblA WHERE...

Hi David,

I previsouly added the case statement in the group by, but forgot to add it in the order by and that's where the problem was....

Here is the correct query:

SELECT

SalesMan1,

SUM(grand_inv_total) as TotalSales,

DATEPART(mm,CONVERT(DATETIME,DateEntered,106)) as Month

FROM Sales WITH (NOLOCK)

WHERE SUBSTRING(DateEntered,1,4) = 2008

AND SalesMan1 <> ''

GROUP BY SalesMan1, DATEPART(mm,CONVERT(DATETIME,DateEntered,106))

ORDER BY SalesMan1, DATEPART(mm,CONVERT(DATETIME,DateEntered,106))

Here the...

Thanks Gail. That makes perfect sense and it is taking less longer than the other one. Thanks again.

Thanks Gail,

I still have a cursor, but now I am updating based on the value from the cursor. Take a look at this tell me whether...

Hello All,

I just could not see the extra "D" in the "UPDATE". Thanks for pointing that out. As far as "set based insert", I am little...

Hi Gail,

Here is a whole stored procedure. I don't think that giving you any additional structure will help:

ALTER Procedure [dbo].[SP_Proc1]

(

@RDCCHAR(5) = '00910',

@DateOneDATETIME = '03/04/2007',

@DateTwoDATETIME = '04/02/2007',

@LocationNo...

Hi Gail,

I am not sure that I am understanding what you mean by

"moving the subquery with the sum into the from clause and treating it...

Hi Gail,

I apologize. Here is the actual query:

UPDATE #TMP

SET

...

Hi Gail,

Here is the whole query:

UPDATE #TMP

SET

ItmRecvdYR2=ISNULL((SELECT SUM(Quantity) FROM dbo.GetAllReorderScannedItemsByDate() INNER JOIN (SELECT VendorID, LOC, DateYR2 FROM #TMP) TMPData ON VendorID=TMPData.VendorID AND locationno=TMPData.LOC and DATEPART(YEAR,requisitiondate) =...