Return message from a stored procedure

I've written a lot of SQL in the past (Access and SQL Server), but do not have a lot of T-SQL stored procedure experience.  I currently have a stored procedure which does not return any results to the pass-thru query which runs it.  this stored procedure looks like:
ALTER PROCEDURE [dbo].[df_Upload_WellData]
		@DS_ID integer,
		@Entity_Type_ID integer,
		@DS_PK_Integer integer, 
		@DS_PK_Text nvarchar(50),
		@Field_ID integer,
		@Entity_Name nvarchar(75),
		@Common_Name nvarchar(75), 
		@Bolo_ID nvarchar(10),
		@State_CD nvarchar (10),
		@County_ID integer,		
		@API nvarchar (20),
		@Status_ID integer,
		@Eff_Date Date,
		@Thru_Date Date
		
AS
 
Declare @Entity_ID integer

SET NOCOUNT ON

INSERT INTO [WHR_System_Tables].dbo.tbl_sysEntities (
	DS_ID, DS_PK_Integer, DS_PK_Text, Entity_Type_ID, Entity_Name, County_ID, 
	API, Status_ID, Eff_Date, Thru_Date, Parent_Entity_ID, Bolo_ID)
VALUES (@DS_ID, @DS_PK_Integer, @DS_PK_Text, @Entity_Type_ID, @Entity_Name,
		@County_ID, @API, @Status_ID, @Eff_Date, @Thru_Date, @Field_ID, @Bolo_ID)
		        
SET @Entity_ID = Scope_Identity()

INSERT INTO [WHR_System_Tables].dbo.tbl_sysWells (
		Entity_ID, DS_ID, DS_PK_Integer, DS_PK_Text, Bolo_ID, Common_Name,
		State_CD, County_ID, API)
VALUES (@Entity_ID, @DS_ID, @DS_PK_Integer, @DS_PK_Text, @Bolo_ID, @Common_Name,
		@State_CD, @County_ID, @API)

Open in new window

It works well, but I would like to add some functionality to the SP.  What I would like to do is modify this stored procedure so that it:
1.  Performs a test to determine whether the combination of DS_ID and Bolo_ID already exist in tbl_sysEntities.  If so, return bypass the insertion process and return a message: "Record not added, duplicates in DS_ID and Bolo_ID"
2.  If no record exists, run the code shown above to insert the record into both tables and return a message: "Record added"
3.  If some other error occurs during either of the two insert processes, return an error message indicating the cause of the error.

Thanks for you assistance.
LVL 50
Dale FyeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargBusiness Intelligence DeveloperCommented:
Hello,

Please find the query for the same ..

ALTER PROCEDURE [dbo].[df_Upload_WellData]
		@DS_ID integer,
		@Entity_Type_ID integer,
		@DS_PK_Integer integer, 
		@DS_PK_Text nvarchar(50),
		@Field_ID integer,
		@Entity_Name nvarchar(75),
		@Common_Name nvarchar(75), 
		@Bolo_ID nvarchar(10),
		@State_CD nvarchar (10),
		@County_ID integer,		
		@API nvarchar (20),
		@Status_ID integer,
		@Eff_Date Date,
		@Thru_Date Date
		
AS
 
Declare @Entity_ID integer

SET NOCOUNT ON

BEGIN TRANSACTION 
BEGIN TRY
	IF NOT EXISTS (SELECT 1 FROM [WHR_System_Tables].dbo.tbl_sysEntities WHERE DS_ID = @DS_ID AND Bolo_ID = @Bolo_ID)

BEGIN
INSERT INTO [WHR_System_Tables].dbo.tbl_sysEntities (
	DS_ID, DS_PK_Integer, DS_PK_Text, Entity_Type_ID, Entity_Name, County_ID, 
	API, Status_ID, Eff_Date, Thru_Date, Parent_Entity_ID, Bolo_ID)
VALUES (@DS_ID, @DS_PK_Integer, @DS_PK_Text, @Entity_Type_ID, @Entity_Name,
		@County_ID, @API, @Status_ID, @Eff_Date, @Thru_Date, @Field_ID, @Bolo_ID)
		        
SET @Entity_ID = Scope_Identity()

INSERT INTO [WHR_System_Tables].dbo.tbl_sysWells (
		Entity_ID, DS_ID, DS_PK_Integer, DS_PK_Text, Bolo_ID, Common_Name,
		State_CD, County_ID, API)
VALUES (@Entity_ID, @DS_ID, @DS_PK_Integer, @DS_PK_Text, @Bolo_ID, @Common_Name,
		@State_CD, @County_ID, @API)

