Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2007
    Posts
    18

    Unanswered: Stored Procedure and calling user defined function

    I seem to be getting tasks that I am not familiar with these days. I am a
    guy that has coded it all in the asp page or in the code behind in .NET.
    This problem is outlined below and I need a help / advice on doing this. I
    had the flow of the 3 parts to it expanded below. A call is made to a Stored
    Procedure, The SP then calls a user defined function that runs SQL, this
    returns a 1 or 0 to the SP which then returns the value back to the call on
    the asp page. This is a lot I know but it is the way the lead guy wants it
    done. Any help so I can keep most of the hair I have left is appreciated :-)

    Short list of process flow:

    1. Form.asp calls to rx_sp_HasAccessToClient in SQL SERVER

    2. rx_sp_HasAccessToClient then calls ab_HasAccessToClient

    3. ab_HasAccessToClient runs SQL command on db and sends return bit back to
    rx_sp_HasAccessToClient

    4. rx_sp_HasAccessToClient then sends this back to the call in the Form.asp
    page

    5. Form.asp then checks the Boolean and if 1 then show or if 0 then deny.

    <FLOW WITH CODE AND FUNCTIONS :>

    This is not the correct syntax but is showing what I understand sort of how
    this is to be done so far.

    This panel loads up the Vendors and id's when the user clicks on the link
    "view detailed list of vendors associated with this client". This is the
    beginning of the process.

    This is code in Form.asp

    'PANEL ONE
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
    >

    If ValidateInput(Request.Querystring("Postback"))="Fo rmDetails" then 'Check
    Postback Type

    'We need to load up vendors associated with the current client.

    '--------- CHECK ACCESS HERE via function ab_HasAccessToClient
    --------

    'If the call returns 1, then the employee has access.

    'Otherwise, just write out "Access to this client is denied."

    'CALL SP - Not sure what parameters need to go with it or its syntax

    Execute_SP("rx_sp_HasAccessToClient '" & ClientSSN & "', 1)

    'When it returns can check it here........

    if ab_HasAccessToClient result is a 1 then

    'boolean would be 1 so show panel

    Else

    'boolean would be 0 so show access denied

    'allow them to go back to the original page.

    end if

    'PANEL ONE
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
    >

    ON SQL SERVER: Stored Procedure

    ----------------------------------------------------------
    --------------------------------

    rx_sp_HasAccessToClient

    CREATE PROCEDURE [dbo].[ rx_sp_HasAccessToClient]

    @EmployeeID INT,

    @ClientSSN varchar(50),

    @ReturnBitValue = OUTPUT

    /*

    ' Parameters here passed via call from Form.asp - not sure what is passed
    yet.

    */

    AS

    set nocount on

    /*

    Written by Mike Belcher 9/27/2007 for Form.asp

    'Calls ab_HasAccessToClient function - not sure of the syntax as of yet,
    just making flow.

    'Gets return bit and passes that back to the call from Form.asp

    */

    GO

    ----------------------------------------------------------
    --------------------------------

    ON SQL SERVER: User-Defined Function

    ----------------------------------------------------------
    --------------------------------

    ab_HasAccessToClient

    CREATE FUNCTION ab_HasAccessToClient (@employeeID INT, @ClientSSN
    VARCHAR(50))

    @ClientSSN varchar(50),

    @EmployeeID,

    @ReturnBitValue = OUTPUT

    AS

    SELECT 1

    FROM tblEmployeesClients ec

    INNER JOIN tblClients c ON ec.ClientID = c.ClientSSN

    INNER JOIN tblEmployees e ON ec.Employee = e.EmployeeLogInName

    WHERE e.EmployeeID= @EmployeeID

    AND c.InActiveClient=0

    AND c.ClientSSN = @ClientSSN

    'Some Code here to save result bit ..

    RETURN @ReturnBitValue 'Back to rx_sp_HasAccessToClient

    ----------------------------------------------------------
    --------------------------------

    </FLOW WITH CODE AND FUNCTIONS :>

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Did you have a question?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2007
    Posts
    18
    Well basically:


    1. How to call the stored procedure from asp classic page.
    2. How to call the user defined function from the Stored Procedure.
    3. How to return the bit value from the UDF back to the SP or calling asp page.


    Of course I have very little experience writing SP's and UDF's.

    I am not having a lot of luck doing this.

  4. #4
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    I can't check the syntax at this moment but the answers are something like:

    1. How to call the stored procedure from asp classic page.
    Little shaky on this, something about creating a connection object (ADO), command object and parameter objects (start Googling from here ).
    2. How to call the user defined function from the Stored Procedure.
    Code:
    CREATE PROCEDURE [dbo].[ rx_sp_HasAccessToClient] 
       @EmployeeID     INT,
       @ClientSSN      VARCHAR(50),
       @ReturnBitValue BIT OUTPUT
    AS
    BEGIN
       SET NOCOUNT ON
    
       SET @ReturnBitValue = dbo.dbo.ab_HasAccessToClient (@EmployeeID, @ClientSSN)
    END
    GO
    3. How to return the bit value from the UDF back to the SP or calling asp page.
    Code:
    CREATE FUNCTION dbo.ab_HasAccessToClient (@EmployeeID INT, @ClientSSN VARCHAR(50))
    RETURNS BIT
    AS 
    BEGIN
       DECLARE @ReturnBitValue BIT
       SET @ReturnBitValue = 0
    
       -- Check if access is granted
       IF EXISTS (SELECT 1
                  FROM tblEmployeesClients ec
                     INNER JOIN tblClients c   ON ec.ClientID = c.ClientSSN
                     INNER JOIN tblEmployees e ON ec.Employee = e.EmployeeLogInName
                  WHERE e.EmployeeID     = @EmployeeID
                  AND   c.InActiveClient = 0
                  AND   c.ClientSSN      = @ClientSSN)
       BEGIN
          SET @ReturnBitValue = 1
       END
    
       RETURN @ReturnBitValue -- Back to rx_sp_HasAccessToClient 
    END

  5. #5
    Join Date
    Sep 2007
    Posts
    18
    Thanks a lot, I am working on putting all this together now.

    thanks again.

  6. #6
    Join Date
    Sep 2007
    Posts
    18
    Okay I got the procedures in etc............ now my issue is with calling it. I am getting this error:



    Microsoft VBScript runtime error '800a01f9'
    Invalid or unqualified reference

    /VendorForm.asp, line 102




    Dim objConn
    Dim objCmd

    '[Instantiate objects]
    Set objConn = Server.CreateObject("ADODB.Connection")
    set objCmd = Server.CreateObject("ADODB.Command")
    objConn.Open (AccessConnectionString)

    objCmd.ActiveConnection = AccessConnectionString
    objCmd.CommandText = "rx_sp_HasAccessToClient"
    objCmd.CommandType = adCmdStoredProc

    '[Add Input Parameters]
    objCmd.Parameters.Append .CreateParameter("@EmployeeID", adInteger, adParamInput, 0) ' Line 102 error
    objCmd.Parameters.Append .CreateParameter("@ClientSSN", adVarChar, adParamInput, 255)

    '[Add Output Parameters]
    objCmd.Parameters.Append .CreateParameter("@ReturnBitValue", adInteger, adParamOutput, 0)

    '[Set Parameter Values]
    objCmd.Parameters("@EmployeeID") = EmployeeID
    objCmd.Parameters("@ClientSSN") = ClientSSN
    objCmd.Parameters("@ReturnBitValue") = 0

    '[Execute the function]
    'If not returning a recordset, use the adExecuteNoRecords parameter option
    'objCmd.Execute, , adExecuteNoRecords

    objCmd.Execute

    ReturnBitValue = objCmd.Parameters("@ReturnBitValue")



    Still trying to figure out why this is happening. Otherwise I could finish this up.

    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •