p1lot

HI this is probably a doddle to you guys but I am entering the world of stored procedures and am getting a bit muddled by the syntax.

What I want is to create a dynamically named table just passing a single string for the name of the table I tried my code "effort" below but doesnt seem to be working when I execute it.

use maelstrom
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE mCreateTable @TableName char(10)
CREATE TABLE #tablename
(
customerid int
IDENTITY(1,1)
PRIMARY KEY,
lastName varchar(100) NULL,
firstName varchar(100) NULL,
title varchar(10) NULL,
emailAddress varchar(100) NULL,
customerReference varchar(50) NULL
)


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GaryK

Hi,

Cant check it at the moment but you probably need to build the whole statement dynamically so something like

CREATE PROCEDURE <procname>
@TableName varchar(128)
AS
BEGIN
DECLARE @CreateTable varchar(1024)

SELECT @CreateTable = 'CREATE TABLE #' + @TableName +
'( <field defs here&gt'

EXEC (@CreateTable)

END

Hope that helps!

Gary

p1lot

hmm I dont really want to have loads of procedures!!

I just need a way to create tables on sql server set the columns etc and the name of the table. The only thing I see I need that is dynamic is the passing of a variable that sets the name for a new table.

GaryK

Err well you wont need loads of procedures, the code above will do what you want, whats the problem???

p1lot

I dont understand it

p1lot

OK I just did this:

I called the following stored proc from Cold Fusion passing it a string of "test" and it seemingly executes without error but when I go to enterprise manager it the test table is not there!

<cfstoredproc datasource="mailstrom" procedure="test1">
<cfprocparam cfsqltype="CF_SQL_VARCHAR" dbvarname="TableName" value="test">
</cfstoredproc>



CREATE PROCEDURE test1
@TableName varchar(128)
AS
BEGIN
DECLARE @CreateTable varchar(1024)

SELECT @CreateTable = 'CREATE TABLE #' + @TableName +
'( customerid int
IDENTITY(1,1)
PRIMARY KEY,
lastName varchar(100),
firstName varchar(100),
title varchar(10),
emailAddress varchar(100),
customerReference varchar(50)
)'

EXEC (@CreateTable)

END



This resulting in the creation of test1 which is executed as follows:

DECLARE @RC int
DECLARE @TableName varchar(128)
-- Set parameter values
EXEC @RC = [mailstrom].[dbo].[test1] @TableName


HELLLLLLLLLLPPPPPPPPP!!!


[Edited by p1lot - 14/07/2003 14:20:59]

GaryK

Yes its the '#' that denotes a local temporary table and a '##' that denotes a global temp table, you need to use double hash and it should work!

Cheers

Gary

p1lot

ahh cool gary I got it working!! thanks!!

Though I actually took all the hashes away two hashes didnt work either!!

GaryK

p1,

well the ## can only be used in the same transaction so if you issue the commands within a 'BEGIN TRANS'..'COMMIT/ROLLBACK' you should see the table, anyways you got it going, just dont forget to issue a DROP TABLE afterwards and your sorted!!!

Gary