User Defined function returning table give inconsistent results

Discussion in 'ASP General' started by Marc Walgren, Aug 20, 2004.

  1. Marc Walgren

    Marc Walgren Guest

    Greetings

    I have an ASP application to enter reservations. There are multiple user
    security settings that require some users to have a restricted list of
    client in a drop list on a form.

    I constructed the following function:


    CREATE FUNCTION [estudf_UserClientsList] (@pUserId int)
    RETURNS @UserClientQ table
    (
    -- user information
    UserId int,
    FirstName varchar(50),
    LastName varchar(50),
    UserName varchar(25),
    -- client information
    ClientId int,
    ClientName varchar(40),
    UsageTypeId int,
    -- event update rights
    noSaveReq int, -- =1 disallow save of Request status events
    for the client
    noSaveConflict int, -- =1 disallow save of Conflict status
    events for the client
    noSaveApprov int, -- =1 disallow save of Approved status
    events for the client
    noSaveDenied int, -- =1 disallow save of Denied status events
    for the client
    -- debug trace
    Stage int
    )
    AS
    BEGIN
    ----------------------------------------------------------------------------
    ------------------------------
    -- Marc Walgren - 07/14/2004
    -- This routine will determine proper client list for user based on user
    -- settings and user/contact/client relationships
    ----------------------------------------------------------------------------
    ------------------------------
    -- local variables
    declare @UserAdmin int
    declare @UserFirstName varchar(50)
    declare @UserLastName varchar(50)
    declare @UserName varchar(25)
    declare @UserActive int
    declare @UserReadOnly int
    declare @FacilityAdminCount int
    declare @UserClientCount int

    -- Users with Admin rights get all clients
    select @UserAdmin = Admin,
    @UserFirstName = FirstName,
    @UserLastName = LastName,
    @UserName = UserName,
    @UserActive = Active,
    @UserReadOnly = ReadOnly
    from Users where UserId = @pUserId

    -- clear return q to be sure
    delete @UserClientQ

    if @UserAdmin = 1
    begin

    -- give all clients
    insert @UserClientQ ( UserId, FirstName, LastName, UserName,
    ClientId, ClientName, UsageTypeId, Stage, noSaveReq, noSaveConflict,
    noSaveApprov, noSaveDenied )
    Select @pUserId, @UserFirstName, @UserLastName, @UserName,
    c.ClientId, c.ClientName, c.UsageTypeId, 1, 0, 0, 0, 0
    from Client c

    end
    else
    begin
    -- check for Building Administrator
    select @FacilityAdminCount = count(UFLID) from UserFacilityLink
    where UserId = @pUserId

    if @FacilityAdminCount > 0
    begin
    -- give all clients
    insert @UserClientQ ( UserId, FirstName, LastName, UserName,
    ClientId, ClientName, UsageTypeId, Stage, noSaveReq, noSaveConflict,
    noSaveApprov, noSaveDenied )
    Select @pUserId, @UserFirstName, @UserLastName,
    @UserName, c.ClientId, c.ClientName, c.UsageTypeId, 2, 0, 0, 0, 0
    from Client c
    end
    else
    begin
    -- count the user's client
    SELECT @UserClientCount = count(dbo.Users.UserId)
    FROM dbo.Users
    INNER JOIN dbo.Contacts ON dbo.Contacts.UserId =
    dbo.Users.UserId
    INNER JOIN dbo.ClientContactLink ON dbo.Contacts.ContactID =
    dbo.ClientContactLink.ContactID
    INNER JOIN dbo.Client ON dbo.Client.ClientID =
    dbo.ClientContactLink.ClientID
    WHERE dbo.Users.UserId = @pUserId

    -- users with associated client get just their clients
    if @UserClientCount > 0
    begin
    -- give user's clients
    insert @UserClientQ ( UserId, FirstName, LastName,
    UserName, ClientId, ClientName, UsageTypeId, Stage, noSaveReq,
    noSaveConflict, noSaveApprov, noSaveDenied )
    SELECT dbo.Users.UserId,
    dbo.Users.FirstName, dbo.Users.LastName, dbo.Users.UserName,
    dbo.Client.ClientId,
    dbo.Client.ClientName, dbo.Client.UsageTypeId , 3, 0, 0, 1,1
    FROM dbo.Users
    INNER JOIN dbo.Contacts ON
    dbo.Contacts.UserId = dbo.Users.UserId
    INNER JOIN dbo.ClientContactLink ON
    dbo.Contacts.ContactID = dbo.ClientContactLink.ContactID
    INNER JOIN dbo.Client ON dbo.Client.ClientID
    = dbo.ClientContactLink.ClientID
    WHERE dbo.Users.UserId = @pUserId
    end
    else
    begin
    -- Active and non-readonly users get full client
    list
    if @UserActive = 1 and @UserReadOnly = 0
    begin
    -- give all clients
    insert @UserClientQ ( UserId, FirstName,
    LastName, UserName, ClientId, ClientName, UsageTypeId, Stage, noSaveReq,
    noSaveConflict, noSaveApprov, noSaveDenied )
    Select @pUserId, @UserFirstName,
    @UserLastName, @UserName, c.ClientId, c.ClientName, c.UsageTypeId, 4, 0, 0,
    1, 1
    from Client c
    end
    end
    end
    end

    return

    END

    My administrative user is ID=1. I perform the following code to build the
    drop list


    strSQL = "SELECT ClientID, ClientName, UsageTypeId, Stage FROM
    estudf_UserClientsList(" strSQL = strSQL & cstr(iUserId)
    strSQL = strSQL & ") ORDER BY ClientName"

    response.write "<select size=1 name=txtClient>"
    response.write "<option value='' >--Choose--</option>"

    pbIsClientListExists = false
    set rs=Server.CreateObject("ADODB.RecordSet")
    rs.open strSQL,CONNECTIONSTRING

    while rs.EOF=false
    pbIsClientListExists = true
    stage = rs.fields("Stage")
    If Trim(rs.fields("ClientID"))=Trim(checkvalue)Then
    selected=" selected"
    pClientsUsageTypeID = rs.fields("UsageTypeId")
    Else
    selected=""
    End If

    response.write "<option value='"
    response.write Trim(rs.fields("ClientID"))
    response.write "' " & selected & ">"
    response.write rs.fields("ClientName")
    response.write "</option>"

    wend


    I have used Profiler to be sure the parameter to the function is proper and
    have never found it to be incorrect.

    When the client list is not build properly, only one client name appears and
    it is clientid=1. If I change the select sql in the function to exclude
    clientid=1 then the name of the next lowest client id is displayed when the
    list is improperly built.

    Any ideas to resolve the inconsistency would be gratefully received.

    Marc
     
    Marc Walgren, Aug 20, 2004
    #1
    1. Advertising

  2. There's too much for me to digest, but maybe one of the people over at
    m.p.sqlserver.programming will take a crack at it ...

    They'll want you to post table-creation DDL and insert statements with
    sample data before they'll look at it.

    Bob Barrows
    Marc Walgren wrote:
    > Greetings
    >
    > I have an ASP application to enter reservations. There are multiple
    > user security settings that require some users to have a restricted
    > list of client in a drop list on a form.
    >
    > I constructed the following function:
    >

    <snip>

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Aug 20, 2004
    #2
    1. Advertising

  3. "Marc Walgren" <marcmittenATyahoo.com> wrote in message
    news:...
    [snip]
    > My administrative user is ID=1. I perform the following code to build the
    > drop list
    >
    >
    > strSQL = "SELECT ClientID, ClientName, UsageTypeId, Stage FROM
    > estudf_UserClientsList(" strSQL = strSQL & cstr(iUserId)
    > strSQL = strSQL & ") ORDER BY ClientName"
    >
    > response.write "<select size=1 name=txtClient>"
    > response.write "<option value='' >--Choose--</option>"
    >
    > pbIsClientListExists = false
    > set rs=Server.CreateObject("ADODB.RecordSet")
    > rs.open strSQL,CONNECTIONSTRING
    >
    > while rs.EOF=false
    > pbIsClientListExists = true
    > stage = rs.fields("Stage")
    > If Trim(rs.fields("ClientID"))=Trim(checkvalue)Then
    > selected=" selected"
    > pClientsUsageTypeID = rs.fields("UsageTypeId")
    > Else
    > selected=""
    > End If
    >
    > response.write "<option value='"
    > response.write Trim(rs.fields("ClientID"))
    > response.write "' " & selected & ">"
    > response.write rs.fields("ClientName")
    > response.write "</option>"
    >
    > wend
    >
    >
    > I have used Profiler to be sure the parameter to the function is proper

    and
    > have never found it to be incorrect.
    >
    > When the client list is not build properly, only one client name appears

    and
    > it is clientid=1. If I change the select sql in the function to exclude
    > clientid=1 then the name of the next lowest client id is displayed when

    the
    > list is improperly built.
    >
    > Any ideas to resolve the inconsistency would be gratefully received.


    I don't see an "rs.MoveNext" call in your code. Also, you should really
    consider rewriting that UDF. It could be done in a stored procedure with one
    SELECT statement and a well placed CASE expression. Finally, you may also
    want to consider using GetRows/GetString instead of recordset iteration.
    Here's an article:

    http://aspfaq.com/show.asp?id=2467
     
    Chris Hohmann, Aug 21, 2004
    #3
  4. Marc Walgren

    Marc Walgren Guest

    Chris

    Thanks for you thoughts.

    I omitted the movnext from my code snip for the post. I switch from the
    getrow() approach as a wild attempt to resolve the instability.

    The article you reference is good and I had already read it.

    Best regards,

    Marc

    "Chris Hohmann" <> wrote in message
    news:%...
    > "Marc Walgren" <marcmittenATyahoo.com> wrote in message
    > news:...
    > [snip]
    > > My administrative user is ID=1. I perform the following code to build

    the
    > > drop list
    > >
    > >
    > > strSQL = "SELECT ClientID, ClientName, UsageTypeId, Stage FROM
    > > estudf_UserClientsList(" strSQL = strSQL & cstr(iUserId)
    > > strSQL = strSQL & ") ORDER BY ClientName"
    > >
    > > response.write "<select size=1 name=txtClient>"
    > > response.write "<option value='' >--Choose--</option>"
    > >
    > > pbIsClientListExists = false
    > > set rs=Server.CreateObject("ADODB.RecordSet")
    > > rs.open strSQL,CONNECTIONSTRING
    > >
    > > while rs.EOF=false
    > > pbIsClientListExists = true
    > > stage = rs.fields("Stage")
    > > If Trim(rs.fields("ClientID"))=Trim(checkvalue)Then
    > > selected=" selected"
    > > pClientsUsageTypeID = rs.fields("UsageTypeId")
    > > Else
    > > selected=""
    > > End If
    > >
    > > response.write "<option value='"
    > > response.write Trim(rs.fields("ClientID"))
    > > response.write "' " & selected & ">"
    > > response.write rs.fields("ClientName")
    > > response.write "</option>"
    > >
    > > wend
    > >
    > >
    > > I have used Profiler to be sure the parameter to the function is proper

    > and
    > > have never found it to be incorrect.
    > >
    > > When the client list is not build properly, only one client name appears

    > and
    > > it is clientid=1. If I change the select sql in the function to exclude
    > > clientid=1 then the name of the next lowest client id is displayed when

    > the
    > > list is improperly built.
    > >
    > > Any ideas to resolve the inconsistency would be gratefully received.

    >
    > I don't see an "rs.MoveNext" call in your code. Also, you should really
    > consider rewriting that UDF. It could be done in a stored procedure with

    one
    > SELECT statement and a well placed CASE expression. Finally, you may also
    > want to consider using GetRows/GetString instead of recordset iteration.
    > Here's an article:
    >
    > http://aspfaq.com/show.asp?id=2467
    >
    >
     
    Marc Walgren, Aug 23, 2004
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Oodini
    Replies:
    1
    Views:
    1,779
    Keith Thompson
    Sep 27, 2005
  2. Replies:
    10
    Views:
    475
    James Kanze
    May 31, 2007
  3. Yas
    Replies:
    0
    Views:
    483
  4. MikeR
    Replies:
    5
    Views:
    148
    MikeR
    Jan 7, 2009
  5. Alok
    Replies:
    3
    Views:
    254
Loading...

Share This Page