I just attached a stored procedure which I am trying to execute in management studio. I am using a cursor to update the table in sql server 2005.
exec EXEC [sp_SiteUpdate] @PaymentID = 123456.
I get the message 'Command(s) completed successfully.' though the table is not updated
But if I execute the same stored procedure in debug mode in visual studio, I am able to update the table and the message I get,
'(3 row(s) affected)
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[sp_SiteUpdate].
The thread 'db01 ' (0x13c4) has exited with code 0 (0x0).
The program ' [SQL] db01: db01' has exited with code 0 (0x0).
Does anyone know what's wrong with the stored procedure?
CREATE PROC [dbo].[sp_SiteUpdate](
--SET NOCOUNT ON
DECLARE @LeanLoadID nvarchar(50)
DECLARE @InvoiceSite nvarchar(50)
DECLARE @GroupInfo nvarchar(50)
DECLARE @RowsAffected int
SET @RowsAffected = 0
DECLARE invoicesite_cursor CURSOR FOR
SELECT leanloadID FROM PAYMENT LP,LOAD LL
WHERE LP.PAYMENTID = LL.PAYMENTID AND LP.PAYMENTID = @PaymentID
ORDER BY LL.LEANLOADID
WHILE @@FETCH_STATUS = 0
FETCH NEXT FROM invoicesite_cursor
SELECT @InvoiceSite = S.LOCATIONREF,@GroupInfo = GROUPINFORMATION
FROM LEANWS_LOAD L
LEFT OUTER JOIN
(SELECT LOCATIONREF,LEANLOADID FROM STOP WHERE LOCATIONREF IN('1','2','3') AND LEANLOADID = @LeanLoadID) AS S ON L.LEANLOADID = S.LEANLOADID
WHERE L.LEANLOADID = @LeanLoadID
SET @InvoiceSite = ISNULL(@InvoiceSite,0)
IF @InvoiceSite = '1'
SET @InvoiceSite = 'TXHQ'
ELSE IF @InvoiceSite = '2'
SET @InvoiceSite = 'KNOX'
ELSE IF @InvoiceSite = '3'
SET @InvoiceSite = 'ARIZ'
SET @InvoiceSite = @GroupInfo
UPDATE LOAD SET INVOICESITE = @InvoiceSite
WHERE LEANLOADID = @LeanLoadID
SET @RowsAffected = @@ROWCOUNT