Using Output Parameters In A Stored Procedure

Hi Guys.

I'm having some difficulties coding a stored procedure that has output parameters and I'm hoping that someone can point out what I'm doing wrong. The procedure is coded based on an example found on the net. The error I am getting is procedure has too many arguments specified.

Here is the code for the procedures. Any help you can provide is appreciated.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
IF OBJECT_ID('sp_NewKey') IS NOT NULL
BEGIN
DROP PROCEDURE sp_NewKey
END
GO
CREATE PROCEDURE sp_NewKey
-- Add the parameters for the stored procedure here
@InCounterName nvarchar(50),
@InCounterPrefix nvarchar(4)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @CounterName nvarchar(50)
DECLARE @Prefix nvarchar(4)
DECLARE @CurrentValue Integer
DECLARE @MaxValue Integer

--- Check To See If Requested Counter Record Exists. If It Exists Retrieve The Current Values
--- If It Does Not Them Insert A New Counter Record Into The Counters Table With Default Values
IF (SELECT COUNT(*) FROM tblCounters WHERE CounterName = @InCounterName) = 0
BEGIN
--- CAll Stored Procedure Insert New Counter Record Into Counter Table
EXECUTE ins_tblCounters @InCounterName,@InCounterPrefix,0,999
END
ELSE
BEGIN
--- Call Stored Procedure To Retrieve Values For Requested Counter Record
EXECUTE sel_tblCountersByCounterName @InCounterName, @OutCounterName = @CounterName Output, @OutPrefix = @Prefix Output, @OutCurrentValue = @CurrentValue Output, @OutMaxValue = @MaxValue Output
END
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: V. Shane Curtis
-- Create date: <Create Date,,>
-- Description: Select Counter Record By Counter Name
-- =============================================
IF OBJECT_ID('sel_tblCountersByCounterName') IS NOT NULL
BEGIN
DROP PROCEDURE sel_tblCountersByCounterName
END
GO
CREATE PROCEDURE sel_tblCountersByCounterName
-- Add the parameters for the stored procedure here
@InCounterName nvarchar(50),
@OutCounterName nvarchar(50) Output,
@OutPrefix nvarchar(4) OutPut,
@OutCurrentValue Integer OutPut,
@OutMaxValue Integer Output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT @OutCounterName = CounterName, @OutPrefix = Prefix, @OutCurrentValue = CurrentValue, @OutMaxValue = MaxValue
FROM tblCounters
WHERE CounterName = @InCounterName;
END
GO

What about the stored procedure : ins_tblCounters ? Can you post the definition of it ?

Recommend you don't start SProc names with "sp_" as that is used by MS for their Sprocs and will cause additional tests for location (e.g.in master DB) which may impact performance, or make debugging more complicated. Unless you want a "global Sproc" in which case create it in master DB with "sp_" prefix.

This will COUNT all the rows, which takes time. Recommend you use EXISTS instead, which can abort once it finds the first row

Recommend you use named parameters in all calls to Sprocs. Are the other of these right?

EXECUTE ins_tblCounters 
    @InCounterName = @InCounterName
    ,@OutCounterName = @InCounterPrefix
    ,@OutPrefix = 0
    ,@OutCurrentValue = 999
--   @OutMaxValue not provided

All parameters are defined as requiring values, so this will result in an error and thus fail-safe, but IME it takes longer to debug when the parameters are not named and their order is wrong in some way

Naming parameters also safeguards against the defined order of parameters being changed in future, or an extra one "added in"

Please post the actual error - otherwise we are guessing which SProc it is referring to. It may be the mismatched parameters I mentioned above.

Ah ... ignore that, I misread ins_tblCounters as sel_tblCountersByCounterName ... they may well have different parameters / sequence of their parameters

Illustrates that not naming parameters leads to this type of debugging issue though.

Thanks for the reply. The naming convention is temporary. I prefer to use three position prefix and haven't decided on a convention for driver SProcs yet, but thank you for the heads up. Not dealing with the insert procedure yet, just trying to figure out the cause of this error before I proceed any further in the coding of this driver SProc. I will add the named parameters for the current hard-coded values and see if that makes a difference.

Hi All,

