Dynamic table name in sp?

Hi,

I need to create a stored procedure that accepts a string parameter containing a table name and truncates the table if it exists and creates it if it doesn't. Is there a way to do this without creating the whole sp in a string and then executing it?

For example, I can do this:

DECLARE @.ThisQuery VARCHAR(8000)

SET @.ThisQuery = 'Create Table ' + @.TableName <--this is the parameter

SET @.ThisQuery = @.ThisQuery + '...'

EXEC (@.ThisQuery)

Is there a better way that does not involve creating a dynamic query like this?

Thanks

I would convert the varchar(8000) to varchar(max); otherwise I would be converned that you might overflow your variable. Yes, this looks like it will work. As an aside I would personally try to avoid this type of generic operation.|||

Using Dynamic SQL for schema change issues is extremely dangerous. First, there would have to be a high level of permission to accomplish the task, and second, it could leave your server open to SQL Injection attacks.

You would be 'safer' using [ sp_executesql() ], and I recommend using unicode, so [ nvarchar(max) ] would be in order.

You may find this article by Erland worth the time to read.

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

|||

Thank you for this help. Yes, I understand it's dangerous, that's why I'm looking for another way to do it! The article seems great, but it is very long and verbose and after looking through it for a while now and I'm still not sure if it can be done and how. Is there possibly just some syntax someone can please give me that cuts through all this? I don't really need to understand it, I just need to know how to do it.

Many thanks.

|||

Well, it appears to me that with generic way that you are trying to do this that in the long run you are still going to have to have a script for each table create / truncate anyway. For me, I would just keep standard scripts for each table -- in fact, this is exactly what I do. I keep them in either Source Safe, Clear Case or ERWin

