question

David 2 1 avatar image
David 2 1 asked

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-sqlsql-server-2000stored-procedures
10 |1200 characters needed characters left characters exceeded

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

Kristen avatar image
Kristen answered

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 characters needed characters left characters exceeded

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

Madhivanan avatar image
Madhivanan answered

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

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

10 |1200 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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