CODEWITHSUNDEEP
sqlsql-serverparameterscreate-table
EyMarie
EyMarie

Reputation: 41

Can I use parameters in a CREATE query?

Actually I have 2 questions, both in SQL Server.

  1. There's a query like "Create Table" but for Create columns? If it exist, please, how it is?

  2. I need to use a "Create Table" but using parameters, but I don't know if that is possible. For example:

    @table_name string,
@column_name1 int,
@column_name2 int,
@column_name3 int

CREATE TABLE @table_name
    (
    @column_name1,
    @column_name2,
    @column_name3
    ....
    );

Obviously that's only what I have in mind and doesn't work. There is a right way to do it?

I appreciate your help!

Upvotes: 0

Views: 66

Answers (3)

hgulyan
hgulyan

Reputation: 8249

  1. By creating a column probably you mean adding a column to an existing table, right?

    use ALTER TABLE for that

    ALTER TABLE TableName ADD newColumn datatype

More info https://msdn.microsoft.com/en-us/library/ms190273.aspx

  1. For dynamically creating a table, try using sp_executesql function

      sp_executesql 'CREATE TABLE @table_name @column_name1 datatype1, 
     @column_name2 datatype2, 
     @column_name3 datatype3 
  )', 
  N'@table_name varchar(100),  
    @column_name1 varchar(100), 
    @column_name2 varchar(100),  
    @column_name3 varchar(100)', 
    @table_name, 
    @column_name1, 
    @column_name2, 
    @column_name3

More info here https://msdn.microsoft.com/en-us/library/ms188001.aspx

Upvotes: 1

HashSu
HashSu

Reputation: 1517

CREATE PROCEDURE create_table 
    @table varchar(10),
    @ID varchar(10),
    @Name varchar(20)

AS
BEGIN
    Declare @sql nvarchar(MAX)
    SET NOCOUNT ON
    Set @sql = 'CREATE TABLE '+@table+'('+@ID+' bigint NOT NULL,'+@Name+' varchar(25)) ON [PRIMARY]';
    exec (@sql)
END
GO

Upvotes: 1

Silvia Doomra
Silvia Doomra

Reputation: 953

For #1, use ALTER TABLE command.

For #2, how are you executing the query? You can create a string variable first with the complete command and table name as parameter and then execute the query. Something like:

declare @ CustomerID int 

set @ CustomerID = 3262833

declare @sql nvarchar(1000)



set @sql = N'SELECT * FROM [dbo].[Customers] AS [tbCustomers] WITH (NOLOCK) 

WHERE  tbCustomers.ID = '+ + cast(@CustomerID as nvarchar(10))   



exec (@sql)

Upvotes: 1

Related Questions