How To Create A Stored Procedure To Create A Table Passing Table Name In As A Parameter

Question

question

David 2 1 asked Dec 2, '09

How To Create A Stored Procedure To Create A Table Passing Table Name In As A Parameter

I am looking to create a stored procedure that once executed passes in the name of the table as a parameter which is then apended with the string '_TEXT'. However the below does not work:

create procedure tabcreate @tabname varchar(50)
as
create table @tabname+'_TEXT' (
col1 varchar(10))

Is this possible in SQL Server 2000?

Thanks in advance.

t-sql sql-server-2000 stored-procedures

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-12-02T07:31:54Z

Kristen answered Dec 2, '09

You will need to use dynamic SQL

            
create procedure tabcreate @tabname varchar(50)            
as            
DECLARE @strSQL varchar(8000)            
SELECT @strSQL = 'create table ' + @tabname + '_TEXT (            
col1 varchar(10))'            
            
EXEC (@strSQL)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2009-12-02T09:41:11Z

Madhivanan answered Dec 2, '09

Make sure you read this link fully to understand the dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question