Keith3679
asked on
How do I use a variable for a Default vlaue in a SQL Create Table statement?
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]
...
The SQL script:
DECLARE @ParamID AS int
SET @ParamID = (SELECT Shift FROM DatabaseVersion WHERE ObjectID = 1)
...
CREATE TABLE [dbo].[IntercomProtocol](
...
[PartitionID] [dbo].[PartitionReference]
...
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 +
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 +
ASKER
What is the "set @sql = 'CREATE TABLE..." for? Why do you need this? Is the CREATE TABLE returning a value?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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.
ASKER
The general idea demonstrated was the solution. The sample partially worked.
declare @SQL varchar(max)
set @sql = 'CREATE TABLE [dbo].[IntercomProtocol](
...
[PartitionID] [dbo].[PartitionReference]
'
print @SQL
exec (@SQL)
That will do the trick. ;)