Stored Procedures in Azure Data Studio

A Procedure or stored procedure is a group of SQL statements in Azure Data Studio that has been created and stored in the database. It accept input and output Parameters. All relational database system supports stored procedure, Stored Procedures are faster than SQL Queries. stored procedures a query plan is generated and cached. We can enable plan caching for our queries by using Parametized queries.

Syntax: -
-- Create a new stored procedure called 'StoredProcedureName' in schema 'dbo'
-- Drop the stored procedure if it already exists
IF EXISTS (
SELECT *
    FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
    AND SPECIFIC_NAME = N'StoredProcedureName'
    AND ROUTINE_TYPE = N'PROCEDURE'
)
DROP PROCEDURE dbo.StoredProcedureName
GO
-- Create the stored procedure in the specified schema
CREATE PROCEDURE dbo.StoredProcedureName
    @param1 /*parameter name*/ int /*datatype_for_param1*/ = 0, /*default_value_for_param1*/
    @param2 /*parameter name*/ int /*datatype_for_param1*/ = 0 /*default_value_for_param2*/
-- add more stored procedure parameters here
AS
    -- body of the stored procedure
    SELECT @param1, @param2
GO
-- example to execute the stored procedure we just created
EXECUTE dbo.StoredProcedureName 1 /*value_for_param1*/, 2 /*value_for_param2*/
GO

Example : –

-- sp_Emp is the Stored procedure name.
IF EXISTS (
SELECT *
    FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
    AND SPECIFIC_NAME = N'sp_Emp'
    AND ROUTINE_TYPE = N'PROCEDURE'
)
DROP PROCEDURE dbo.sp_Emp
GO
CREATE PROCEDURE dbo.sp_Emp
    @param1 /*parameter name*/ int /*datatype_for_param1*/ = 0, /*default_value_for_param1*/
    @param2 /*parameter name*/ int /*datatype_for_param1*/ = 0 /*default_value_for_param2*/
AS
    SELECT @param1, @param2
GO
EXECUTE dbo.sp_Emp 1 , 2 
GO
Output : Col1 Col2
          1    2

Example of Stored procedure creation on Azure Data Studio. If Any Doubt . Leave Your Comments. Happy Coding….

Post Author: adama