Jun 30 10
Creating stored procedures
Stored procedures are essentially functions that you can create in the database and reuse. What’s neat about them from a usability standpoint is that they can take input parameters and then return a result.
CREATE PROCEDURE procedure_name @param data_type = default_value, @param data_type = default_value, @param data_type = default_value AS -- statements for procedure here
The demo below shows a script that creates a simplified version of a SalesByCategory stored procedure that ships with SQL Server 2000’s Northwind sample database.
CREATE PROCEDURE SalesByCategory @CategoryName nvarchar(15), @OrdYear int = 1998 AS SELECT ProductName, SUM(OD.Quantity * (1-OD.Discount) * OD.UnitPrice) AS TotalPurchase FROM "Order Details" od, Orders o, Products p, Categories c WHERE od.OrderID = o.OrderID AND od.ProductID = p.ProductID AND p.CategoryID = c.CategoryID AND c.CategoryName = @CategoryName AND DATEPART(year,OrderDate) = @OrdYear GROUP BY ProductName ORDER BY ProductName --Call stored procedure EXEC SalesByCategory 'Seafood', 1997
Jun 29 10
Inserting a record in an ASP.NET project
string insertSQL;
insertSQL = "Insert into Movie (";
insertSQL += "CatID, MovTitle, DirID, ProdID, Duration, Description, ReleaseYear,url) ";
insertSQL += " Values ('";
insertSQL += ddlCategory.SelectedValue.ToString() + "','";
insertSQL += tbxTitle.Text.ToString() + "','";
insertSQL += ddlDirector.SelectedValue.ToString() + "', '";
insertSQL += ddlProducer.SelectedValue.ToString() + "' , '";
insertSQL += tbxDuration.Text.ToString() + "', '";
insertSQL += tbxDescription.Text.ToString() + "', '";
insertSQL += tbxReleaseyr.Text.ToString() + "', '";
insertSQL += FileUpload1.FileName.ToString() + "')";
SqlCommand cmd = new SqlCommand(insertSQL,con);
//try to open database and update query
int added = 0;
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
added = cmd.ExecuteNonQuery();
}
catch(Exception err){
Label1.Text = "Error Inserting Record";
Label1.Text += err.Message;
}
finally
{
con.Close();
}
if (added > 0)
{
Response.Redirect("/Admin/viewAllMovies.aspx");
}