This is just an exercise of how to work with dinamyc SQL inside procedures. This one is a very simple example of how create a procedure that will create objects according to the information that you will provide, in this case it will be a table.
The procedure will receive two parameters: 1) The table name; 2) the list of columns with it datatypes. For the second parameter, each pair of name and datatype will be separated by comma. For example: you want two columns id with datatype int and name with datatype varchar(10), the paramenter will be ‘id int, name varchar(10)’.
With this rule defined, we can create our procedure.
SQL Server Sintax
CREATE PROC PROC_CREATE_TABLE
@TABLE_NAME VARCHAR(50),
@FIELDS_LIST VARCHAR(50)
AS
BEGIN
DECLARE @QUERY VARCHAR(8000)
SET @QUERY = ' CREATE TABLE '+@TABLE_NAME+'( '+@FIELDS_LIST+')'
PRINT(@QUERY)
EXECUTE(@QUERY)
EXEC SP_COLUMNS @TABLE_NAME
END
1 – DEFINE PARAMETERS: Note in the code above, we have the procedure receiving two parameters: one for the table name and other for the fields list.
2 – HANDLE THE PARAMETERS: Once the two parameters are received by the procedure, they will be concatenated with the code CREATE TABLE. The statement will be stored in on variable called @QUERY. We use the command PRINT just to show the statement after the concatenation.
3 – EXECUTE THE STATEMENT: We have the command “EXECUTE” which receives a SQL statement as string and executes it on the database. This command is the key to execute dynamic queries. You just need to apply your logic to concatenate or create a SQL statement, and after that you just need to submit this statement to the command “EXECUTE”.
4 – RETURN THE OUTPUT: Just to have an output and be sure that everything is ok, we will use the command sp_columns providing the table name as parameter. This command will return the information related to the columns of the new table.
We all know that instead of calling a procedure to create a table, you can just use the command create table providing all the column information that you need, but the purpose of this exercise is just to introduce the idea of working with dynamic SQL.
Now call the procedure
The script for this solution in on the link below.