How to write a stored procedure

[Mondeo]

I have a few visual basic functions that I want to move onto my SQL 2000 server. I've never written a stored procedure before, could someone give me a few pointers on how I would execute the following as an SP

 

Public Class SQLDatabase
Private conn As SqlConnection

Public Sub New
conn = New SqlConnection("MyConnectionString")
End Sub

Public Sub openDB()
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
End Sub

Public Sub closeDB()
If Not (conn.State = ConnectionState.Closed) Then
conn.Close()
End If
End Sub

Public Function GetCoOrds(ByVal postcode As String) As String

Try
Dim cmd As New SqlCommand("SELECT grideast,gridnorth From gridrefs WHERE postcode='" & postcode & "'", conn)
Dim rdr As SqlDataReader = cmd.ExecuteReader
If rdr.HasRows Then
While rdr.Read
Return = rdr(0).ToString + "," + rdr(1).ToString
End While
Else
Throw New Exception("No co-ordinates in database for " + postcode)
End If
Catch ex As Exception
Throw ex
End Try
End Function
End Class

 

I'm calling my function simply with

 

Dim db as New SqlDatabase
db.opendb
messagebox.show("the co-ordinates are " + db.GetCoOrds("TQ13OIU")
db.closedb

 

Thanks

[barski]

Create Procedure dbo.MyCoords

@postcode as int --i'm assuming postcode is an int

as

SELECT grideast,gridnorth From gridrefs WHERE postcode=@ 

 

make sure to change your OleCommandType to System.Data.CommandType.StoredProcedure