September 15, 2011 at 2:56 pm
yeah, have to do it through cursors
September 15, 2011 at 3:01 pm
hardial_bhatia (9/15/2011)
yeah, have to do it through cursors
WOW!!! The logic in this processing is row by row when all that needs to happen is two single update statements. If you have a million rows you have execute two million update statements. I guess that decision is up to you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 15, 2011 at 3:10 pm
Try this:
ALTER PROCEDURE [dbo].[abc_specialty1_sp_]
@sysID bigint,
@qnxtStatus char(15),
@ReturnCode AS INT OUTPUT,
@ErrMsg nvarchar(4000)OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRAN T1
BEGIN TRY
UPDATE abc_specialty_tbl
SET code =
CASE
WHEN NPVS.code IS NOT NULL THEN NPVS.specialt
ELSE NPS.specialty
END,
type =
CASE
WHEN NPVS.type IS NOT NULL THEN NPVS.type
ELSE NPS.type
END,
effdate =
CASE
WHEN NPVS.effdate IS NOT NULL THEN NPVS.effdate
ELSE NPS.effdate
END
FROM abc_specialty_tbl(NOLOCK) AS NPVS
INNER JOIN CombinedDriver3_tbl(NOLOCK) AS DVR
ON DVR.sysID=NPVS.sysID
WHERE DVR.sysID=@sysID
AND DVR.qnxtStatus=@qnxtStatus
END TRY
-----------------------------
BEGIN CATCH
SET @ReturnCode = 2 --Code for failure
SET @ErrMsg = ERROR_MESSAGE()
IF @@TRANCOUNT>0 and @@ERROR<>0
ROLLBACK TRAN T1
SET @ReturnCode = 2 --Code for failure
SET @ErrMsg = ERROR_MESSAGE()
END CATCH
if @@trancount>0
COMMIT TRAN T1
if @qnxtStatus='Success'
begin
SET @ReturnCode = 0 --Code for success
end
if @qnxtStatus='Warning'
begin
SET @ReturnCode = 1 --Code for success
end
END
It may need some adjusting, but I think it is better. Then see if everything works with the other sp.
Jared
Jared
CE - Microsoft
September 15, 2011 at 3:56 pm
No its not working
Viewing 4 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply