February 5, 2017 at 11:44 pm
Hi expert,
I have this reference code which make use of Scalar functions and stored procedure.
The thing is that I can't see how is the scalar functions being executed by the Stored Procedure.
-- =============================================
ALTER FUNCTION [dbo].[EPMT_findStatus]
(
-- Add the parameters for the function here
@Action varchar(20),
@status int
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @NStatusID int
-- Add the T-SQL statements to compute the return value here
SELECT @NStatusID=Next_Workflow_TaskID from [SSMC_EPMT].[dbo].[EPMT_MASTER_WORKFLOW]
WHERE Workflow_TaskID =@status and
[Action]=@Action
-- Return the result of the function
RETURN @NStatusID
END
And then I have this stored Procedure which make use of this Scalar function
PROCEDURE [dbo].[ePMT_SP_UpdateEPMTRequest]
-- Add the parameters for the stored procedure here
(
@action varchar(20),
@year int,
@ePMTNumber varchar(20),
@iSGGID varchar(20),
@name varchar(100),
@empID int,
@email varchar(100),
@dept varchar(100),
so on and so for
@status int
)
AS
BEGIN
declare @NStatusID int
set @NStatusID =dbo.ePMT_findStatus(@action, @status)
if @action='Save' or @action='Submit'
begin
Insert into SSMC_EPMT.dbo.EPMT_TBL_REQUEST
([EPMTID],[Year],
[IssuerSGGID],[IssuerName],[IssuerEmpID],[IssuerEmail],[IssuerDept],[IssuerSection],
[CostCenter],[CategoryName],[issuerPhoneExt],[EPMTPurpose]
,
[Status])
Values
(
@ePMTNumber,YEAR(getdate()),@iSGGID,@name,@empID,@email,@dept,@section,
@costctr,@category,@phoneExt,
@epmtPurpose,
@NStatusID
)
so on and so for
Hope someone can tell me. Tks.
February 6, 2017 at 1:49 am
Tks Eirikur,
I am so pressed by this deadline that I can't concentrate and looked at things.
Now, can I know if based on the above Stored Procedure, if the action is 'Save', can I say the data is supposed to go into the database table SSMC_EPMT.dbo.EPMT_TBL_REQUEST ?
Cos I have some fields which are hidden and I do not have data for these hidden fields yet due to the stage of development now.
But, I would like to see if the Save event button will work.
Sorry but this is really a asp.net related question....and I duno if I can post question like this here.
Here's the event button that I am trying to execute but there is no data in the database
protected
void btnSave_Click(object sender, EventArgs e)
{
if (Request.QueryString["EPMTID"] != null)
{
string Action = "SaveUpdate";
string EPMTNumber = Request.QueryString["EPMTID"].ToString();
int status = Convert.ToInt32(ViewState["CStatus"].ToString());
UpdateEPMTRequest(Action, EPMTNumber, status);
Response.Redirect("ePMTDraft.aspx?msg=1");
}
else
{
string Action = "Save";
string EPMTNumber;
int status = 0;
if (Request.QueryString["EPMTID"] == null)
{
if (status == 0)
try
{
status = Convert.ToInt32(ViewState["CStatus"].ToString());
// lblstatus.Text = eBiz.findStatus(Convert.ToInt32(ViewState["CStatus"].ToString()));
EPMTNumber = Request.QueryString["EPMTID"].ToString();
UpdateEPMTRequest(Action, EPMTNumber, status);
} catch (Exception ex) { }
Response.Redirect("ePMTDraft.aspx?msg=1");
}
}
}
February 6, 2017 at 2:07 am
If @action='Save' or @action='Submit' then the procedure will add the data to the SSMC_EPMT.dbo.EPMT_TBL_REQUEST table
😎
Suggest you generate some sample data in order to make your ,net code work, otherwise how are you going to test it?.
February 6, 2017 at 6:43 am
karenworld - Sunday, February 5, 2017 11:44 PMHi expert,
I have this reference code which make use of Scalar functions and stored procedure.
The thing is that I can't see how is the scalar functions being executed by the Stored Procedure.
-- =============================================ALTER FUNCTION [dbo].[EPMT_findStatus]
(
-- Add the parameters for the function here
@Action varchar(20),
@status int
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @NStatusID int
-- Add the T-SQL statements to compute the return value here
SELECT @NStatusID=Next_Workflow_TaskID from [SSMC_EPMT].[dbo].[EPMT_MASTER_WORKFLOW]
WHERE Workflow_TaskID =@status and
[Action]=@Action
-- Return the result of the function
RETURN @NStatusID
END
And then I have this stored Procedure which make use of this Scalar functionPROCEDURE [dbo].[ePMT_SP_UpdateEPMTRequest]
-- Add the parameters for the stored procedure here
(
@action varchar(20),
@year int,
@ePMTNumber varchar(20),
@iSGGID varchar(20),
@name varchar(100),
@empID int,
@email varchar(100),
@dept varchar(100),
so on and so for
@status int
)
AS
BEGIN
declare @NStatusID int
set @NStatusID =dbo.ePMT_findStatus(@action, @status)
if @action='Save' or @action='Submit'
begin
Insert into SSMC_EPMT.dbo.EPMT_TBL_REQUEST
([EPMTID],[Year],
[IssuerSGGID],[IssuerName],[IssuerEmpID],[IssuerEmail],[IssuerDept],[IssuerSection],
[CostCenter],[CategoryName],[issuerPhoneExt],[EPMTPurpose]
,
[Status])
Values
(
@ePMTNumber,YEAR(getdate()),@iSGGID,@name,@empID,@email,@dept,@section,
@costctr,@category,@phoneExt,
@epmtPurpose,
@NStatusID
)
so on and so for
Hope someone can tell me. Tks.
I guess I don't understand why you didn't just do a "find" for "ePMT_findStatus". Pressing {ctrl-f} in SSMS is one way to get there.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply