Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    84

    Unanswered: Procedure correction..Pls Help

    Hi, I have this following procedure and I am sure on how to convert a part of the procedure. Can anyone pls help....
    The function is created successfully. I have problem in calling that function n the procedure..Some syntax error...
    I think, the problem is here...
    UPDATE signoff SET user_name_assigned = getUserGroupName_upg(signoff_assigned_rec.user_ass igned) WHERE id = signoff_assigned_rec.id
    -----------------

    CREATE FUNCTION getUserGroupName_upg
    (
    @userId INT
    )
    RETURNS varchar
    AS
    BEGIN

    DECLARE @temp varchar(520)
    DECLARE @tmp_first_name varchar(520)
    DECLARE @tmp_last_name varchar(520)
    DECLARE @cnt INT
    SELECT @cnt = COUNT(*) FROM agileuser WHERE id = @userId
    IF ( @cnt = 1 )
    BEGIN
    SELECT
    @tmp_first_name = first_name,
    @tmp_last_name = last_name
    FROM agileuser WHERE id = @userId
    SELECT @tmp_first_name = RTRIM(@tmp_first_name)
    SELECT @tmp_last_name = RTRIM(@tmp_last_name)
    SELECT @temp = @tmp_first_name
    IF ( @temp is not null )
    BEGIN
    SELECT @temp = @temp
    END
    SELECT @temp = @temp + @tmp_last_name
    END
    IF ( @cnt = 0 )
    BEGIN
    SELECT @cnt = COUNT(*) FROM user_group WHERE id = @userId
    IF ( @cnt = 1 )
    BEGIN
    SELECT @temp = name FROM user_group WHERE id = @userId
    END
    END
    return @temp
    END
    GO

    -- Processing user assigned field of signoff table
    DECLARE @signoff_assigned_rec VARCHAR(200)
    DECLARE signoff_assigned_csr CURSOR FOR
    SELECT id, user_assigned FROM signoff WHERE user_assigned is not null
    OPEN signoff_assigned_csr
    WHILE (0 = 0)
    BEGIN --(
    fetch NEXT FROM signoff_assigned_csr INTO @signoff_assigned_rec
    IF (@@FETCH_STATUS = -1)
    BREAK
    UPDATE signoff SET user_name_assigned = getUserGroupName_upg(signoff_assigned_rec.user_ass igned) WHERE id = signoff_assigned_rec.id
    END --)
    close signoff_assigned_csr
    DEALLOCATE signoff_assigned_csr
    GO
    Kishore

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    YOu need dbo.

    in front of the udf...

    kinda of a pain...

    also you need a lot more work of the function

    Like

    Code:
    CREATE FUNCTION getUserGroupName_upg
    (
    @userId INT 
    )
    RETURNS varchar 
    AS 
    BEGIN
    
    DECLARE @temp varchar(520) 
    IF ( @cnt = 1 ) 
    BEGIN 
    SELECT  @tmp = ISNULL(RTRIM(first_name),'')+ISNULL(RTRIM(last_name),'')
      FROM agileuser WHERE id = @userId
    IF @@ROWCOUNT <> 0 SELECT @temp = 'No User Or Mulitple Users'
    return @temp
    END
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2004
    Posts
    84
    Hi Bret, I used below statement and it worked..Thanks..
    ---------
    UPDATE signoff SET user_name_assigned = dbo.getUserGroupName_upg(signoff_assigned_rec.user _assigned)
    FROM signoff_assigned_rec
    WHERE id = signoff_assigned_rec.id
    Kishore

Posting Permissions

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