Post reply

User defined function

  • February 18, 2002 at 12:38 pm

    #77788

    I am converting an Access database into SQL, and am totally stuck on some queries that required the user to input criteria in the Access version. I know I need to write a user defined function for this, but not sure how. Below is one of the simple Access queries that I need to convert to SQL. Can anyone help me with writing my first user defined function to make this work?

    SELECT MProjects.ProjNo, MProjects.ProjTitle, MProjects.ProjObjective, MProjects.CustID, MProjects.UserID

    FROM MProjects

    WHERE (((MProjects.UserID)=[Enter User ID:]));

  • February 18, 2002 at 3:42 pm

    #424088

    How will they access the data first. If thru access then right view to database (but I hope you are not using access as it will drag large DBs down this way). If is going to be in VBA within Access or another programming language then use Stored Procedures (they are much better on you and the server).

    Ex:

    CREATE PROCEDURE ip_GetProj

    @user-id VARHCAR(20) --I am assuming alphanumeric input possible in your queries.

    AS

    SELECT MProjects.ProjNo, MProjects.ProjTitle, MProjects.ProjObjective, MProjects.CustID, MProjects.UserID

    FROM MProjects

    WHERE MProjects.UserID = @user-id

    Then to run you pass the query as

    ip_GetProj @user-id = 'AABB2'

    You do not have to use @user-id but if you every program with optional items you will want to get used to this IMHO.

    Hope this is helpfull.

  • February 19, 2002 at 8:03 am

    #424116

    Thanks for the assistance. I'll give it a try and let you know if it works for me. The frontend is Access project (.adp)which will eventually be replicated on remote laptops.

Viewing 3 posts - 1 through 2 (of 2 total)

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