Using a stored procedure to dynamically create a table

Question

Using a stored procedure to dynamically create a table

netmikem

SSCarpal Tunnel

Points: 4001
More actions
September 24, 2004 at 1:44 pm

#59616

Hello.
I'm trying to write a stored procedure in SQL Server 2000 that will accept a parmeter and attempt to create a table using it.
I've actually got the procedure working such that the table is created. My problem is that I can't find an elegant way to test first if it exists. You see, if the table exists, I don't want to issue the CREATE TABLE statement.
Here is what I have thus far ...
CREATE PROCEDURE DBO.CREATE_TABLE (@TABLENAME)
AS
SET @sql = 'CREATE TABLE ' + @TABLENAME + ' (' +
'[UserID] [varchar] (20) NOT NULL ,' +
'[Message] [varchar] (7000) NULL' +
') ON [PRIMARY]'
EXEC (@SQL)
GO
To reiterate, I want to find a way to test for existence and only execute the create statement if the table doesn't exist. Thanks.
- Mike

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

datawhizzz Newbie Points: 9 More actions · Answer 1

Hi Mike, how about this to test for existence?

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[@TABLENAME]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

Capri

netmikem SSCarpal Tunnel Points: 4001 More actions · Answer 2

Capri,

Thanks but I believe your suggestion results in an existence check for [<A href="mailtoBO.@TABLENAME">DBO].[@TABLENAME]. In other words, the contents of the parameter @TABLENAME is not being used here. It will always be checking for the same table.

I think I need to use the EXEC function but I somehow have to be able to check the results of it.

- Mike

Osoba SSCommitted Points: 1964 More actions · Answer 3

slip this in front of your create table statement:

declare @TABLENAME varchar(50)

set @TABLENAME= ?(However you're creating table names)

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[@TABLENAME]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

netmikem SSCarpal Tunnel Points: 4001 More actions · Answer 4

Hi there.

Respectfully, I disagree with you (and Capri). Did you actually try this?

I think you (and Capri) are assuming that @TABLENAME will be resolved to the value of the parameter but that is not the case.

I would love it if you were correct about this as it would solve my problem.

- Mike

olavho Say Hey Kid Points: 706 More actions · Answer 5

Hi,

I agree with Capri and Osoba on this. It should work - we're using this in several procedures. However, I noted a typo in both their examples - @tablename should not be included within the string delimiters. My example:

declare @tablename sysname

select @tablename = 'Items'

if exists (select 1 from sysobjects where id = object_id(N'dbo.' + @tablename))

print 'Exists'

else

print 'Not exists'

It's probably sensible to check for correct type as well. If you're using tablenames that may have spaces you should add the '[]' to query as well - or alternatively require the client to add them? Good luck.

netmikem SSCarpal Tunnel Points: 4001 More actions · Answer 6

Hello again.

Thanks very much for the tip. It worked!

Here is my procedure ...

CREATE PROCEDURE DBO.CREATE_TABLE (@TABLENAME VARCHAR(20))

AS

DECLARE @sql VARCHAR(800)

f not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].' + @TABLENAME) and OBJECTPROPERTY(id, N'IsUserTable') = 1)

BEGIN

SET @sql = 'CREATE TABLE DBO.' + @TABLENAME + ' (' +

'[UserID] [varchar] (20) NOT NULL ,' +

'[Message] [varchar] (7000) NULL ,' +

'[TimeSubmitted] [datetime] NULL DEFAULT (getdate())' +

') ON [PRIMARY]'

EXEC (@SQL)

END

GO

- Mike