-- =============================================
-- Author: <George Jeffcock>
-- Create date: <2 Feb 2016>
-- Description: <Get API Message by ID>
-- =============================================
CREATE PROCEDURE [dbo].[usp_GetAPIMessage]
@MessageID varchar(7)
,@ReturnInteger numeric(4,0) = 0 Output
,@MessageText256 varchar(256) = '' Output
,@MessageSeverity numeric(1,0) = 0 Output
,@MessageReplaceMarkers numeric(1,0) = 0 Output
AS
SET NOCOUNT ON
IF @MessageID = ''
BEGIN
SET@ReturnInteger = 99
RETURN 99
END
ELSE
BEGIN
SELECT TOP 1 @MessageID = MessageID, @MessageText256 = MessageText256, @MessageSeverity = MessageSeverity, @MessageReplaceMarkers = MessageReplaceMarkers FROM APIMSG WHERE MessageID = @MessageID ORDER BY MessageID
DECLARE @PreserveROWCOUNT INT = @@ROWCOUNT
END
IF @@ERROR <> 0
BEGIN
SET @ReturnInteger = -1
RETURN -1
END
ELSE
BEGIN
IF @PreserveROWCOUNT = 0
BEGIN
SET @ReturnInteger = 100
RETURN 100
END
ELSE
BEGIN
SET @ReturnInteger = 0
RETURN 0
END
BEGIN
#END
so in the end I kinda doing it twice to satisfy both enviromnents..work in progress moving to NET
Shame looks a little amateur..with the inbuilt Returns integer and our ca plex one @ReturnInteger