0

Is there any difference between direct querying and stored procedure?

[Below]

I have this query for 6 years because now I'm 7 months employed in a company, I use Select *Update* delete Insert in .net application using this query I don't know what it calls if this was a store procedure or a direct query or whatsoever but the output is the same 

<WebMethod()> _
    Public Function add(ByVal firstname As String, ByVal lastname As String)


        Dim con As SqlConnection
        Dim com As SqlCommand

        con = New SqlConnection(cstring)
        con.Open()
         com = New SqlCommand("SELCT * FROM NewSalestbl where Firstname = @Firstname and Lastname= @Lastname", con)

        com.Parameters.AddWithValue("@Firstname", firstname)
        com.Parameters.AddWithValue("@Lastname", lastname)
        Dim reader As SqlDataReader = com.ExecuteReader
        While reader.Read
            status = reader("Lastname").ToString
        End While

        MsgBox("Inserted")

        con.Close()

        Return status

    End Function

I want to know what is the best procedure to make when it comes to creating querys?

[BELOW]

I try to use this code now 

    Dim con As SqlConnection
    Dim com As New SqlCommand
    con = New SqlConnection(cstring)
    con.Open()
    com.CommandText = "searchtest"
    com.CommandType = CommandType.StoredProcedure
    com.Connection = con
    com.Parameters.AddWithValue("@firstname", TextBox1.Text)
    Dim raeder As SqlDataReader
    raeder = com.ExecuteReader
    While raeder.Read
        MsgBox(raeder(1))
    End While

can anyone explain this to me if this two examples are the same or not TIA

Regime Evangelista Lesmoras
  • 69
  • 1
  • 11

1 Answers1

1

The direct SQL is good because it's more portable. Hopping from SQL Server to another database? Change the text in the program, you don't need database access. Also good if your organization doesn't let you create stored procedures. (All db access in one person's hands, all desktop/web code in another's.)

The stored procedure is good because it's compiled. For a simple query, it may not matter much, but for longer, multi-statement queries it probably will. If there's an error in the query, you change it in one place on the server, there's no rolling out a new executable to a dozen desktops.

The answer is, test performance both ways, if there's not a significant difference then you should choose based on what makes maintenance the least hassle.

I prefer procedures, but your situation may vary.

NeedsAnswers
  • 169
  • 5
  • I think direct query is good if the programmer(s) is/are employed to a company coz they can just modify the codes and stored procedure is good t0o in terms of maintaining the queries in place – Regime Evangelista Lesmoras Dec 21 '17 at 02:40
  • 1
    Glad to see the use of parameters but .AddWithValue has its own problems. ADO.net has to guess what data type you want. Being worldly, ADO choses nvarchar for your TextBox1.Text. Your database, being created a while ago, is not worldly and its FirstName column is varchar. Now the database is not allowed to risk losing data so it widens every row to fit an nvarchar. Those lovely indexes that do so much to speed our queries become useless and we haven’t even gotten to LastName. A simple.. cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 100).Value = Text1.Text – Mary Dec 21 '17 at 03:30
  • @Mary can the nvarchar and varchar can effect the values of the inputs of the system? I just know that nvarchar can be use as normal inputs and varchar can be use in input like for example plate number of vehicle by the way thank you for sugesting to use .add property :D – Regime Evangelista Lesmoras Dec 21 '17 at 07:37
  • I wish a real DBA could chime in here but, as far as I know, a varchar cannot accept special characters like the n with the squiggle on top that is common in spanish. As the world grows smaller, the need for nvarchar grows even though it takes more space. Storage is cheap. No guarantees on the accuracy of this info. – Mary Dec 21 '17 at 18:45
  • What characters a varchar can accept depends on what character set it is defined with. CREATE TABLE FOO ( ID INT NOT NULL IDENTITY(1, 1), FrenchText VARCHAR(10) COLLATE French_CI_AS NULL, Entered DEFAULT(GETDATE()) ); You can pick a collation for whatever characters you want. The benefit of NVARCHAR is you don't have to choose, you can mix French and Japanese instead of having to pick one or the other since you're using unicode instead of a code page / collation. https://stackoverflow.com/questions/7781103/sql-server-set-character-set-not-collation – NeedsAnswers Dec 22 '17 at 03:06