0

I need to maintain some values from one stored procedure and use them again in some other stored procedure.

Below is the part of my stored procedure where I am creating a global temp table.

IF object_id('tempdb..##TempNewSchedule') IS NULL
BEGIN
    CREATE TABLE ##TempNewSchedule (OrderId INT)
END

INSERT INTO ##TempNewSchedule 
VALUES(@OrderID)

So my requirement is like , whenever this SP will be called it will check if there is already a global temp table or not, If not then it should create a new global temp table , other wise it should insert the record into already created temp table. but every time it creates new temp table.

Please suggest any other approach also.

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
bhupesh
  • 104
  • 9
  • 1
    one way: Don't use a temporary table that goes away when the connection ends. Instead, create a 'permanent work table' that you can insert / update / delete as you wish. Give each resultset a unique id that you pass around. – Ryan Vincent Aug 28 '16 at 17:46
  • This seems too obvious, but: Save/read the value(s) to/from a table. – Bohemian Aug 28 '16 at 17:47
  • I also doubt that for temp tables , but i cant create new table as per the requirement. I hope there must be some other way round.. thanks for suggestion anyway.. – bhupesh Aug 28 '16 at 17:49
  • 3
    Global temporary tables are preserved between calls and across sessions but only until the session that created the table is terminated. If your session ends then the temporary table will be dropped automatically even if it contains data. – Martin Aug 28 '16 at 17:49
  • One typical example of a session ending in this way: an asp.net application that creates a new connection to the database for each procedure call. – Martin Aug 28 '16 at 17:50
  • so what i can do except using real table?? – bhupesh Aug 28 '16 at 17:52
  • True.. I guess before going one should make his back ready right .. that what i did ;) thanks everyone for solutions .. – bhupesh Aug 28 '16 at 18:16
  • You can also create normal tables into tempdb, which is basically the same thing as global temp. tables, except they are only dropped automatically when the server restarts. – James Z Aug 28 '16 at 20:01

0 Answers0