Efficient Method for a stored Procedure

• SqlServerNinja

  Ten Centuries

  Points: 1238

  More actions

  March 11, 2013 at 12:05 pm

  #394356

  Hello,

  I am a Sql Server Newbie.

  I am writing a stored procedure which has one input parameter

  I have written it as shown below using two temporary tables,

  and I wanted to know if there is any better efficient way of writing it.

  -- I have simplified the stored procedure

  StoredProcedureX

  @Id Int (Id sent from another stored procedure)

  Create TempTable 1 (Name1 String, Name2 String, Name3 String, Marks int)

  Insert INTO TempTable1

  select Name,Name1, Name2, Marks from TableA where Id = @Id.

  Create TempTable2 (Address,City)

  Insert Into TempTable2

  select Address, City from TableA where Name = (select Name from TempTable1) AND

  Name1 = (select Name1 from TempTable1) AND Name2 = (select Name2 from TempTable1)

  AND MARKS > 100;

  --- Some other processing on TempTable2

  -- drop tables TempTable1 & TempTable2

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  March 11, 2013 at 1:21 pm

  #1595712

  Love & Peace (3/11/2013)

  ---

  Hello,

  I am a Sql Server Newbie.

  I am writing a stored procedure which has one input parameter

  I have written it as shown below using two temporary tables,

  and I wanted to know if there is any better efficient way of writing it.

  -- I have simplified the stored procedure

  StoredProcedureX

  @Id Int (Id sent from another stored procedure)

  Create TempTable 1 (Name1 String, Name2 String, Name3 String, Marks int)

  Insert INTO TempTable1

  select Name,Name1, Name2, Marks from TableA where Id = @Id.

  Create TempTable2 (Address,City)

  Insert Into TempTable2

  select Address, City from TableA where Name = (select Name from TempTable1) AND

  Name1 = (select Name1 from TempTable1) AND Name2 = (select Name2 from TempTable1)

  AND MARKS > 100;

  --- Some other processing on TempTable2

  -- drop tables TempTable1 & TempTable2

  The code you posted is NOT using temp tables. It instead is creating permanent tables and treating them like temp tables. This may not seem like a big deal at first but you have a serious concurrency issue here. What happens when 2 people are running this at the same time? The first one will run but the second one will fail. To use temp tables they must be defined as temp tables. #TempTable1 and #TempTable2. The # at the beginning indicates it is a temp table. This means that the connection that created the temp table is the only connection that can see it. Anybody else running the same sproc will have their own version of the tables.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/