PRINT 'Record added'

END       
ELSE
BEGIN
PRINT 'Record not added, duplicates in DS_ID and Bolo_ID'
END 
END TRY
BEGIN CATCH
	SELECT @@ERROR
END CATCH
commit        

Open in new window

0
ste5anSenior DeveloperCommented:
Use the return value of the stored procedure, otherwise it's getting harder..

E.g.
 
ALTER PROCEDURE dbo.df_Upload_WellData
    @DS_ID INTEGER ,
    @Entity_Type_ID INTEGER ,
    @Entity_Name NVARCHAR(75) ,
    @Common_Name NVARCHAR(75) ,
    @Bolo_ID NVARCHAR(10)
AS
    BEGIN TRY                                                         
        SET NOCOUNT ON;
        SET XACT_ABORT ON;

	-- Constants.
        DECLARE @NO_ACTION INT = 1;
        DECLARE @NO_ERROR INT = 0;
        DECLARE @GENERIC_ERROR INT = 55555;

	-- Variables.        
        DECLARE @Result INT = @NO_ERROR;

	-- Use explicit transaction.
        BEGIN TRANSACTION;

        INSERT  INTO [WHR_System_Tables].dbo.tbl_sysEntities
                ( DS_ID ,
                  Entity_Type_ID ,
                  Entity_Name ,
                  Bolo_ID
                )
                SELECT  DS_ID ,
                        Entity_Type_ID ,
                        Entity_Name ,
                        Bolo_ID
                FROM    ( VALUES ( @DS_ID , @Entity_Type_ID , @Entity_Name , @Bolo_ID) ) I ( DS_ID, Entity_Type_ID, Entity_Name, Bolo_ID )
                WHERE   NOT EXISTS ( SELECT *
                                     FROM   [WHR_System_Tables].dbo.tbl_sysEntities E
                                     WHERE  E.DS_ID = I.DS_ID
                                            AND E.Bolo_ID = I.Bolo_ID );

        IF ( @@ROWCOUNT = 0 )
            BEGIN 
                SET @Result = @NO_ACTION;
            END;
        ELSE
            BEGIN
                INSERT  INTO [WHR_System_Tables].dbo.tbl_sysWells
                        ( Entity_ID ,
                          DS_ID ,
                          Bolo_ID ,
                          Common_Name 
                        )
                VALUES  ( SCOPE_IDENTITY() ,
                          @DS_ID ,
                          @Bolo_ID ,
                          @Common_Name 
                        );
            END;            

	-- Use explicit transaction.
        COMMIT TRANSACTION;

	-- Single point of exit.
        RETURN @Result;

    END TRY  
    BEGIN CATCH
        IF ( @@trancount > 0 )
            ROLLBACK TRANSACTION;
        -- EXECUTE dbo.p_ThrowError;
        RETURN @GENERIC_ERROR;
    END CATCH;

Open in new window


and execute it as PT with:

DECLARE @Result INT;

EXECUTE @Result = dbo.df_Upload_WellData @DS_ID = 1, @Entity_Type_ID = 2, @Entity_Name = 'EN', @Common_Name = 'CN', @Bolo_ID = 3;

SELECT  @Result AS [Result];

Open in new window

0
BitsqueezerCommented:
Hi,

if you want to return a message to the frontend you can use a return value like ste5an recommended, in the end you must read out this value in the frontend and replace it with messages here. Disadvantage is that you need to redploy a new frontend if you want to change the message.

I always use a "@strError" parameter defined as OUTPUT and nvarchar(MAX). This gives you the possibility to fill it with an error message coming from the SP so whenever you must change the SP you can adjust the message or add new messages. The frontend only needs to check this parameter if it is not empty and in this case display it. Another advantage of this method is that it is easy to implement a translation function in the SP which translates the error message into the language of the user. For this purpose, for security check and for further history purposes I also always send the user ID as a further parameter for each SP.

The way SQL Server defines is a little bit more complicate, you can extend the internal error messages with own messages and an own error code, then you can use the error raise method of T-SQL to forward an error. That gives you also the possibility to set a severity of the the error and let BeginTry/Catch processes react on the error - I personally find this way as too complicate for every day use.

Cheers,

Christian
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

David ToddSenior DBACommented:
Hi,

You can pass values back out of a stored procedure as an output parameter and still return a result set. ie, a status value indicating if the results are good or best efforts or complete garbage say.

HTH
  David
0
ste5anSenior DeveloperCommented:
@Bitsqueezer and@David:

When you have to support more than one language, it is easier to translate a number in the front-end. Even when it seems to be more work.

