You must Sign In to post a response.
  • Category: SQL Server

    Dml operations in udf in sql server

    hi,
    can we perform insert,update,delete operations in stored procedurs in sql server
  • #747321
    Hi,

    we can perform insert,update,delete operations in stored procedures in sql server



    CREATE PROCEDURE USP_DMLOperation
    (
    @UserId INTEGER,
    @UName VARCHAR(10),
    @Age INTEGER,
    @StType nvarchar(20) = ''
    )

    AS
    BEGIN
    IF @StType = 'Insert'
    BEGIN
    INSERT INTO employee (UserId,Uname,Age) values( @UserId, @Uname, @Age)
    END

    IF @StType = 'Update'
    BEGIN
    UPDATE employee SET
    Uname = @Uname,
    Age = @Age
    WHERE UserId = @UserId
    END

    else IF @StType = 'Delete'
    BEGIN
    DELETE FROM employee WHERE UserId = @UserId
    END
    end




    EXEC USP_DMLOperation @UserId = 101 , @UName = 'Suresh0583', @Age = 30, @StType='Insert'

    EXEC USP_DMLOperation @UserId = 101, @StType='Delete'

    EXEC USP_DMLOperation @UserId = 101 , @UName = 'Suresh', @Age = 30, @StType='Update'


    Thanks and Regards
    S.Suresh

    "IF YOU WANT TO SHINE LIKE A SUN FIRST YOU HAVE BURN LIKE IT".

  • #747334
    Refer the following stored procedures for DML Opeations(insert, update and delete)


    CREATE PROCEDURE INSERT_TEST @TestName NVARCHAR(50)
    AS
    BEGIN
    SET nocount ON;

    INSERT INTO dbo.testtable
    (testname)
    VALUES (@TestName)

    SELECT CAST(SCOPE_IDENTITY() AS INT) AS NewTestID
    WHERE @@ROWCOUNT > 0
    END

    go

    CREATE PROCEDURE UPDATE_TEST @TestID INT,
    @TestName NVARCHAR(50)
    AS
    BEGIN
    SET nocount ON;

    UPDATE dbo.testtable
    SET testname = @TestName
    WHERE testid = @TestID
    END

    go

    CREATE PROCEDURE DELETE_TEST @TestID INT
    AS
    BEGIN
    SET nocount ON;

    DELETE FROM dbo.testtable
    WHERE testid = @TestID
    END

    go

  • #748620
    DML operations (Insert, delete and Update) are allowed within a stored procedure.

    However DML operations are not allowed within a udf (user defined function).


  • Sign In to post your comments