Compilemode

In this article, we will learn how to get the return value using the SQL stored procedure Output parameter. There are some scenarios where we need the return value using the SQL stored procedure such as whenever a new order is created into the database, then return an OrderId to the user for reference.

Let's consider the similar scenario and we have the following table OrderDetails which maintains the processed orders as shown in the following image.

I hope you have created the same table structure as shown above. Now create the stored procedures to get the return value as in the following code snippet.

Create PROCEDURE PlaceOrder  
(  
@Product varchar(50),  
@Amount decimal(18,2),  
@OrderId int out  
)  
AS  
BEGIN  
SET NOCOUNT ON;  
  
INSERT INTO [dbo].[OrderDetails]  
           (  
            Product,  
            Amount
           )  
     VALUES  
           (  
          @Product,  
          @Amount
           )  
select @OrderId=isnull(max(OrderId),0) from OrderDetails

END 

Summary

Related articles

In this article, we will learn how to use the SQL stored procedures with dapper ORM by writing a few lines of code. Let's consider we have the following values that need to be inserted into the SQL database using a stored procedure with the help of dapper ORM.

• Name
• City
• Address

Create the stored procedure to map the above parameters.

Create procedure [dbo].[AddNewEmpDetails]  
(  
   @Name varchar (50),  
   @City varchar (50),  
   @Address varchar (50)  
)  
as  
begin  
   Insert into Employee values(@Name,@City,@Address)  
End 

    public class Employee

    {     
       [Required(ErrorMessage = "First name is required.")]
        public string Name { get; set; }
        [Required(ErrorMessage = "City is required.")]
        public string City { get; set; }
        [Required(ErrorMessage = "Address is required.")]
        public string Address { get; set; }

    }

       public void AddEmployee(Employee objEmp)

        {
            //Passing stored procedure using Dapper.NET
                connection();
                con.Execute("AddNewEmpDetails", objEmp, commandType: CommandType.StoredProcedure);
               
       }

Related Article

In this article, we will learn how to get the return value using the SQL stored procedure Out parameter. There are some scenarios where we need the return value using the SQL stored procedure such as whenever a new order is created into the database, then return an OrderId to the user for reference.

Let's consider the similar scenario and we have the following table OrderDetails which maintains the processed orders as shown in the following image.

I hope you have created the same table structure as shown above. Now create the stored procedures to get the return value as in the following code snippet.

Create PROCEDURE PlaceOrder  
(  
@Product varchar(50),  
@Amount decimal(18,2),  
@OrderId int out  
)  
AS  
BEGIN  
SET NOCOUNT ON;  
  
INSERT INTO [dbo].[OrderDetails]  
           (  
            Product,  
            Amount
           )  
     VALUES  
           (  
          @Product,  
          @Amount
           )  
select @OrderId=isnull(max(OrderId),0) from OrderDetails

END 

Summary

Related articles

Prerequisites

If you are new to the Azure SQL, then please watch my following videos on the Azure SQL database.

How To Create Azure SQL Database

How To Connect Azure SQL Database

The following two commands are used to delete the records from the Azure SQL table.

delete from TableName where ColumName=value

delete from Employee where Id=1

Deleting all records

delete from Employee

Using Truncate command

truncate table Employee

Summary

In this article we will learn about the Azure SQL alter statement. In the previous article we have learned how create and connect to the Azure SQL database. If you are new to the Azure SQL, then please watch my following videos on the Azure SQL database.

How To Create Azure SQL DataBase

How To Connect Azure SQL DataBase Using SQL Management Studio

What is SQL Alter Table Statement?

Alter statement used to modify the structure of the existing table. The alter statement used is to add, drop the table column. It can also be used to change the data types of the column etc.
Let us consider we have the table having name Employee in our Azure SQL Server database having the following records,

Add Column To Existing Table Using Alter Statement

The following SQL query will add the new column Salary into the existing table.

ALTER TABLE Employee
ADD salary  decimal(50);

You can alter the multiple columns at a time, the following query will alter the multiple columns of the existing table.

ALTER TABLE Employee ADD
(
Location varchar (50),
EmailID Varchar (50)
)

ALTER TABLE Employee 
MODIFY Salary  decimal(60);

ALTER TABLE Employee
DROP COLUMN Salary;