x

Create Table From Variable

Hi All Can anyone help with this, I am trying to create a table from a variable. The create table section works fine but not the check exists first.

 ALTER PROCEDURE [dbo].[CreateResultsDB]
 ( 
      @ProgramID   Varchar(20)
 )
 AS

 DECLARE @CreateTableSQL varchar(2000)
 
 SET @CreateTableSQL = 'IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N"[dbo].[ICT_Results' + @ProgramID + ']") AND type in (N"U"))
 
 BEGIN
 
 CREATE TABLE [dbo].[ICT_Results' + @ProgramID + '] ([ResultsID] [int] IDENTITY(1,1) NOT NULL,[THid] [int] NULL,[TPid] [int] NULL,CONSTRAINT [PK_ICT_Results' + @ProgramID + '] PRIMARY KEY CLUSTERED ([ResultsID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]
 
 END'
 
 EXEC (@CreateTableSQL)
 
 GO 

This is what I get back after running the Proc

 Msg 102, Level 15, State 1, Line 1
 Incorrect syntax near '[dbo].[ICT_Results2]'.
 
 (1 row(s) affected)




more ▼

asked Jun 30, 2010 at 05:49 AM in Default

avatar image

Softman
1 1 2 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Switch

 OBJECT_ID(N"[dbo].[ICT_Results' + @ProgramID + ']")

for

 OBJECT_ID(N''[dbo].[ICT_Results' + @ProgramID + ']'')

You'll need to do the same for the TYPE='U' bit too.

more ▼

answered Jun 30, 2010 at 05:56 AM

avatar image

ThomasRushton ♦♦
42k 20 51 53

  • Well spotted. I was looking, scratching my head cause I couldn't find anything odd about the code. To me, the double quote looked like two singles.

Jun 30, 2010 at 02:01 PM Magnus Ahlkvist

I only spotted it when I copied the code out and into SSMS! The way that @Softman asked the question, the code hadn't been put into appropriate format, and I couldn't see anything wrong with it...

Jun 30, 2010 at 10:27 PM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

Thank you Thomas, that worked, been scratching my head over this one for a while until I thought of posting here.

more ▼

answered Jun 30, 2010 at 06:09 AM

avatar image

Softman
1 1 2 2

@Softman, if Thomas's answer helps you, please click the tick mark to accept the answer.

Jun 30, 2010 at 06:10 AM Kev Riley ♦♦

@Kev Riley - we have the power to do that now!! :)

Jun 30, 2010 at 01:41 PM Matt Whitfield ♦♦

@Matt Whitfield: Oh, the power to paint the answer's background in green! Seriously, this is a great feature, because it happens occasionally when people who ask the question do not mark the answer as accepted even though they actually like it. On the unrelated note, how can I get these "notifications in the @username format" to be delivered to my email?

Jun 30, 2010 at 03:42 PM Oleg

@Matt - \\o/

@Oleg - only occasionally?

Jun 30, 2010 at 10:27 PM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x119
x18

asked: Jun 30, 2010 at 05:49 AM

Seen: 1428 times

Last Updated: Jun 30, 2010 at 05:57 AM

Copyright 2017 Redgate Software. Privacy Policy