February 5, 2017 at 1:12 am
Hi,
I hope I am in the right place to post this question. If not, please direct me to the right place.
My problem stems from having difficulty in getting the output from a computed value in Stored Procedure
Basically, I have created a table that has a computed column value as follows :
CREATE TABLE [dbo].[MASTER_EPMTID](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Descriptor] AS ('PMP-' + [RANGE] + '-' + right('00000' + cast(id as varchar(5)), 5)) PERSISTED,
[RANGE] VARCHAR(50) NULL,
CONSTRAINT [PK_MASTER_EPMTID] PRIMARY KEY ([ID] ASC)
)
GO
And then I created a unique Index which will I need it to be an output later on for me to draw it out in my method in cs code
But, actually I am not quite sure about this Unique_Index where I have put as follows
CREATE UNIQUE INDEX [UK_Master_EPMTID] ON [dbo].[Master_EPMTID]
(
[Descriptor] ASC
)
GO
All goes well. The table is created.
I went on to create a Stored Procedure which I will be used in my method in cs so that each time I call the stored Procedure, the EPMTID (which is the Descriptor) will be inserted
SE [test]
GO
/****** Object: StoredProcedure [dbo].[insert_EPMTID] Script Date: 5/2/2017 3:37:35 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[insert_EPMTID]
-- Add the parameters for the stored procedure here
@NEWEPMTID varchar(50) output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @Id INT
SET IDENTITY_INSERT [dbo].[MASTER_EPMTID] OFF
-- Insert statements for procedure here
INSERT INTO MASTER_EPMTID([Range]) values(CONVERT(VARCHAR,RIGHT('0' + RTRIM(YEAR(GETDATE())), 2)) + '-' + CONVERT(VARCHAR,RIGHT('0' + RTRIM(MONTH(GETDATE())), 2)) )
SET @ID = SCOPE_IDENTITY()
SELECT Descriptor from Master_EPMTID where ID = @Id
END
However, when I executed the above the return value is null which shows that the Stored Procedure is wrong which I can't place my fingers where.
Hope someone can help me out on this. Tks.
February 5, 2017 at 8:55 am
karenworld - Sunday, February 5, 2017 1:12 AMHi,
I hope I am in the right place to post this question. If not, please direct me to the right place.
My problem stems from having difficulty in getting the output from a computed value in Stored ProcedureBasically, I have created a table that has a computed column value as follows :
CREATE TABLE [dbo].[MASTER_EPMTID](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Descriptor] AS ('PMP-' + [RANGE] + '-' + right('00000' + cast(id as varchar(5)), 5)) PERSISTED,
[RANGE] VARCHAR(50) NULL,
CONSTRAINT [PK_MASTER_EPMTID] PRIMARY KEY ([ID] ASC)
)GO
And then I created a unique Index which will I need it to be an output later on for me to draw it out in my method in cs code
But, actually I am not quite sure about this Unique_Index where I have put as follows
CREATE UNIQUE INDEX [UK_Master_EPMTID] ON [dbo].[Master_EPMTID]
(
[Descriptor] ASC
)GO
All goes well. The table is created.
I went on to create a Stored Procedure which I will be used in my method in cs so that each time I call the stored Procedure, the EPMTID (which is the Descriptor) will be insertedSE [test]
GO
/****** Object: StoredProcedure [dbo].[insert_EPMTID] Script Date: 5/2/2017 3:37:35 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[insert_EPMTID]
-- Add the parameters for the stored procedure here
@NEWEPMTID varchar(50) output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @Id INT
SET IDENTITY_INSERT [dbo].[MASTER_EPMTID] OFF
-- Insert statements for procedure here
INSERT INTO MASTER_EPMTID([Range]) values(CONVERT(VARCHAR,RIGHT('0' + RTRIM(YEAR(GETDATE())), 2)) + '-' + CONVERT(VARCHAR,RIGHT('0' + RTRIM(MONTH(GETDATE())), 2)) )
SET @ID = SCOPE_IDENTITY()
SELECT Descriptor from Master_EPMTID where ID = @Id
ENDHowever, when I executed the above the return value is null which shows that the Stored Procedure is wrong which I can't place my fingers where.
Hope someone can help me out on this. Tks.
What are you trying to return, exactly? Your existing code does two things:
a) It SELECTs the Descriptor of the inserted row
b) It RETURNs the parameter, unchanged, it was executed with (@NEWEPMTID). It does not return NULL (I just executed in on 2016).
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply