SQL Rowcount always returning 1 on insert into command

All I want to do is set a variable to the count of rows affected by the transaction and send that to a table. I don't know why I'm having trouble, but here is my code

Begin

Declare @errDate datetime
Declare @RowCount nvarchar(10)
Declare @Message nvarchar(max)
Declare @startproc datetime
Declare @endproc datetime
Declare @duration int
set @startproc = getdate()

Declare @myDate Datetime

Set @myDate = CAST(CONVERT(varchar,getdate()-1, 112)   +' ' +'00:00 AM'AS DATETIME)

DELETE FROM [Cal_CommOps].[dbo].[Progression_PreCall_CaliforniaECH]
         Where segStart_date = @myDate

INSERT INTO [Cal_Co开发者_如何学JAVAmmOps].[dbo].[Progression_PreCall_CaliforniaECH]
           ([callid]
           ,[segstart_date]
           ,[dialed_num]
           ,[origlogin]
           ,[duration])

SELECT  *
FROM               (
Select callid,
         CAST ([SEGSTART_D] as datetime) as segstart_date,
         RIGHT(dialed_num, 10) as dialed_num,
         origlogin,
         duration
FROM [CMS_ECH] ECH
Inner join [Cal_CommOps].[dbo].[CommOps_Emp] EMP on EMP.extension = ECH.origlogin
AND Dept in ('Pre-Call')
Where [SEGSTART_D] = @myDate and dialed_num <> ''

UNION

Select callid,
         CAST ([SEGSTART_D] as datetime) as segstart_date,
         RIGHT(calling_pty, 10) as dialed_num,
         anslogin,
         duration
FROM [CMS_ECH] ECH
Inner join [Cal_CommOps].[dbo].[CommOps_Emp] EMP on EMP.extension = ECH.anslogin
AND Dept in ('Pre-Call')
Where [SEGSTART_D] = @myDate and calling_pty <> ''

                  ) as myCalls

Set @RowCount = @@rowcount
Set @endproc = getdate()
Set @duration = DATEDIFF(minute, @startproc, @endproc)
Set @errDate = getdate()

Exec usp_myDashboardInsert 'PreCall_ECH', @@Rowcount, @errDate, @duration
End

Even if I try

Exec usp_myDashboardInsert 'PreCall_ECH', @Rowcount, @errDate, @duration

I still get 1.

Why is this and how do I fix it?

If I change the code and add Select @@ROWCOUNT before Set @RowCount = @@rowcount then it works to select it, but I still want it in a variable.

Answer: I guess it was the case of @@ROWCOUNT I was using @@Rowcount and it wanted @@ROWCOUNT.

---

This will definitely not work:

Exec usp_myDashboardInsert 'PreCall_ECH', @@Rowcount, @errDate, @duration

because @@Rowcount gets set to 1 by the assignment statements. So the first step is to change your code to this and stick with it:

Exec usp_myDashboardInsert 'PreCall_ECH', @Rowcount, @errDate, @duration

If this still doesn't give you the expected result, you may find that your query logic is unexpectedly selecting 1 row, as @StarShip3000 suggests.

---

I'm not seeing the issue at first glance. I did create a simple test and it seems to work fine. Run the example below for a sanity check and see if it returns 2? If you run your select without the insert your sure it returns more than 1 row?

http://technet.microsoft.com/en-us/library/ms187316.aspx

 CREATE TABLE #Test(id INT, FirstName VARCHAR(MAX))

DECLARE @RowCount nvarchar(10)

INSERT INTO #Test(id,FirstName)
SELECT *
FROM (SELECT 1 as id, 'Fred' as FirstName
      UNION
      SELECT 2,'Dora') as People


SET @RowCount = @@rowcount

SELECT @RowCount

UPDATE:

The issue is your call here

Exec usp_myDashboardInsert 'PreCall_ECH', @@Rowcount, @errDate, @duration

Your doing a @@RowCount not a @RowCount. Your @@RowCount in the proc is giving the count from Set @errDate = getdate()

You need to change the proc input parameter to @RowCount.

In your question to me I thought the proc call was an extra thing you threw in to make your point not the actual part that was going bat crazy on you. Meaning your @@RowCount after the insert will contain the correct value.

---

Turns out it was the case of @@Rowcount, it must be @@ROWCOUNT