Scribd Logo
Upload

Welcome to Scribd!

  • 14 views

    Chapter10 SQL

    Uploaded by

    Asim Azizov
    Stored procedures allow developers to accept input parameters, perform actions like insert, update and delete, return status or output parameters. They can improve performance by reducing network traffic and execution time. The key differences between stored procedures and user-defined functions are that stored procedures can perform insert, update and delete operations and return multiple output parameters, while functions must return a value and cannot perform DML operations. Stored procedures are useful for CRUD operations as they decouple SQL code from applications and improve security and performance. Error handling with try/catch blocks allows procedures to catch errors and continue normal execution.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Chapter10 SQL

    Uploaded by

    Asim Azizov
    14 views30 pages
    Stored procedures allow developers to accept input parameters, perform actions like insert, update and delete, return status or output parameters. They can improve performance by reducing network traffic and execution time. The key differences between stored procedures and user-defined functions are that stored procedures can perform insert, update and delete operations and return multiple output parameters, while functions must return a value and cannot perform DML operations. Stored procedures are useful for CRUD operations as they decouple SQL code from applications and improve security and performance. Error handling with try/catch blocks allows procedures to catch errors and continue normal execution.

    Original Title

    chapter10_SQL

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Stored procedures allow developers to accept input parameters, perform actions like insert, update and delete, return status or output parameters. They can improve performance by reducing network traffic and execution time. The key differences between stored procedures and user-defined functions are that stored procedures can perform insert, update and delete operations and return multiple output parameters, while functions must return a value and cannot perform DML operations. Stored procedures are useful for CRUD operations as they decouple SQL code from applications and improve security and performance. Error handling with try/catch blocks allows procedures to catch errors and continue normal execution.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    14 views30 pages

    Chapter10 SQL

    Uploaded by

    Asim Azizov
    Stored procedures allow developers to accept input parameters, perform actions like insert, update and delete, return status or output parameters. They can improve performance by reducing network traffic and execution time. The key differences between stored procedures and user-defined functions are that stored procedures can perform insert, update and delete operations and return multiple output parameters, while functions must return a value and cannot perform DML operations. Stored procedures are useful for CRUD operations as they decouple SQL code from applications and improve security and performance. Error handling with try/catch blocks allows procedures to catch errors and continue normal execution.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 30

    Stored procedures

    W R I T I N G F U N C T I O N S A N D S TO R E D P R O C E D U R E S I N S Q L S E R V E R

    Etibar Vazirov
    Slides credit: Meghan Kwartler
    What is a stored procedure?
    What?

    Routines that

    Accept input parameters


    Perform actions ( EXECUTE SELECT , INSERT , UPDATE , DELETE , and other SP statements)

    Return status (success or failure)

    Return output parameters

    WRITING FUNCTIONS AND STORED PROCEDURES IN SQL SERVER


    Why use stored procedures?
    Why?

    Can reduce execution time

    Can reduce network tra c

    Allow for Modular Programming

    Improved Security

    WRITING FUNCTIONS AND STORED PROCEDURES IN SQL SERVER


    What's the difference?
    UDFs SPs

    Must return value Return value optional


    Table-valued allowed No table-valued

    Embedded SELECT execute allowed Cannot embed in SELECT to execute

    No output parameters Return output parameters & status

    No INSERT , UPDATE , DELETE INSERT , UPDATE , DELETE allowed


    Cannot execute SPs Can execute functions & SPs

    No Error Handling Error Handling with TRY...CATCH

    WRITING FUNCTIONS AND STORED PROCEDURES IN SQL SERVER


    CREATE PROCEDURE with OUTPUT parameter
    -- First four lines of code
    -- SP name must be unique
    CREATE PROCEDURE dbo.cuspGetRideHrsOneDay
    @DateParm date,
    @RideHrsOut numeric OUTPUT
    AS
    .......

    WRITING FUNCTIONS AND STORED PROCEDURES IN SQL SERVER


    CREATE PROCEDURE with OUTPUT parameter
    CREATE PROCEDURE dbo.cuspGetRideHrsOneDay
    @DateParm date,
    @RideHrsOut numeric OUTPUT
    AS
    SET NOCOUNT ON
    BEGIN
    SELECT
    @RideHrsOut = SUM(
    DATEDIFF(second, PickupDate, DropoffDate)
    )/ 3600
    FROM YellowTripData
    WHERE CONVERT(date, PickupDate) = @DateParm
    RETURN
    END;

    WRITING FUNCTIONS AND STORED PROCEDURES IN SQL SERVER


    Output parameters vs. return values
    Output parameters Return value

    Can be any data type Used to indicate success or failure

    Can declare multiple per SP Integer data type only


    Cannot be table-valued parameters 0 indicates success and non zero indicates
    failure

    WRITING FUNCTIONS AND STORED PROCEDURES IN SQL SERVER


    You're ready to
    CREATE
    PROCEDUREs!
    W R I T I N G F U N C T I O N S A N D S TO R E D P R O C E D U R E S I N S Q L S E R V E R
    SP for CRUD!
    W R I T I N G F U N C T I O N S A N D S TO R E D P R O C E D U R E S I N S Q L S E R V E R

    Etibar
    Meghan Vazirov
    Kwartler
    Slides credit:
    IT C ons ultantMeghan Kwartler
    Why stored procedures for CRUD?
    Decouples SQL code from other application
    layers

    WRITING FUNCTIONS AND STORED PROCEDURES IN SQL SERVER


    Why stored procedures for CRUD?
    Decouples SQL code from other application
    layers

    Improved security

    WRITING FUNCTIONS AND STORED PROCEDURES IN SQL SERVER


    Why stored procedures for CRUD?
    Decouples SQL code from other application
    layers

    Improved security

    Performance

    WRITING FUNCTIONS AND STORED PROCEDURES IN SQL SERVER


    C for CREATE
    CREATE PROCEDURE dbo.cusp_TripSummaryCreate (
    @TripDate as date,
    @TripHours as numeric(18, 0)
    ) AS BEGIN INSERT INTO dbo.TripSummary(Date, TripHours)
    VALUES
    (@TripDate, @TripHours)
    SELECT Date, TripHours
    FROM dbo.TripSummary
    WHERE Date = @TripDate END

    WRITING FUNCTIONS AND STORED PROCEDURES IN SQL SERVER


    R for READ
    CREATE PROCEDURE cusp_TripSummaryRead
    (@TripDate as date)
    AS
    BEGIN
    SELECT Date, TripHours
    FROM TripSummary
    WHERE Date = @TripDate
    END;

    WRITING FUNCTIONS AND STORED PROCEDURES IN SQL SERVER


    U for UPDATE
    CREATE PROCEDURE dbo.cusp_TripSummaryUpdate
    (@TripDate as date,
    @TripHours as numeric(18,0))
    AS
    BEGIN
    UPDATE dbo.TripSummary
    SET Date = @TripDate,
    TripHours = @TripHours
    WHERE Date = @TripDate
    END;

    WRITING FUNCTIONS AND STORED PROCEDURES IN SQL SERVER


    D for DELETE
    CREATE PROCEDURE cusp_TripSummaryDelete
    (@TripDate as date,
    @RowCountOut int OUTPUT)
    AS
    BEGIN
    DELETE
    FROM TripSummary
    WHERE Date = @TripDate

    SET @RowCountOut = @@ROWCOUNT


    END;

    WRITING FUNCTIONS AND STORED PROCEDURES IN SQL SERVER


    Your turn for CRUD!
    W R I T I N G F U N C T I O N S A N D S TO R E D P R O C E D U R E S I N S Q L S E R V E R
    Let's EXEC!
    W R I T I N G F U N C T I O N S A N D S TO R E D P R O C E D U R E S I N S Q L S E R V E R

    Etibar
    Meghan Vazirov
    Kwartler
    Slides credit:
    IT C ons ultantMeghan Kwartler
    Ways to EXECute
    No output parameter or return value

    Store return value

    With output parameter


    With output parameter & store return value

    Store result set

    WRITING FUNCTIONS AND STORED PROCEDURES IN SQL SERVER


    No output parameter or return value With output parameter

    EXEC dbo.cusp_TripSummaryUpdate DECLARE @RideHrs as numeric(18,0)


    @TripDate = '1/5/2017'
    @TripHours = '300' EXEC dbo.cuspSumRideHrsOneDay
    @DateParm = '1/5/2017',
    @RideHrsOut = @RideHrs OUTPUT

    SELECT @RideHrs as TotalRideHrs

    + +
    | TotalRideHrs |
    | +
    | 77733 |
    + +

    WRITING FUNCTIONS AND STORED PROCEDURES IN SQL SERVER


    With return value With return value & output parameter

    Declare @ReturnValue as int Declare @ReturnValue as int


    Declare @RowCount as int
    EXEC @ReturnValue =
    dbo.cusp_TripSummaryUpdat EXEC @ReturnValue =
    e @TripDate = '1/5/2017', dbo.cusp_TripSummaryDelete
    @TripHours = 300 @TripDate = '1/5/2017',
    @RowCountOut = @RowCount OUTPUT
    Select @ReturnValue as ReturnValue
    Select @ReturnValue as ReturnValue,
    @RowCount as RowCount
    +-------------+
    | ReturnValue |
    |-------------+ +-------------+----------
    +
    | 0 |
    | ReturnValue | RowCount |
    +-------------+
    |-------------+----------
    |
    | 0 | 1 |
    + + |

    WRITING FUNCTIONS AND STORED PROCEDURES IN SQL SERVER


    EXEC & store result set
    DECLARE @TripSummaryResultSet as TABLE(
    TripDate date,
    TripHours numeric(18,0))
    INSERT INTO @TripSummaryResultSet
    EXEC cusp_TripSummaryRead @TripDate = '1/5/2017'
    SELECT * FROM @TripSummaryResultSet

    + + +
    | TripDate | TripHours |
    | + |
    | 2017-01-05 | 200 |
    + + |

    WRITING FUNCTIONS AND STORED PROCEDURES IN SQL SERVER


    Time to EXEC your
    SPs!
    W R I T I N G F U N C T I O N S A N D S TO R E D P R O C E D U R E S I N S Q L S E R V E R
    TRY & CATCH those
    errors!
    W R I T I N G F U N C T I O N S A N D S TO R E D P R O C E D U R E S I N S Q L S E R V E R

    Etibar
    Meghan Vazirov
    Kwartler
    Slides credit:
    IT C ons ultantMeghan Kwartler
    To handle errors or not
    What is error handling? What happens without error handling?

    Anticipation, detection and resolution of Sudden shut down or halts execution


    errors
    Generic error messages without helpful
    Maintains normal ow of execution context are provided
    Integrated into initial design

    WRITING FUNCTIONS AND STORED PROCEDURES IN SQL SERVER


    Let's TRY
    ALTER PROCEDURE dbo.cusp_TripSummaryCreate
    @TripDate nvarchar(30),
    @RideHrs numeric,
    @ErrorMsg nvarchar(max) = null OUTPUT
    AS
    BEGIN
    BEGIN TRY
    INSERT INTO TripSummary (Date, TripHours)
    VALUES (@TripDate, @RideHrs)
    END TRY
    ........

    WRITING FUNCTIONS AND STORED PROCEDURES IN SQL SERVER


    Time to CATCH
    ALTER PROCEDURE dbo.cusp_TripSummaryCreate
    @TripDate nvarchar(30),
    @RideHrs numeric,
    @ErrorMsg nvarchar(max) = null OUTPUT
    AS
    BEGIN
    BEGIN TRY
    INSERT INTO TripSummary (Date, TripHours)
    VALUES (@TripDate, @RideHrs)
    END TRY
    BEGIN CATCH
    SET @ErrorMsg = 'Error_Num: ' +
    CAST (ERROR_NUMBER() AS varchar) +
    ' Error_Sev: ' +
    CAST(ERROR_SEVERITY() AS varchar) +
    ' Error_Msg: ' + ERROR_MESSAGE()
    END CATCH
    END

    WRITING FUNCTIONS AND STORED PROCEDURES IN SQL SERVER


    Show me the ERROR...
    DECLARE @ErrorMsgOut as nvarchar(max)

    EXECUTE dbo.cusp_TripSummaryCreate
    @TripDate = '1/32/2018',
    @RideHrs = 100,
    @ErrorMsg = @ErrorMsgOUT OUTPUT

    SELECT @ErrorMsgOut as ErrorMessag

    ErrorMessage

    Error_Num: 241 Error_Sev: 16


    Error_Msg: Conversion failed when converting date and/or time
    from character string

    WRITING FUNCTIONS AND STORED PROCEDURES IN SQL SERVER


    THROW vs RAISERROR
    THROW RAISERROR

    Introduced in SQL Server 2012 Introduced in SQL Server 7.0

    Simple & easy to use Generates new error and cannot access
    details of original error (e.g. line number
    Statements following will NOT be executed
    where error originally occurred)
    Statements following can be executed

    WRITING FUNCTIONS AND STORED PROCEDURES IN SQL SERVER


    Your turn to CATCH!
    W R I T I N G F U N C T I O N S A N D S TO R E D P R O C E D U R E S I N S Q L S E R V E R

    You might also like