Create table stored procedure in SQL Server

Question

I have a table Persons (Id, Name), and I need to create a table with name Results_NameUser (where NameUser is the name of the user). This table is automatically generated after I create a user on my Form. I need use a stored procedure for this operation, but I don't know how to write this.

I have preliminarily code of a procedure (this procedure will called in code after create user)

CREATE PROCEDURE createResult
    @userName VARCHAR(50)
AS
BEGIN
    CREATE TABLE Result_@userName 
    (
        resultId UNIQUEIDENTIFIER NOT NULL,
        scores INT 
    );
END

But the code is not correct. Please, tell me, how create this procedure

You'll need to use `Exec`: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql — p e p, Apr 06 '18 at 17:35
Thanks, but i don't understand where i can use Exec in my code. Can you tell me? — Travis Bincle, Apr 06 '18 at 17:41
Possible duplicate of [T-SQL How to create tables dynamically in stored procedures?](https://stackoverflow.com/questions/10877407/t-sql-how-to-create-tables-dynamically-in-stored-procedures) — Tab Alleman, Apr 06 '18 at 17:46
Could this be done with a View instead of an additional table that a stored procedure would need to maintain? — jradich1234, Apr 06 '18 at 18:52

score 0 · Answer 1 · answered Apr 06 '18 at 18:15

I would add a check for an existing table before I try to create one, something like....

CREATE PROCEDURE createResult
    @userName NVARCHAR(120)
AS
BEGIN
  SET NOCOUNT ON;

  Declare  @Create_Sql  NVARCHAR(MAX)
         , @Drop_Sql    NVARCHAR(MAX)
         , @TableName   SYSNAME     = N'Result_' + @userName


 IF OBJECT_ID( @TableName , 'U') IS NOT NULL 
  BEGIN
      SET @Drop_Sql = N' DROP TABLE ' + QUOTENAME(@TableName) + N';' ;
      Exec sp_executesql @Drop_Sql
  END


 SET @Create_Sql = N' CREATE TABLE ' + QUOTENAME(@TableName)
                +  N' (resultId uniqueidentifier not null, '
                +  N' scores int); ';

 Exec sp_executesql @Create_Sql

END

Also use QUOTENAME() to protect yourself against SQL-Injection attacks.

Create table stored procedure in SQL Server

1 Answers1