I would like to create stored procedure, which creates me new table, but the problem is that the table name and column (count and datatypes of columns) is dynamic.
So, the procedure should take probably two parameters: table name and list of columns (in this list are names of columns and datatypes)
Is there any way to do something like this?
You can create dynamic SQL something like this.
For sending list, datatable you need to create USER DEFINED TABLE
CREATE TYPE [dbo].[myColumnDatatyes] AS TABLE(
columnName [NVARCHAR](200) NULL,
columnDataType [NVARCHAR](200) NULL
);
Then in the SP use this UDT something like this, create cursor to loop over table and make your dynamic SQL statement.
Create Procedure sp_CreateDynamicTables(
@tableName nvarchar(150),
@tableSchema [myColumnDatatyes] readonly
)
AS BEGIN
DECLARE @sql NVARCHAR(MAX);
set @SQL= N' Create table '+QUOTENAME(@tableName);
set @SQL =@SQL+ ' Here loop over @tableSchema to add N Columns '
EXECUTE sp_executesql @sql
END
varchar) have ended up in multiple column(s) (or multiple table(s)). In a well designed data model, any variations should be occurring at the level of data (values in other columns) rather than metadata.