SQL Server Performance

syntax error in function SP

Discussion in 'ALL SQL SERVER QUESTIONS' started by Samane, Jan 3, 2013.

  1. Samane New Member

    Hi guys

    I have three errors in below code:
    Code:
    CREATE FUNCTION delUserGrp
    (@name nvarchar(15))
    RETURNS INT
    AS
    BEGIN
    
    SELECT * FROM [dbo].[user]
    WHERE [user-grp-fk] IN (SELECT PK FROM [dbo].[user-grp] WHERE @name =name)
    
    IF @@ROWCOUNT=0
        BEGIN
            DELETE FROM [dbo].[user-grp] WHERE @name=name
            RETURN (1)
        END
    ELSE
        BEGIN
            RETURN (0)
        END
    END
    error: msg444, 443,455
    please help me to solve the syntax errors
  2. Luis Martin Moderator

  3. Sandeep Mittal New Member

    Code:
    CREATE FUNCTION delUserGrp (@name nvarchar(15))
    RETURNS INT
    AS
    BEGIN
        DECLARE @RETVAL INT
    
        IF NOT EXISTS (SELECT    *
            FROM    [dbo].[user]
            WHERE    [user-grp-fk] IN (
                SELECT PK FROM [dbo].[user-grp] WHERE name = @name
            )
        )
        BEGIN
            DELETE FROM [dbo].[user-grp] WHERE @name=name
            SET @RETVAL = 1
        END
        ELSE
        BEGIN
            SET @RETVAL = 0
        END
        RETURN @RETVAL
    END
  4. Samane New Member

    Excellent!! Thank you Sandeep
    but unfortunately I still have msg443 error on Delete line.
    what should i do?!

    Thank you
  5. Shehap MVP, MCTS, MCITP SQL Server

    Delete Statements can't be included within scalar function coz If I am generally speaking about UDF , they can’t perform any kind of DML operations (Insert/update/Delete)

    so you have to replace UDF by SP as below :

    CREATE procedure delUserGrp_SP @name nvarchar(15) , @RETVAL INT OUTPUT

    AS
    BEGIN

    IF NOT EXISTS(SELECT *
    FROM [dbo].[user]
    WHERE [user-grp-fk] IN(
    SELECT PK FROM [dbo].[user-grp] WHERE name = @name
    )
    )
    BEGIN
    DELETE FROM [dbo].[user-grp] WHERE @name=name
    SET @RETVAL = 1
    END
    ELSE
    BEGIN
    SET @RETVAL = 0
    END
    RETURN
    END

    END

    declare @retval_result int

    exec delUserGrp_sp@name ='Name_value',@RETVAL=@retval_result output

    select @retval_result

    Please let me know if any further help is needed
    Samane likes this.
  6. Samane New Member

    your code executed correctly, but I thought procedure cannot return output value!!
    I have some question about your codes:
    1- it's not so much important to use "return" at the end of procedure?!
    2- when we can use function?

    thank you
  7. Shehap MVP, MCTS, MCITP SQL Server

    For the 1st question , it is right you can dispense Return command used at the end of SP for this case and for the 2nd question , scalar function can be used widely if we need to return a single value differentiated from a certain select Query having specific where conditions, inner joins ..etc

    Let me know if any further help is needed
    Samane likes this.
  8. Sandeep Mittal New Member

    OMG!!! I am sorry for the silly answer. While posting answer, I never realized, delete statement is not allowed in scalar functions. As suggested by Shehap, you need to create stored procedure
    Code:
    CREATE procedure delUserGrp_SP @name nvarchar(15)
    AS
    BEGIN
        IF NOT EXISTS(
            SELECT    1 FROM [dbo].[user]
            WHERE    [user-grp-fk] IN (
                SELECT PK FROM [dbo].[user-grp] WHERE name = @name
            )
        )
        BEGIN
            DELETE FROM [dbo].[user-grp] WHERE name = @name
            SELECT 1
        END
        ELSE
        BEGIN
            SELECT 0
        END
    END
    Samane likes this.
  9. Samane New Member

    Great! both of the codes are executed correctely. but whitch one is better?! select or return?
  10. Shehap MVP, MCTS, MCITP SQL Server

    Both are workable and almost equivalent except the way of using output function is more closely to UDFs where you can re-use easily the output of SPs within any other variables but the way of using select commands can’t permit you to use the output of SPs easily unless you define a temp table and insert the output of SPs into this temp table like below:

    Create table # temp (retval int)
    Insert into # temp exec delUserGrp_SP@name

    Let me know if any further help is needed
    Samane likes this.

Share This Page