CODEWITHSUNDEEP
sql-serverazure-sql-databaseuser-defined-typestable-valued-parameters
Van2k
Van2k

Reputation: 31

Is a table-valued parameter (UDT) data entry specific to a user session?

I use the user-defined table parameter to track the keyed in values and pass it to a stored procedure. Syntax works fine. No problem with it.

Question:

  1. Does it creates multiple instances at the same time for individual sessions?
  2. Does it maintain different inserted data sets by session?

Requirement:

  1. User enters exam names and scores through a front end UI. It could be multiple exams and corresponding scores.
  2. This data should go through a stored procedure and will pick relevant information for all the exams and scores the student has entered.
  3. Multiple users may be connecting to the application at the same time, and entering their exam results. Entered information must be specific to that particular user.

Alternate: if the UDT is not supporting multi instance, I am planning to get the User input in concatenated values and split it by list inside the stored procedure

Upvotes: 0

Views: 94

Answers (1)

Charlieface
Charlieface

Reputation: 72229

If by User Defined Table you mean a Table Valued Parameter, then yes, it is not just per session but per request.

They are stored in tempdb similar to temp tables, under a random name, but as soon as the request finishes and it goes out of scope then it is dropped. You cannot access it from another scope. Their names begin with @.

If you mean temp tables then it depends. While they are stored under a random name, sometimes they can be shared.

  • Local temp tables are accessible from the same session only, but from any scope (procedure) and any request. They are normally dropped as soon as the session closes. Their names begin with #.
  • Global temp tables are accessible from any session, and will be dropped only when the last session using it (including the one that created it) has closed. Their names begin with ##.

Upvotes: 0

Related Questions