none
SP and Local Temp Table

    Question

  • Hi Everyone,

    This is my first time doing something like this and would like to know if I'm doing it correctly.  I need to create a local #temp table and then insert some values in it.  I also need to check if the  record already exist and do an update statement.  I wrote the below Store Procedure but I'm not sure how SQL handle #temp table.  Do I have to perform a check before creating a #temp table to see if it already exist or will SQL  ignore the creation if it already exist? During the user created session, he might be inserting or updating the  #temp table several times.

    ALTER PROCEDURE dbo.usp_AppsCreateScratchTempTable
    @RaceDate datetime,
    @HorseName nvarchar(30),
    @RaceNo nvarchar(2),
    @TrackName nvarchar(3),
    @Scratch bit
    
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- SQL statements for procedure herew
    
    create table #temp_Scratch
    (
     [Race_Date] nvarchar(30) null,
     [Track] nvarchar(3) null,
     [Race_No] nvarchar(2) null,
     [Horse_Name] nvarchar(30) null,
     [Scratch] bit
    )
    
    END
    IF NOT EXISTS(SELECT * FROM #temp_Scratch Where Race_Date=@RaceDate and Track=@TrackName
    and Race_No=@RaceNo and Horse_Name=@HorseName)
    BEGIN
    Insert Into #temp_Scratch (Race_Date, Track, Race_No, Horse_Name, Scratch)
    Values(@RaceDate, @TrackName, @RaceNo, @HorseName, @Scratch)
    END
    Else
    BEGIN
    Update #temp_Scratch
    Set Scratch=@Scratch
    Where Race_Date=@RaceDate and Track=@TrackName
    and Race_No=@RaceNo and Horse_Name=@HorseName
    END

    Tuesday, October 29, 2013 3:02 PM