|||DECLARE @.ThisQuery VARCHAR(8000)
declare @.TableName sysname
set @.TableName = 'MyTab'
SET @.ThisQuery = 'if object_ID('''+@.TableName +''') is null '+ CHAR(13)+
'Create Table ' + @.TableName + '([Dummy] varchar(50))'
EXEC (@.ThisQuery)

|||You realize, I hope, that TRUNCATE TABLE will FAIL if there are PK-FK relationships.|||

>> Well, it appears to me that with generic way that you are trying to do this that in the long run you are still going to have to have a script for each table create / truncate anyway. For me, I would just keep standard scripts for each table -- in fact, this is exactly what I do. I keep them in either Source Safe, Clear Case or ERWin<<

100% agree with you here. Building tables in a stored procedure is generally a sign of using SQL in a non-preferred manner. We haven't been given any insight into the problem trying to be solved, but the "dummy" column makes it seem probably worse than it might actually be.

|||

I have to say, point well taken! After re-thinking my approach, I came up with a better way to do things that does not require a dynamic table name. Thanks.

Dynamic Sql: Truncate Table: Trap @@ERROR

Greeting All, I have a stored proc that dynamically truncates all the
tables in my databases. I use a cursor and some dynamic sql for this:

.....
create cursor
Loop through sysobjects and get all table names in my database.

...
exec ('truncate table ' + @.TableName)

Now, I want to be able to determine if an error occurred or not nad log
that error to a table in another database.

However, when I try to trap the value of @.@.ERROR after the
exec ('truncate table ' + @.TableName) when an actual error occurs it
fails. My error was synthetically created by placing a foreign key on
the table which precludes the option of truncation:

Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'MyTable' because it is being referenced by a
FOREIGN KEY constraint.

The actual relevant code snippet is:

BEGIN
BEGIN
SET @.v_RowCount = (
SELECT rowcnt
FROM sysindexes
WHERE id = (
SELECT id
FROM sysobjects
WHERE name = @.v_Name
)
AND indid IN (0,1)
)
EXEC('truncate table ' + @.v_Name)
-- If there was an error truncating the current table.
-- Write the event to the MessageLog table.
IF (@.@.ERROR <> 0)
BEGIN
SET @.v_OutputMessage = ('There was an error ' + @.v_name)
INSERT INTO MessageLog (message) values (@.v_outputmessage)
RETURN (-1)
END

Like I was saying, when the error is generated because of the foreign
key the variable @.@.error is never set to 4712, in fact if I were to put
a "select @.@.ERRROR" directly below the "exec('tru..')" statement it
would never be executed. The only thing that would show up in
Enterprise Manager would be the:

Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'MyTable' because it is being referenced by a
FOREIGN KEY constraint.
Any ideas as to what is going on here?

Thanks, TFD."LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1106171149.461401.53660@.f14g2000cwb.googlegro ups.com...
> Greeting All, I have a stored proc that dynamically truncates all the
> tables in my databases. I use a cursor and some dynamic sql for this:
>
> ....
> create cursor
> Loop through sysobjects and get all table names in my database.
> ...
> exec ('truncate table ' + @.TableName)
> Now, I want to be able to determine if an error occurred or not nad log
> that error to a table in another database.
> However, when I try to trap the value of @.@.ERROR after the
> exec ('truncate table ' + @.TableName) when an actual error occurs it
> fails. My error was synthetically created by placing a foreign key on
> the table which precludes the option of truncation:
> Server: Msg 4712, Level 16, State 1, Line 1
> Cannot truncate table 'MyTable' because it is being referenced by a
> FOREIGN KEY constraint.
> The actual relevant code snippet is:
> BEGIN
> BEGIN
> SET @.v_RowCount = (
> SELECT rowcnt
> FROM sysindexes
> WHERE id = (
> SELECT id
> FROM sysobjects
> WHERE name = @.v_Name
> )
> AND indid IN (0,1)
> )
> EXEC('truncate table ' + @.v_Name)
> -- If there was an error truncating the current table.
> -- Write the event to the MessageLog table.
> IF (@.@.ERROR <> 0)
> BEGIN
> SET @.v_OutputMessage = ('There was an error ' + @.v_name)
> INSERT INTO MessageLog (message) values (@.v_outputmessage)
> RETURN (-1)
> END
>
> Like I was saying, when the error is generated because of the foreign
> key the variable @.@.error is never set to 4712, in fact if I were to put
> a "select @.@.ERRROR" directly below the "exec('tru..')" statement it
> would never be executed. The only thing that would show up in
> Enterprise Manager would be the:
> Server: Msg 4712, Level 16, State 1, Line 1
> Cannot truncate table 'MyTable' because it is being referenced by a
> FOREIGN KEY constraint.
> Any ideas as to what is going on here?
> Thanks, TFD.

That particular error terminates the current batch, which is why the rest of
the code doesn't execute - Erland has a detailed article about error
handling in MSSQL, in which he points out that there is unfortunately no
real consistency about which errors will do this:

http://www.sommarskog.se/error-hand...#statementbatch

If your goal is to delete all rows from all tables, then truncation won't
work anyway, so you would have to look at something else. Cascading foreign
keys with DELETE would work, but of course it wouldn't perform as well (and
it wouldn't reset any identity seeds, if that's relevant). Rebuilding the
tables (and constraints and indexes) from scripts or restoring an 'empty'
backup would be other options.

Simon|||LineVoltageHalogen (tropicalfruitdrops@.yahoo.com) writes:
> However, when I try to trap the value of @.@.ERROR after the
> exec ('truncate table ' + @.TableName) when an actual error occurs it
> fails. My error was synthetically created by placing a foreign key on
> the table which precludes the option of truncation:
> Server: Msg 4712, Level 16, State 1, Line 1
> Cannot truncate table 'MyTable' because it is being referenced by a
> FOREIGN KEY constraint.

This error can be avoided before-hand by

IF NOT EXISTS (SELECT * FROM sysforeignkeys
WHERE rkeyid = object_name(@.tbl))

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland and Simon, thank you for your responses. The example I showed
you was created so that I could create an error (testing phase of a big
project) during the truncation operation, it was the only way I knew of
that would guarentee an error. This database actually has no foreign
keys in it, it is a persistent store which feeds a dimensional data
store for an OLAP server. This small snippet I brought forth is just a
tiny piece of a much larger data load. I just wanted to be able to
trap an error during the truncation process if one should arise, I
can't imagine any such thing happening but I just wanted to be able to
log it should it occur.

Regards, TFD.