0

I have stored procedure that is creating temp table. This procedure is used to generate report in SSRS. Multiple users can use this report at the same time. I noticed that sometimes error appears: There is already an object named 'PK_#employees' in the database. oryginal thought was to create dynamic PK name with UUID, but it doesn't work anyway, and I don't know why.

I tried like this:

BEGIN
    DECLARE @PrimaryKey NVARCHAR(200)
        ,@guid UNIQUEIDENTIFIER
        ,@sql NVARCHAR(max)

    SELECT @guid = newid()

    SET @PrimaryKey = CONCAT (
            'PK_#employees'
            ,cast(@guid AS NVARCHAR(50))
            )

    SELECT @PrimaryKey = replace(@PrimaryKey, '-', '')

    IF OBJECT_ID('tempdb..#employees_test') IS NOT NULL
        DROP TABLE #employees_test;

    SET @sql = 'CREATE TABLE #employees_test
    (
        emplid INT NOT NULL
        ,created_at_date DATE NOT NULL
        ,job_indicator CHAR(1)
        ,name nvarchar(255)
        ,CONSTRAINT ' + @PrimaryKey + ' PRIMARY KEY (emplid, created_at_date)
    );'

    SELECT @sql

    EXECUTE (@sql)

    SELECT *
    FROM #employees_test
END

the EXECUTE works without error, but SELECT * FROM #employees_test returns: Invalid object name '#employees_test'.

If I run created @sql manually, it works correctly. What could be problem here?

Maybe some other idea how to avoid issue with There is already an object named 'PK_#employees' in the database. in that situation?

I am using below technique currently

IF OBJECT_ID('tempdb..#employees') IS NOT NULL
        DROP TABLE #employees;
CREATE TABLE # #employees
...
ThisOne
  • 13
  • 3
  • 1
    The temp table is dropped at the end of the `EXEC`. A simple workaround is to simply not name the constraint at all and have the system do this (assuming you're not intending to do anything with it later that requires an explicit name): just use `PRIMARY KEY(emplid, created_at_date)` without `CONSTRAINT`. Furthermore, if the table's scope is restricted to the stored procedure alone, there is no need to check for its existence -- it will be automatically dropped at the end. If you're getting conflicts there, something interesting is happening. Last but not least, a table variable is an option. – Jeroen Mostert Nov 20 '19 at 15:18
  • 1
    temp tables created in dynamic sql do not have scope in "real" sql statements. https://stackoverflow.com/questions/2917728/t-sql-dynamic-sql-and-temp-tables – Jeremy Nov 20 '19 at 16:25

0 Answers0