Blogger Sandbox

In this example we will pass an input parameter to determine the result of the query.  We will pass in supplier id to get all the products that are provided by the supplier. Here is the code Use Northwind GO CREATE PROCEDURE dbo.selProductsBySupplierID @SupplierID int AS SELECT DISTINCT p.ProductID, p.ProductName, p.UnitPrice FROM Products p INNER JOIN Suppliers s ON p.SupplierID = @SupplierID GO The @SupplierID is the input parameter that the stored procedure expects, you use it in the join portion of the statement to match the SupplierID in the Products table. The DISTINCT keyword means that the query will only return one row in the result if there are duplicate rows. Type the following to execute the stored procedure with the supplier id of 1 EXEC dbo.selProductsBySupplierID 1 The result should look like this

Sometimes you need to call a stored procedure that only returns one value. It would be overkill to use the SqlDataReader to store just one value. You can use the SqlCommand.ExecuteScalar() method instead to retrieve just one value from the database. Here is how you would call the "GetProductsAvgPrice" stored procedure in the Northwind database. 1.  First you need create a stored procedure in the SQL Server that will return just one value the average price of products in Products table in the Northwind database. Run this code in the SQL Server query editor window USE Northwind; GO CREATE PROCEDURE GetProductsAvgPrice AS SELECT AVG(UnitPrice) FROM Products; GO 2.  In your C# code file you need the namespaces using System.Web.Configuration; using System.Data.SqlClient; using System.Data; 2. Then get the Northwind connection string value from the Web.config file string connectString = WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"]. Con...

Today we will be calling a stored procedure in SQL Server that we've created earlier in this blog call selProductsBySupplierID.  The stored procedure takes one input parameter call @SupplierID which takes an int. Here is how you would do it in C# 1.  First you need the namespaces using System.Web.Configuration; using System.Data.SqlClient; using System.Data; 2. Then get the Northwind connection string value from the Web.config file string connectString = WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"]. ConnectionString; 3. Now call the stored procedure and output the result from the SqlDataReader using (SqlConnection conn = new SqlConnection(connectString)) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = "selProductsBySupplierID"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = conn; SqlParameter parameter = new S...

In most of your projects you will have to work with stored procedures.  As a developer most of the time you only have to concern yourself with the basic stored procedures such as the SELECT, INSERT, UPDATE, and DELETE stored procedures.  If there's a DBA then you will probably be handed a stored procedure written by the database god.  But if you are the only developer in the five mile radius you might have to get your hands dirty and roll your own stored procedure.  In this tutorial we will be creating a select stored procedure. Here is how 1.  Right click on the "Northwind" database and then select "New Query" in "Microsoft SQL Server Management Studio" 2.  A new query window will be open type in the following into the query window to create the SELECT stored procedure. USE Northwind GO CREATE PROCEDURE dbo.ProductsSuppliers AS SELECT p.ProductID, p.ProductName, p.UnitPrice, s.CompanyName AS Supplier FROM Products p INNER JOIN Suppliers s ON p.Supplie...

Here is how you would create a stored procedure to update an a new record into the Products table in the Northwind database. USE Northwind GO CREATE PROCEDURE dbo.updProduct( @ProductID int, @ProductName nvarchar(40), @SupplierID int = null, --default is null @CategoryID int = null, @QuantityPerUnit nvarchar(20) = null, @UnitPrice money = null, @UnitsInStock smallint = null, @UnitsOnOrder smallint = null, @ReorderLevel smallint = null, @Discontinued bit) AS UPDATE Products SET ProductName = @ProductName, SupplierID = @SupplierID, CategoryID = @CategoryID, QuantityPerUnit = @QuantityPerUnit, UnitPrice = @UnitPrice, UnitsInStock = @UnitsInStock, UnitsOnOrder = @UnitsOnOrder, ReorderLevel = @ReorderLevel, Discontinued = @Discontinued WHERE Products.ProductID = @ProductID GO When you see a parameter with the = null, it means the field can have a null value. Since we need ProductID is needed to update a specific record we need it in the input parameter list. The...

To create the delete procedure type in the following code in the SQL editor window in "Microsoft SQL Server Management Studio" USE Northwind GO CREATE PROCEDURE dbo.delProduct @ProductID int AS DELETE FROM Products WHERE Products.ProductID = @ProductID GO The stored procedure only takes in one input parameter which is the ProductID, the DELETE statement needs a ProductID because if there is no WHERE clause, every record in the product in the Products table will be deleted. Make sure you backup the table before you work with a DELETE stored procedure. Here is how would execute the stored procedure EXEC dbo.delProduct 78 Blogs In the T-SQL Series: T-SQL: Stored Procedure (INSERT), INSERT A New Product In Northwind Part 1 ASP.NET : Stored Procedures (INSERT), Insert a new Northwind Product Part 2 T-SQL: Stored Procedures (DELETE), DELETE An Existing Northwind Product Part 3 T-SQL: Stored Procedures (UPDATE), UPDATE An Existing Product In Northwind Part 4 T-SQL: Stored Pro...

Today we will be calling a stored procedure in SQL Server that we've created earlier in this blog call addProduct.  The stored procedure takes the following input parameters. 1.  First you need the namespaces using System.Web.Configuration; using System.Data.SqlClient; using System.Data; 2. Then get the Northwind connection string value from the Web.config file string connectString = WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"]. ConnectionString; 3. Now call the stored procedure and output the result from the SqlDataReader using (SqlConnection conn = new SqlConnection(connectString)) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = "addProduct"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = conn; SqlParameter productName = new SqlParameter("@ProductName", "Teh"); productName.SqlDbType = S...

Here is how you would create a stored procedure to insert a new record into the Products table in the Northwind database. USE Northwind GO CREATE PROCEDURE dbo.addProduct( @ProductName nvarchar(40), @SupplierID int = null, --default is null @CategoryID int = null, @QuantityPerUnit nvarchar(20) = null, @UnitPrice money = null, @UnitsInStock smallint = null, @UnitsOnOrder smallint = null, @ReorderLevel smallint = null, @Discontinued bit) AS INSERT INTO Products(ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued) VALUES(@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice, @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued) GO When you see a parameter with the = null, it means the field can have a null value. Since the ProductID is auto incremented you don't include it. The data types must match the fields in the database. Here is how you would execute the stored pro...

A lot of times you want to call a stored procedure quickly in the code to access the stored procedure.  You don't want to go through the trouble of dragging an SqlDataSource control in the design view just to use the stored procedure. Here is how you would call the "Top Ten Most Expensive Products" stored procedure in the Northwind database. 1.  First you need the namespaces using System.Web.Configuration; using System.Data.SqlClient; using System.Data; 2. Then get the Northwind connection string value from the Web.config file string connectString = WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"]. ConnectionString; 3. Now call the stored procedure and output the result from the SqlDataReader using (SqlConnection conn = new SqlConnection(connectString)) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = "GetProductsAvgPrice"; cmd.CommandType = CommandType.Stor...