Answers

  • Hi Tom,

    I am not convinced that #temptable per user is a good idea.

    I would go for a more stable solution: shared permanent table with user id column such as cookie id if any.


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    • Marked as answer by zXSwordXz Wednesday, October 30, 2013 1:39 AM
    Tuesday, October 29, 2013 7:17 PM
    Moderator
  • I agree, that would probably be a good idea. 

    Mainly (IMO) it depends on what you want to have happen if you lose the connection but did not intend to sign off (for example because of datacomm errors or because the SQL Server service failed or was rebooted.  If you want the values in that table to still be available in that case then you must do this with a permanent table.

    On the other hand, if in that case you want the data to be gone and to start over with an empty table for the user when the user signs on, then each user having their own temp table will work well and can have some advantages.  Security would be one example, with a temp table you are guaranteed other connections cannot read your data.

    Tom

    • Marked as answer by zXSwordXz Wednesday, October 30, 2013 1:38 AM
    Tuesday, October 29, 2013 11:34 PM
  • Hi,

    Before going for any suggestions on how to handle temp table I prefer to say one thing. I see that in you code you are using BEGIN and END statement after AS clause, actualy the stored procs will execute only the code between BEGIN and END so you will ddefinitely end up with create table #temp_Scratch statement.

    ALTER PROCEDURE dbo.usp_AppsCreateScratchTempTable
    @RaceDate datetime,
    @HorseName nvarchar(30),
    @RaceNo nvarchar(2),
    @TrackName nvarchar(3),
    @Scratch bit
    
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- SQL statements for procedure herew
    
    create table #temp_Scratch
    (
     [Race_Date] nvarchar(30) null,
     [Track] nvarchar(3) null,
     [Race_No] nvarchar(2) null,
     [Horse_Name] nvarchar(30) null,
     [Scratch] bit
    )
    
    END
    IF NOT EXISTS(SELECT * FROM #temp_Scratch Where 
    ......
    ......


    

    Can you just try below code:

    ALTER PROCEDURE dbo.usp_AppsCreateScratchTempTable
    @RaceDate datetime,
    @HorseName nvarchar(30),
    @RaceNo nvarchar(2),
    @TrackName nvarchar(3),
    @Scratch bit
    
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- SQL statements for procedure herew
    
    create table #temp_Scratch
    (
     [Race_Date] nvarchar(30) null,
     [Track] nvarchar(3) null,
     [Race_No] nvarchar(2) null,
     [Horse_Name] nvarchar(30) null,
     [Scratch] bit
    )
    
    IF NOT EXISTS(SELECT 1 FROM #temp_Scratch Where Race_Date=@RaceDate and Track=@TrackName
    and Race_No=@RaceNo and Horse_Name=@HorseName)
    BEGIN
    	Insert Into #temp_Scratch (Race_Date, Track, Race_No, Horse_Name, Scratch)
    		Values(@RaceDate, @TrackName, @RaceNo, @HorseName, @Scratch)
    END
    Else
    BEGIN
    	Update #temp_Scratch
    	Set Scratch=@Scratch
    	Where Race_Date=@RaceDate and Track=@TrackName
    	and Race_No=@RaceNo and Horse_Name=@HorseName
    END
    
    END

    • Marked as answer by zXSwordXz Wednesday, October 30, 2013 1:39 AM
    Wednesday, October 30, 2013 1:23 AM

All replies

  • Hi,

    You can't create a #temp table twice in the same  session,Drop it before you use it in your procedure

    Best regards.

    Tuesday, October 29, 2013 3:08 PM
  • Hi,

    You can't create a #temp table twice in the same  session,Drop it before you use it in your procedure

    Best regards.

    Therefore, I should create a sperate Store Procedure to create the #temp  table when the user login and drop it when  the user logout? Because I need the table to be there for they entire session so it can remember temporary values that they user set.
    Tuesday, October 29, 2013 3:37 PM
  • Temp tables that are created in a stored procedure are automatically dropped when you exit the procedure.  So you definitely do not want to be creating this temp table inside this procedure.  If you do, you create the temp table, insert one row in it, then exit the procedure which will drop the temp table, so effectively your stored proc has done nothing.

    You want to create the temp table before calling the stored procedure.

    If necessary, you can test for the existence of a temp table named #temp_Scratch by checking Object_ID('tempdb..#temp_Scratch').  It will return NULL if that temp table does not exist in your connection.  So before calling the stored procedure, you could do

    If Object_ID('tempdb..#temp_Scratch') Is Null
    Begin
      create table #temp_Scratch
      (
       [Race_Date] nvarchar(30) null,
       [Track] nvarchar(3) null,
       [Race_No] nvarchar(2) null,
       [Horse_Name] nvarchar(30) null,
       [Scratch] bit
      )
    End
    

    Tom

    Tuesday, October 29, 2013 3:49 PM
  • No - you are not doing "it" correctly.  But let's take a step back and think about what you are trying to accomplish.  Assuming your stored procedure compiled/worked, executing it has no real effect - it does not change your existing data in anyway nor does it produce any output.  The only real thing it does is consume resources on the server.  So what are you trying to accomplish with this procedure? 

    Some additional comments:

    • Lack of consistency will cause you problems that are difficult to understand, find, and correct.  Your procedure has an argument named @RaceDate of type datetime.  First, does this need to be datetime or should it be date?  Next, your temp table has a similarly named column but a different datatype.  DO NOT DO THIS INTENTIONALLY unless you need to and understand the implications of changing the datatype.  In addition, some of your temp table columns have underscores separating words while your argument names do not.
    • Your temp table is composed of all nullable columns.  More than likely this should not be the case.  Only allow nullable columns where the value can actually be nullable. 
    • RaceNo is nvarchar(2).  Really?  Think about this.  Should it be a number?  Does it really need to be variable length?
    • TrackName (argument) becomes Track (temp table column), both of which are nvarchar(3).  Really?
    • A local temp table created within a stored procedure will be dropped when execution of the procedure ends.  Refer back to "what are you trying to accomplish".
    • When you create a temp table (or any table FWIW), it will be created without rows.  Your first IF statement will always evaluate to TRUE so you will always insert a row containing the values passed as arguments.  You really do not need to test for the (non)existence of rows.
    Tuesday, October 29, 2013 3:54 PM

  • Therefore, I should create a sperate Store Procedure to create the #temp  table when the user login and drop it when  the user logout? Because I need the table to be there for they entire session so it can remember temporary values that they user set.

    You can use global temp table like this:

    IF OBJECT_ID('tempdb..##Test', 'U') IS NULL 
        CREATE TABLE ##Test ( Id INT );
    
    
    SELECT * 
    FROM ##Test
    

    Or create a permanent table inside tempdb database like this sample:

    IF OBJECT_ID('tempdb..Test', 'U') IS NULL 
        CREATE TABLE tempdb..Test ( Id INT );
    
    
    SELECT * 
    FROM tempdb..Test


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Tuesday, October 29, 2013 4:04 PM
  • Thanks everyone...I really appreciate all the guidance. I know understand that my script  won't solve my issue but I would really appreciate if someone can make a suggestion.  This is what I'm trying to accomplish:

    1) I want to create a separate temp table for each user that log into my web application.

    2) This table must remain available for the user until they logout/close their web browser.

    3) During the user login session, they will be doing several insert and update statements.

    Is this possible? If so, how do I go about implementing this scenario?

    Thanks

    Tuesday, October 29, 2013 6:16 PM
  • Yes, it is possible.  When the user logs on, create a local temp table (that is one with a name that has one #, not a name that has 2 #'s - so your #temp_Scratch is fine) by executing a CREATE TABLE command.  Do NOT do that CREATE TABLE inside a stored procedure.  If you create it outside of all stroed procedures then that table will be available to this connection until you either explicitly DROP the table or you close the session (logoff).

    Tom

    Tuesday, October 29, 2013 6:43 PM
  • Thank you Tom...Appreciate your help. I will create this table from behind code with  a login event and drop it with the logout event. 

    Tuesday, October 29, 2013 6:53 PM
  • Hi Tom,

    I am not convinced that #temptable per user is a good idea.

    I would go for a more stable solution: shared permanent table with user id column such as cookie id if any.


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    • Marked as answer by zXSwordXz Wednesday, October 30, 2013 1:39 AM
    Tuesday, October 29, 2013 7:17 PM
    Moderator
  • Hi Kalman...Excellent idea.  I think I will try that approach. 
    Tuesday, October 29, 2013 8:19 PM
  • Instead of creating a temp table per user, just create one real table, with a userid in it.

    create table Cart
    (
     [UserID] int NOT NULL,
     [Race_Date] nvarchar(30) null,
     [Track] nvarchar(3) null,
     [Race_No] nvarchar(2) null,
     [Horse_Name] nvarchar(30) null,
     [Scratch] bit
    )
    

    Tuesday, October 29, 2013 8:49 PM
    Moderator
  • I agree, that would probably be a good idea. 

    Mainly (IMO) it depends on what you want to have happen if you lose the connection but did not intend to sign off (for example because of datacomm errors or because the SQL Server service failed or was rebooted.  If you want the values in that table to still be available in that case then you must do this with a permanent table.

    On the other hand, if in that case you want the data to be gone and to start over with an empty table for the user when the user signs on, then each user having their own temp table will work well and can have some advantages.  Security would be one example, with a temp table you are guaranteed other connections cannot read your data.

    Tom

    • Marked as answer by zXSwordXz Wednesday, October 30, 2013 1:38 AM
    Tuesday, October 29, 2013 11:34 PM
  • Hi,

    Before going for any suggestions on how to handle temp table I prefer to say one thing. I see that in you code you are using BEGIN and END statement after AS clause, actualy the stored procs will execute only the code between BEGIN and END so you will ddefinitely end up with create table #temp_Scratch statement.

    ALTER PROCEDURE dbo.usp_AppsCreateScratchTempTable
    @RaceDate datetime,
    @HorseName nvarchar(30),
    @RaceNo nvarchar(2),
    @TrackName nvarchar(3),
    @Scratch bit
    
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- SQL statements for procedure herew
    
    create table #temp_Scratch
    (
     [Race_Date] nvarchar(30) null,
     [Track] nvarchar(3) null,
     [Race_No] nvarchar(2) null,
     [Horse_Name] nvarchar(30) null,
     [Scratch] bit
    )
    
    END
    IF NOT EXISTS(SELECT * FROM #temp_Scratch Where 
    ......
    ......


    

    Can you just try below code:

    ALTER PROCEDURE dbo.usp_AppsCreateScratchTempTable
    @RaceDate datetime,
    @HorseName nvarchar(30),
    @RaceNo nvarchar(2),
    @TrackName nvarchar(3),
    @Scratch bit
    
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- SQL statements for procedure herew
    
    create table #temp_Scratch
    (
     [Race_Date] nvarchar(30) null,
     [Track] nvarchar(3) null,
     [Race_No] nvarchar(2) null,
     [Horse_Name] nvarchar(30) null,
     [Scratch] bit
    )
    
    IF NOT EXISTS(SELECT 1 FROM #temp_Scratch Where Race_Date=@RaceDate and Track=@TrackName
    and Race_No=@RaceNo and Horse_Name=@HorseName)
    BEGIN
    	Insert Into #temp_Scratch (Race_Date, Track, Race_No, Horse_Name, Scratch)
    		Values(@RaceDate, @TrackName, @RaceNo, @HorseName, @Scratch)
    END
    Else
    BEGIN
    	Update #temp_Scratch
    	Set Scratch=@Scratch
    	Where Race_Date=@RaceDate and Track=@TrackName
    	and Race_No=@RaceNo and Horse_Name=@HorseName
    END
    
    END

    • Marked as answer by zXSwordXz Wednesday, October 30, 2013 1:39 AM
    Wednesday, October 30, 2013 1:23 AM
  • I really appreciate all the help guys.. You  guys Rock!  BTW..I know that my table datatype need to correct.  I was typing with  thinking about datatype structure.
    Wednesday, October 30, 2013 1:47 AM
  • Hi,

    Before going for any suggestions on how to handle temp table I prefer to say one thing. I see that in you code you are using BEGIN and END statement after AS clause, actualy the stored procs will execute only the code between BEGIN and END so you will ddefinitely end up with create table #temp_Scratch statement.

    That's not correct.  A stored procedure is all the code from the CREATE PROCEDURE statement to the end of the batch (the end of the batch is either the end of the file of SQL statements or to a go command.  For example, try running the following, first create a stored procedure with

    Create Procedure TestBeginEnd
    As
    Begin
    Print 'Inside Begin/End'
    End
    Print 'After Begin/End'

    Then run

    Print 'Testing TestBeginEnd'
    Exec TestBeginEnd
    go
    Drop Procedure TestBeginEnd

    You will see that both Print statements are in the stored procedure.

    People often put the entire code of a stored procedure in a begin/end block, but if you look at the syntax for the CREATE PROCEDURE statement use of BEGIN/END around the entire code of the procedure is optional.

    Tom

    Tom

    Wednesday, October 30, 2013 2:25 AM
  • Hi,

    Before going for any suggestions on how to handle temp table I prefer to say one thing. I see that in you code you are using BEGIN and END statement after AS clause, actualy the stored procs will execute only the code between BEGIN and END so you will ddefinitely end up with create table #temp_Scratch statement.

    That's not correct.  A stored procedure is all the code from the CREATE PROCEDURE statement to the end of the batch (the end of the batch is either the end of the file of SQL statements or to a go command.  For example, try running the following, first create a stored procedure with

    Create Procedure TestBeginEnd
    As
    Begin
    Print 'Inside Begin/End'
    End
    Print 'After Begin/End'

    Then run

    Print 'Testing TestBeginEnd'
    Exec TestBeginEnd
    go
    Drop Procedure TestBeginEnd

    You will see that both Print statements are in the stored procedure.

    People often put the entire code of a stored procedure in a begin/end block, but if you look at the syntax for the CREATE PROCEDURE statement use of BEGIN/END around the entire code of the procedure is optional.

    Tom

    Tom

    Hi Tom, 

    Accepted and apologies for wrong statement, its not BEGIN -- END key words it RETURN key word.

    Again sorry and Thanks for description.

    .

    Wednesday, October 30, 2013 5:59 AM