Solved

How do I use a variable for a Default vlaue in a SQL Create Table statement?

Posted on 2010-09-15
7
1,123 Views
Last Modified: 2012-05-10
I'm trying to modify a script to use a variable (@ParamID) as a Default value for a column in my Create Table statement, however when I execute the script below, I get an error "Variables are not allowed in the CREATE TABLE statement."  Can this be done?  Could somebody give me a sample please?  Any way to use the value directly from the Select statement without using the variable?

The SQL script:

DECLARE @ParamID AS int
SET @ParamID = (SELECT Shift FROM DatabaseVersion WHERE ObjectID = 1)
...
CREATE TABLE [dbo].[IntercomProtocol](
...
[PartitionID] [dbo].[PartitionReference] DEFAULT (@ParamID) NOT NULL,
...
0
Comment
Question by:Keith3679
  • 3
  • 3
7 Comments
 
LVL 2

Expert Comment

by:dantheanswerman
ID: 33686495
SELECT @ParamID =Shift FROM DatabaseVersion WHERE ObjectID = 1)

declare @SQL varchar(max)
set @sql = 'CREATE TABLE [dbo].[IntercomProtocol](
...
[PartitionID] [dbo].[PartitionReference] DEFAULT (''' + @ParamID + ''') NOT NULL,

'
print @SQL
exec (@SQL)


That will do the trick. ;)
0
 
LVL 2

Expert Comment

by:dantheanswerman
ID: 33686536
SELECT @ParamID =Shift FROM DatabaseVersion WHERE ObjectID = 1)

declare @SQL varchar(max)
set @sql = 'CREATE TABLE [dbo].[IntercomProtocol](
...
[PartitionReference] int DEFAULT (' + @ParamID + ') NOT NULL,

'
print @SQL
exec (@SQL)

-- Few changes -- you only add the field name in the table creation script, as as you're probably using an integer for the type for column [PartitionReference], you do not need the extra quotes around the @ParamID -- only one single quote on either side of the +
0
 

Author Comment

by:Keith3679
ID: 33686758
What is the "set @sql = 'CREATE TABLE..." for?  Why do you need this?  Is the CREATE TABLE returning a value?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 2

Accepted Solution

by:
dantheanswerman earned 500 total points
ID: 33686892
Nope.. but you can't have variable in a table creation statement unless you script it... it's a string of text that you execute to create the table.

so you wrap your entire table create statement in quotes and execute it in a batch with the "exec (@SQL)" command.

The quotes can be tricky... if you like, you can post your entire create statement, I'll show you how it's done.
0
 
LVL 7

Expert Comment

by:Anoo S Pillai
ID: 33687704
Answer to "What is the "set @sql = 'CREATE TABLE..." for? :- "

While using this trick you are creating a string which is exactly the command to be run on SQL server.  ( You are opting this technique as you do not know the default value , and hence NOT the exact  table create command until runtime) . Strings which are valid SQL commands can be executed useing EXEC () or sp_execteSQL.  

Search for Dynamic queries for more details.
0
 

Author Comment

by:Keith3679
ID: 33687950
Ahh, it's a string--I didn't see the other quote!  It makes perfect sense.  Thanks for the info, I will try this tomorrow and let you know how it goes.
0
 

Author Closing Comment

by:Keith3679
ID: 33697460
The general idea demonstrated was the solution.  The sample partially worked.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL Bulk load data error 5 34
Convert int to military time 8 20
SQL Server creating a temp table 7 39
Report Builder 9 31
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now