How to use "Create Table" Dynamically ? 2

[sa5cha]

Hi,

I have the following Problem:

I want to write a stored Procedure which dynamically creates tables on different SQLDatabases and/or Servers.

The following doesn't work:

assumed that the procedure has been called whith this parameter:

CC_CreateTableMC 'server1.dbo.table1'

The Stored Procedure looks like this:

CREATE PROCEDURE CC_CreateTableMC
@ServerLocationString varchar
AS
CREATE TABLE  @ServerLocationString (field1 varchar(100))
RETURN

Can anyone help ?

Thank you in advance
Sascha

 

[guljar]

your have to make a string and then execute it.
say u declare:

varchar @strSql(200)--length is as you think should be
set @strSql='Create Table ' + @ServerLocationString +'(field1 varchar(100))'
execute(@strSql)

N.B. thus u can execute any dynamic SQL

 

[Cagliostro]

CREATE PROCEDURE CC_CreateTableMC
@ServerLocationString varchar
AS
begin
declare @xxx varchar(200)
set @xxx=
'CREATE TABLE ' + @ServerLocationString + ' (field1 varchar(100))'
execute(@xxx)
end John Fill

ivfmd@mail.md

 

[sa5cha]

Thank you guljar and John Fill for that fast response.

It works fine!
It solved a lot of problems for me.