But you're both right for using such messages for development and logging, cause then you can stick to one language.
0
BitsqueezerCommented:
Hi ste5an,

not really. If you use a general translation procedure in the backend you only need to translate all the messages once for all kind of frontends. Additional return values are good to give the frontend the possibility to react on the error depending on the number. Moreover you can use a simple short translation key which describes the error in the SP itself like "FileNotFound" which is translated to "Sorry, the file xyz could not be found at the desired file server." or anything like that, and additionally into different languages, and additionally using formatting like HTML or anything else.

My current project additionally uses an application key so the message is translated into HTML, .NET style like used in WPF and simple text depending on which application started the SP. This is all coded into a TicketGUID which is a lookup key for the login table where all the parameters about the application and the user is described so the translation function can format the message in the right way and right language using translation tables. If there is a typo in a message I can open the translation table and change the text, or change the formatting to underline a word or extend the text or add a help URL or anything like that.
If I would have done that in the frontend I would need to change this and maybe further frontends so that it can output the desired text - this would end in a horrible change scenario. Instead I open my translation table and change whatever I want. Especially if I need to add a new language for all the texts this is extremely helpful and keeps the frontends small and easy to manage.

Cheers,

Christian
0
Dale FyeAuthor Commented:
Christian,

"I always use a "@strError" parameter defined as OUTPUT and nvarchar(MAX)"

I believe this implies a declaration like:

ALTER PROCEDURE dbo.df_Upload_WellData
    @DS_ID INTEGER ,
--other parameters listed here
    @Bolo_ID NVARCHAR(10),
    @strError nvarchar(max) OUTPUT

For simplicity sake I assume that in my IF statement I would simply set the value of this parameter like:

SET @strError = "Data added"
or
SET @strError = "Unable to add record due to duplicate values in the DS_ID and Bolo_ID fields"

How do I get that value into a variable in Access?  Do I configure the pass-thru query that is calling the procedure as Returns Records?

exec df_Upload_WellData @DS_ID = 4, @OtherParam = 2, @Bolo_ID = 1, @strError = NULL

Dale
0
ste5anSenior DeveloperCommented:
E.g.

DECLARE @Result INT;
DECLARE @Error NVARCHAR(255);

EXECUTE @Result = dbo.df_Upload_WellData @DS_ID = 1, @Entity_Type_ID = 2, @Entity_Name = 'EN', @Common_Name = 'CN', @Bolo_ID = 3, @strError = @Error OUTPUT;

SELECT  @Result AS [Result] ,
        @Error AS [Output];

Open in new window

0
BitsqueezerCommented:
Hi Dale,

yes, that's correct, the way how to define that and set the value for the error.

In the EXEC command you need to add the word "OUTPUT" to the parameter:

EXEC df_Upload_WellData @DS_ID = 4, @OtherParam = 2, @Bolo_ID = 1, @strError = NULL OUTPUT

Open in new window

Otherwise the parameter cannot be read out.

Depending on if the SP outputs records or not you need to set it to output records - that means, only if it uses a SELECT. That's not needed for OUTPUT parameters of SPs.

If you want to use DAO to execute it is a little bit complicate to get the return value, because DAO unfortunately doesn't support OUTPUT variables. Microsoft has a solution which is...emmmm... not really usable. Look here: https://support.microsoft.com/en-us/kb/168210

If you use ADO instead it is really simple. You create an ADO command object, fill in the parameters after using "Parameters.Refresh" and after executing the command (where you only need to specify the name of the procedure, so no complicate string assembling necessary) you can read out the OUTPUT-parameters through the parameters collection of the command object, either by using the index or parameter name (recommended).
Example 1 from MS: https://technet.microsoft.com/en-us/library/aa905903%28v=sql.80%29.aspx
Example 2 from MS: https://support.microsoft.com/en-us/kb/185125

Example 1 shows it by defining the parameters in VBA, that has the advantages that you need no extra roundtrip to get the parameters definition and that you don't need the permission to view the definition on the server but the disadvantage that you need to write a lot of text.
Example 2 uses Parameters.Refresh, that loads the parameters from the SP's definition into the collection so you can fill it.

ADO has the advantage that you can also create a recordset in the SP and assign it to the recordset property of a form in Access so that it is updatable, this is not possible with DAO pass-through queries which are always read-only (a restriction which I do not really understand as it is not needed to restrict that and would open a lot more possibilities in DAO). To create updatable recordsets with ADO you need to use adUseServer and SQLOLEDB driver (Access needs that for forms).

Cheers,

Christian
0
Dale FyeAuthor Commented:
Thanks, guys.  Very useful discussion.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.