Blogger Sandbox

WHILE SomeConditionTrue BEGIN -- Execute code here END

IF SomeCondition BEGIN -- Execute some code here END ELSE BEGIN -- Execute some code here END Else is optional

T-SQL variables allows you to store and assign values in your T-SQL code.  Variables is a storage unit in programming which allows you to refer back to it at a later time. T-SQL variables have the following characteristics: Local variables must be prefixed @ Global variables must be prefixed with @@ Must be declared with the DECLARE statement Must specify the data type when declared Declaring variables DECLARE @employeeID INT; DECLARE @firstName CHAR(10), @lastName CHAR(20); As you can see from the above example you can declare a single variable or declare multiple variables with a comma separated list. Setting variables SET @employeeID = 1; SET @firstName='Davolio'; SET @lastName='Nancy'; The above example uses the SET statement assign values to the variables one at a time. To assign values to multiple variables with one statement you can use the SELECT statement. SELECT @employeeID=1,@firstName='Dovolio',@lastName='Nancy'; Convert INT ...

Transaction processing is a concept in SQL that allows you to execute a query or rollback the changes if something goes wrong.  A way of enforcing the data integrity of the database.  As such, you can only rollback INSERT, UPDATE, and DELETE.  Not that there's any use in rolling back a SELECT statement because there's no change in data. The following is how you would wrap a transaction around a DELETE statement: BEGIN TRANSACTION DELETE Products WHERE ProductID = 87 COMMIT TRANSACTION The above query will only execute if there are no errors, if there's an error the transaction will be rolled back. That's it, that's the whole concept of what a transaction is, if there are no errors then you should get the following message. (1 row(s) affected) If you are dealing with multiple statements then you can use the SAVE TRANSACTION, SAVE TRANSACTION allows you to create a placeholder so that you can rollback a transaction at a checkpoint. For example if you were to INSERT a...

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...

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...