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.