syntax error in function SP | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

syntax error in function SP

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
Check: "Multi statement functions require you to declare the return table structure in the "header" and complete the function with a single RETURN command"
http://www.sqlteam.com/article/user-defined-functions
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

Excellent!! Thank you Sandeep
but unfortunately I still have msg443 error on Delete line.
what should i do?! Thank you
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

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
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
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
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

Excellent!! Thank you Sandeep
but unfortunately I still have msg443 error on Delete line.
what should i do?! Thank you
Great! both of the codes are executed correctely. but whitch one is better?! select or return?
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
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |