Hi,
We are using a stored procedure to fetch data after a user enter filters/criteria.
If three different criteria under different classification are selected,then three temporary tables are created to store this condition initially.
Then the temporary tables values are used with original query to get matching records.
Will there will be issue of overlapping the data when multiple users are accessing
the same stored procedure or will it cause any lock situation?
No, there's no need to worry about that.
Quoting from Books Online:
Local temporary tables are visible only in the current session
Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE:
As, Above @denis_the_thief told.
There will be no issue. Have a look here to know about Global & Local Temporary tables.
Just to be clear:
If you create an @TEMP Table Variable, or a #TEMP temporary table, then that is only available within your session.
If you create a ##TEMP table then that is also available to other sessions.
Pretty sure you didn't mean the second!! but just wanted to be sure 