The whole series of subselects sure makes that far more complicated than it needs to be.

Not sure if what you posted produces what the OP wants but this produces the...

And be wary of NOLOCK. If there is a good reason you can explain then go for it.

http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/%5B/url%5D

So with 5 other methods that are faster you still choose to use the...

New shot in the dark....

declare @PROCINDEX int

select @PROCINDEX = PROCINDEX FROM PROCINDEXCOUNTER

INSERT INTO dbo.ProdHistMaster

( PRODDATE

,PROCESSID

,PARTID

,PROCINDEX

,QUANTITY

,GRS

,UPDATEDBY

,UPDATEDTIME

,INSERIAL

,COMPANY

,QADRecID

)

SELECT

convert(datetime,absdate) as absDate

, 'Pack'

, Item

, ROW_NUMBER() over (order by absDate) + @PROCINDEX

, ItemQty

, 'G'

, 'QAD'

,...

There still is no need for a cursor here at all. A simple insert using Row_Number() should be totally fine.

subbareddy542 (7/11/2012)

---

PLS TRY BELOW CODE

SELECT DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0)

DECLARE @date datetime = '2012/06/20 11:05:02 PM'

DECLARE @HH INT=SUBSTRING(CONVERT(VARCHAR(10),@DATE,108),1,CHARINDEX(':',CONVERT(VARCHAR(10),@DATE,108))-1)

SELECT CONVERT(CHAR(10), CONVERT(datetime, @date,103),101)+' '+CONVERT(VARCHAR(10),CASE WHEN @date LIKE '% PM' THEN

...

Further proof we need ddl and sample data. 😉

Hard to tell for sure without ddl, sample data and desired output but I think this is pretty much the same thing...

Something like this perhaps?

INSERT INTO dbo.ProdHistMaster

( PRODDATE

,PROCESSID

,PARTID

,PROCINDEX

,QUANTITY

,GRS

,UPDATEDBY

,UPDATEDTIME

,INSERIAL

,COMPANY

,QADRecID

)

SELECT convert(datetime,absdate)...

Your insert is outside of the cursor as Lynn pointed out but the bigger question is why do you need a cursor at all? This is a single insert and...

adonetok (7/11/2012)

---

In the store procedure there are about 10 steps (update 10 tables) need to be done. If any step get an error will roll back.

The problem is exec this...

Lucky for you I had a few minutes of downtime.

Since there is no sample data or ddl I created my own to demonstrate one approach to this.

create table #Products

(

ProductID int...

sammyIT (7/11/2012)

---

Sorry about that.

Table "Product" has items with descriptions.

Table "Keywords" is a running list of keywords that we would want to exclude from the outcome of some queries.

What I...

This has pretty much every format imaginable for datetimes. http://msdn.microsoft.com/en-us/library/ms187928.aspx

cjcolton (7/10/2012)

---

Hey Bob,

Thanks so much for your help; just to clarify, is the first step is to create a new table with the same records but a new primary?...

As Jason already said you need to wrap your sum with an ISNULL.

Select ISNULL(SUM(ISNULL(Amount, 0)), 0)