I've modified the driver SProc to provide named parameters for the insert procedure and it did not address the problem with the select procedure. The insert procedure is not producing any errors only the select procedure. But in any case here is the modified code. The objective of the select procedure is if the counter record exists I want to access all retrieved values and store them in variables so they can be used later in the process. The driver SProc is not complete by any means, just simply trying to figure out what this problem is before going any further.

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
IF OBJECT_ID('sp_NewKey') IS NOT NULL
BEGIN
DROP PROCEDURE sp_NewKey
END
GO
CREATE PROCEDURE sp_NewKey
-- Add the parameters for the stored procedure here
@InCounterName nvarchar(50),
@InCounterPrefix nvarchar(4),
@InCurrentValue integer = 0,
@InMaxValue Integer = 999

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @CounterName nvarchar(50)
DECLARE @Prefix nvarchar(4)
DECLARE @CurrentValue Integer
DECLARE @MaxValue Integer

--- Check To See If Requested Counter Record Exists. If It Exists Retrieve The Current Values
--- If It Does Not Them Insert A New Counter Record Into The Counters Table With Default Values
IF (SELECT COUNT(*) FROM tblCounters WHERE CounterName = @InCounterName) = 0
BEGIN
--- CAll Stored Procedure Insert New Counter Record Into Counter Table
EXECUTE ins_tblCounters @InCounterName,@InCounterPrefix,@InCurrentValue,@InMaxValue
END
ELSE
BEGIN
--- Call Stored Procedure To Retrieve Values For Requested Counter Record
EXECUTE sel_tblCountersByCounterName @InCounterName, @OutCounterName = @CounterName Output, @OutPrefix = @Prefix Output, @OutCurrentValue = @CurrentValue Output, @OutMaxValue = @MaxValue Output
END
END
GO

If the select procedure is producing an error it would help if you post it, verbatim here. Otherwise we'll just be guessing.

If an Sproc is not doing what you want put some PRINT statements in it so you can see what is going on (or more likely "not going on"!)

The SProc compiled successfully but the editor says that there is an error. sel_tblCountersByCounterName has too many parameters specified,

Ignore the editor, run it and see what error message you get. That may clarify what the problem is. Post the error message (verbatim, in full) here.

Name all the parameters (i.e. EXEC MySproc @SProcParam1 = @MyLocalParamName1 and make sure they a) match the definition and b) all the required parameters, at least, are satisfied.

make sure you include the schema prefix (most likely "dbo.") on all objects otherwise there might be some other variant stuck in some other schema, particularly whatever is your default (if that is not "dbo"), which SQL is finding instead of the one you think it is finding. Avoiding that pitfall is one of the reasons why it is a good idea to prefix all objects with their schema, even if the schema is always "dbo"; you indicated that you will do that later, but IME having the coding habit of doing it from the outset (even if you change your mind later) prevents a variety of SNAFUs

So the editor lies? How lovely... All good points and thank you. I'm not at a point where I can run this yet. But once I am I'll post what is necessary. As for the naming issue, I understand your point but at the time the SProc had not even been saved to the database and I had not intended to do so until I had settled on a naming convention. Thanks for your continued support

Not necessarily, but its only as good as the cheat-sheets it is using, and the parser. That's very handy when writing code for catching things that are easily fixed, and might not be spotted, but no substitute IME for actually running the object and debugging it in a conventional way. Of course I measure my productivity by how much time I spend "writing" and how little time I spend "fixing", and over the years the use of improved programmer tools has no doubt helped a lot, but I think I have got a lot more benefit from experience, good coding standards and defensive programming techniques - those things protect me from myself far better than the Editor can!

Even if not, if you suspect that there is a simple "parameter error", as the editor suggests, might be worth running it just to see what sort of error message you get. (It still might not be any help ... cascade errors can obfuscate the root-cause of the problem, of course). I think that would he worthwhile whilst the issue, and surrounding code, is still fresh in your memory.

If there are possible side effects, which would be a reason not to try testing it of course :), then using a transaction block is likely to keep the outcome safe (provided that the rest of the code is not littered with NOLOCK, and beware that any INSERTS into tables with IDENTITY will consume ID numbers, even though teh statement is rolled back)

BEGIN TRANSACTION

... test statements here ...

ROLLBACK

P.S. Or create a synthetic test constructed based on just the (perceived) erroneous line of code, to see whether the error message you get clarifies the problem.

Thanks for your input. No IDENTITY values being used in my database. The whole purpose of this block of code is to generate a key value for each record in my primary table. Sort of a customized sequence generator.