Follow up to "moving sqls to SP" question

Results 1 to 2 of 2

Thread: Follow up to "moving sqls to SP" question

  1. #1
    Join Date
    Dec 1969

    Default Follow up to "moving sqls to SP" question

    I asked a question a while back and MorningZ responded that he has his SQLs in stored procedures. <BR><BR>My question is: should I move even my simplest select/update statements to stored procs? is this best practice?

  2. #2
    Join Date
    Dec 1969

    Default Multiple Reasons for PROCs

    In embedded SQL (when your SQL is in your code):<BR>- every time the page gets run, SQL Server "parses" the SQL statement making sure the statement is valid, checks the columns for existance, etc (taking time to do this)<BR><BR>In Stored Procedures<BR>- this "parsing" only happens the first time, then SQL server remembers that "hey, this SQL statement is valid, i don&#039;t have to check again"<BR><BR>Also, i personally like (and this might not go for everyone) PROCs because its super easy to work with Microsoft Data Access Blocks, something I swear by and use as much as humanly possible (<BR><BR>Why are they so neat?<BR><BR>beacause of instead of this:<BR><BR>[code language="VB.NET"]Dim myConnection As SqlConnection<BR>Dim myCommand As SqlCommand<BR>Dim myDataReader As SqlDataReader<BR><BR>myConnection = New SqlConnection( "server=localhost;uid=sa; pwd=secret;database=Pubs" )<BR>myConnection.Open()<BR>myCommand = New SqlCommand( "Select * from Authors", myConnection )<BR>myDataReader = myCommand.ExecuteReader()<BR><BR>.... Do stuff with the DataReader ...<BR><BR>myDataReader.Close()<BR>myConnection.Cl ose()[/code]<BR><BR>you can simply say (and this really goes for PROCs or Embedded SQL, but you&#039;d also gain the beforementioned compiling as well):<BR><BR>[code language="VB.NET"]Dim myDataReader As SqlDataReader<BR>myDataReader = SQLHelper.ExecuteReader("server=localhost;uid=sa; pwd=secret;database=Pubs", CommandType.Text, "SELECT * FROM Authors")<BR><BR>.... Do stuff with the DataReader ...<BR><BR>myDataReader.Close()[/code]<BR>personally, the less i type the better :)<BR><BR>and the blocks can return DataSets, a Scalar value, or do a NonQuery... all in one single line of code

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts