How does SSMS knows about the scalar functions ?

  • karenworld

    Mr or Mrs. 500

    Points: 554

    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.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182347

    The function is executed by this line of code in the procedure
    😎

    set @NStatusID =dbo.ePMT_findStatus(@action, @status)

  • karenworld

    Mr or Mrs. 500

    Points: 554

    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");

    }

    }

    }

  • Eirikur Eiriksson

    SSC Guru

    Points: 182347

    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?.

  • Jeff Moden

    SSC Guru

    Points: 994556

    karenworld - Sunday, February 5, 2017 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.

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply