Invalid ObjectName error in Stored Procedure

Question

Invalid ObjectName error in Stored Procedure

CodeLake

SSCrazy

Points: 2082
More actions
September 25, 2005 at 8:39 pm

#168870

Hi,
I am trying to create a stored procedure where each time the procedure is called the tempoprary table created by it will be unique. When I run the stored procedure I get an error saying the table name is invalid. I checked the TempDB and found that it's not creating the table at all . Follwoing is the Stored Procedure code.
IF EXISTS (SELECT [name] FROM sysobjects WHERE [name] = 'cspGetProductionAnalysisData' AND type = 'P')
BEGIN
DROP PROCEDURE dbo.cspGetProductionAnalysisData
END
GO
--
CREATE PROC dbo.cspGetProductionAnalysisData
(
@Date DATETIME
)
WITH ENCRYPTION
AS
DECLARE @TableName NVARCHAR(30);
DECLARE @TableSQL NVARCHAR(4000);
DECLARE @sql       NVARCHAR(4000);
DECLARE @WhereSQL NVARCHAR(4000);
--
SET @TableName = N'#PA'
    + REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(23), CURRENT_TIMESTAMP, 121), '-', ''), ':', ''), '.', ''), ' ', '')
    + N'Summary';
--
IF OBJECT_ID('tempdb..' + @TableName, 'U') IS NOT NULL
BEGIN
    SET @TableSQL = N'DROP TABLE ' + @TableName;
    EXEC sp_executesql @TableSQL
END;
--
SET @TableSQL = N'CREATE TABLE ' + @TableName
                + N'('
                + N' UID INTEGER IDENTITY(1,1) NOT NULL,'
                + N' DocketNumber VARCHAR(8)'
                + N')';
--
EXEC sp_executesql @TableSQL
--
SET @sql = N'INSERT INTO ' + @TableName + '(DocketNumber) VALUES (''1'')';
EXEC sp_executesql @sql
--
SET @TableSQL = N'SELECT * FROM ' + @TableName;
EXEC sp_executesql @TableSQL;
--
SET @TableSQL = N'DROP TABLE ' + @TableName;
EXEC sp_executesql @TableSQL;
This is how I am calling it:
EXEC cspGetProductionAnalysisData @Date = '20050101'
You can see the errors for urself. Please help.
Kindest Regards,
WRACK
CodeLake

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47796 More actions · Answer 1

Let me just get past the reson you would want to do this as temp tables are created uniquely for the connection anyway.

I think your problem is to do with scope. The table created by the sp_executesql command is in a different scope from the rest of the procedure. You can check this out easily enough by using a permanent table, or a global temporary table.

--------------------
Colt 45 - the original point and click interface

CodeLake SSCrazy Points: 2082 More actions · Answer 2

Thanks Phills

If the temp tables are created uniquely then if 2 users call the same procedure at the same time then what will happen..? i.e. if I remove the date time logic and just hard code the table name as #PASummary

Is it possible that they might get data overlaps or something like that..?

I don't understand what you mean by the scope of the table. Could you please shed some light on this..?

Kindest Regards,

WRACK
CodeLake

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 3

you can use the exec command

rather to use sp_executesql.

syntax is

exec (@stmt)

philcart SSC-Forever Points: 47796 More actions · Answer 4

Check the paragraph headed "Temporary Tables" in the description for CREATE TABLE in Books Online. If this doesn't clarify things then submit another post explaining what you don't understand.

--------------------
Colt 45 - the original point and click interface

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

No, he can't. As written it won't make any difference.

Though Phil is correct - BOL will explain why this whole naming scheme is unnecessary for local temptables. Just stating #myTable will be sufficient.

/Kenneth

CodeLake SSCrazy Points: 2082 More actions · Answer 6

Aha...Thanks all

So the tables starting with # are named as

#PASummary20050101115959101__________________________...___CA1234567890 format where the rest is added by SQL Server to make sure that they are always unique.

I have talked to my Project Manager and he says we still need to take of it ourself so I changed # to ## and it all works now. Super

Kindest Regards,

WRACK
CodeLake