>See for me that is a case where creating a SP is not necessary and will bring to the table even more complexability to the app and more maintenance prbs.

Alexandre,

Whatever you use, be it SP or SPT if a database is not properly designed, there be maintenance problem that could arise and besides why would a developer makes it complex? Aren't developers accustomed to a KISS?

>Same example with SPT with more added functionality where you pass the client name separated by commas and the app will return all the names whose lastname and firstname fields have those strings. Also off course this would be part of a dataobject class and not embedded on the application code itself like your example.
>

>Dim strConn As StringConnection = ConfigurationSettings.AppSettings("INSURANCE")
>Dim objConn As New SqlConnection(strConn)
>Dim objComm As New SqlCommand("SELECT * FROM Clients",objConn)
>Dim strs as String() = txtsearch.text.Trim.Split(",")
>Dim str As String = ""
>Dim strWhere as String = ""
>dim i as Integer = 0
>For Each str in strs
>   i+=1
>   strWhere += "(firstname = @Name" & i.ToString & " OR lastname = @Name" & i.ToString & ") AND "
>   objComm.Parameters.AddWithValue("@Name" & i.ToString, str.Trim)
>Next str
>'
>' This should never happen, the checks should have been done prior,
>' but I like to add checks also on the data layer so if something happens
>' it will raise an error, that is easy to spot in the code.
>'
>If strWhere = "" Then
>   Throw New ApplicationExecption("You need to specify the client name.")
>Else
>   strWhere = strWhere.Substring(0, strwhere.Lenght - 5)
>End If
>
>objComm.CommandText = "SELECT * FROM clients WHERE " & strWhere
>Dim adapt as New SqlAdapter(objComm)
>Dim dsData as New Dataset
>adapt.Fill(dsData, "Clients")
>'... any more code u need
>

>
>>>Jojo to your example when I'm talking about parameters I'm saying to use params when calling SPT, of course SP's use parameters, now to you example if you have a web app where you enter the client name and want to return a list of clients that have that name are you going to create a SP for that?
>>
>>Yes.
>>Sample,

>>

>>Dim strConn As StringConnection = ConfigurationSettings.AppSettings("INSURANCE")
>>Dim objConn As New SqlConnection(strConn)
>>Dim objComm As New SqlCommand("sp_searchcustomer",objConn)
>>objCommand.CommandType = CommandType.StoredProcedure
>>
>>Dim objParam As New SqlParameter("@clientname",SqlDbType.NVarChar, 50)
>>objComm.Parameter.Add(objParam)
>>objParam.Direction = ParameterDirection.Input
>>objParam.Value = txtsearch.text
>>
>>Dim objOutPutParam As New SqlParameter("@results", SqlDbType.Int)
>>objComm.Parameters.Add(objOutPutParam)
>>objOutPutParam.Direction = ParameterDirection.Output
>>
>>objConn.Open()
>>
>>'code blocks
>>.....
>>

>>