SQL Server Stored Procedure, Freelancer California

Freelancer and programmers California

1. What is a SQL Server Stored Procedure ?
A. A compiled set of commands stored on the database server
B. You give it a name…pass it parameters and it does the database manipulation.
2. Question: Should all of your data access be done through stored procedures?
A. Some companies…YES! They require it.
B. New thinking: Maybe
1. Simple selects, inserts, updates and deletes just use command object and executeSQL
2. More complex involving multiple transactions or centralized data validation…use Stored Procedures
3. Either case you need to be able to create Stored Procedures.
3. Creating a stored procedure from Query Analyzer
A. Start Query Analyzer and log in.
B. Select the correct database
C. -- use Create first time
D. -- use Alter there after
1. create proc spu_GetCustomers
2. AS
3. Select CustomerID, CompanyName from customers order by CompanyName
4.
5.
6.
7. Create Proc spu_GetOrders
8. @CustomerID nchar(5)
9. AS
10. Select OrderID, OrderDate, Freight
11. from Orders
12. where CustomerID = @CustomerID
13. Order by Orderdate desc
14.
15. --test
16. exec spu_getorders 'VINET'
17.
18. sp_helptext spu_GetCustomers
4. Calling a stored procedure from VS.Net
Private Sub CustomersLoad()
'calling a stored procedure with no parameters

Dim ds As DataSet = DataSQL.GetDataSet("spu_GetCustomers")


With ddlCustomers
.DataTextField = "CompanyName"
.DataValueField = "CustomerID"
.DataSource = ds
.DataBind()
End With


End Sub

Private Sub OrdersLoad()
'calling a stored procedure with one parameter
Dim cn As New SqlClient.SqlConnection(DataSQL.CS)
Dim cm As New SqlClient.SqlCommand()
Dim dr As SqlClient.SqlDataReader

cn.Open()
With cm
.Connection = cn
.CommandType = CommandType.StoredProcedure
.CommandText = "spu_GetOrders"
With .Parameters.Add("@CustomerID", SqlDbType.VarChar, 10)
.Value = ddlCustomers.SelectedItem.Value
End With
dr = .ExecuteReader(CommandBehavior.CloseConnection)
End With
or

***************
With grdOrders
.DataSource = DataSQL.GetDataSet("spu_GetOrders " & ddlCustomers.SelectedValue)
.DataBind()
End With