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