CODEWITHSUNDEEP
sql-server
HaydenH
HaydenH

Reputation: 3

SQL Server : how to create tables in stored procedures?

Task: write a SQL script to build a table. Call the script spCreateTable(), it should accept one argument, a varchar called subjectOfTable.

subjectOfTable is a varchar containing the subject of the table. Some examples are example “Employee” or “Formula.” Your script will use that subject to build the table name, primary key, and natural key.

For example, given “Employee” as the subject, your script will build a table called tEmployee with a surrogate key called EmployeeID and a natural key called Employee. Be sure to create the necessary constraints for the keys.

CREATE PROCEDURE [dbo].[sp_CreateTable]
    @subjectOfTable VARCHAR(30)
AS
BEGIN
    IF EXISTS (SELECT * FROM sys.objects 
               WHERE object_id = OBJECT_ID  (@tabName) AND type in (N'U'))
        DROP TABLE [dbo].[@tabName]

    CREATE TABLE @tabName
    (
        [ID] [INT] IDENTITY(1,1) NOT NULL,
        [RankID] [INT] NOT NULL,
        [SlotTime] [NVARCHAR](10) NOT NULL,
        [SlotDate] [NVARCHAR](30) NOT NULL
    ) ON [PRIMARY]
END

This is what I have so far, any help would be great. Thanks!

Upvotes: 0

Views: 271

Answers (3)

Ebis
Ebis

Reputation: 380

first: prefixing a stored procedure should be usually avoided

you can use dynamic sql like that: (but better read that before)

BEGIN TRAN;
GO
CREATE PROCEDURE [dbo].[CreateTable]
    ( @Tabname    SYSNAME 
    , @SchemaName NVARCHAR(128) = NULL
    ) AS
BEGIN;
    SET @SchemaName = ISNULL (@SchemaName, N'dbo');  
    IF ( SELECT object_id (@SchemaName + N'.'+@tabname ) ) IS NOT NULL
       EXEC ( N'DROP TABLE ' + @SchemaName + N'.'+@tabname)
    EXEC (N'CREATE TABLE ' + @SchemaName + N'.'+@tabname + N'
    (   [ID] [INT] IDENTITY(1,1) NOT NULL,
        [RankID] [INT] NOT NULL,
        [SlotTime] TIME NOT NULL,
        [SlotDate] DATE NOT NULL
    ) ON [PRIMARY]' 
    );
    RETURN 0;
 END;
 GO
SELECT OBJECT_ID (N'dbo.test')  AS OBJECT_ID
EXEC [dbo].[CreateTable] @tabname = test
SELECT OBJECT_ID (N'dbo.test')  AS OBJECT_ID

ROLLBACK

Upvotes: 0

Jerry Hung
Jerry Hung

Reputation: 160

Since I cannot comment, I'll just throw it out here What's the real usage of this code anyway? to create table often but same with columns?

I don't know how other person will run this (in SSMS? in application?)

FYI SSMS also has Template for create table (Ctrl+Alt+T), then he/she can press Ctrl+Shift+M to fill in the parameters every time.

If the columns are fixed, you can customize first and send it to them so they only change the part

-- =========================================
-- Create table template
-- =========================================
USE <database, sysname, AdventureWorks>
GO

IF OBJECT_ID('<schema_name, sysname, dbo>.<table_name, sysname, sample_table>', 'U') IS NOT NULL
  DROP TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
GO

CREATE TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
(
    <columns_in_primary_key, , c1> <column1_datatype, , int> <column1_nullability,, NOT NULL>, 
    <column2_name, sysname, c2> <column2_datatype, , char(10)> <column2_nullability,, NULL>, 
    <column3_name, sysname, c3> <column3_datatype, , datetime> <column3_nullability,, NULL>, 
    CONSTRAINT <contraint_name, sysname, PK_sample_table> PRIMARY KEY (<columns_in_primary_key, , c1>)
)
GO

Upvotes: 0

Russell Fox
Russell Fox

Reputation: 5425

You can create the table with a generic name, and then use the sp_rename stored procedure to rename it to the variable name:

CREATE TABLE IF EXISTS sp_CreateTable_TempTable (
[ID] [int] IDENTITY(1,1) NOT NULL,
[RankID] [int] NOT NULL,
[SlotTime] [nvarchar](10) NOT NULL,
[SlotDate] [nvarchar](30) NOT NULL
) ON [PRIMARY]
GO

EXEC sp_rename sp_CreateTable_TempTable, @subjectOfTable;
GO

Upvotes: 1

Related Questions