SQL Server : how to create tables in stored procedures?

stored procedure in sql server
stored procedure to create table and insert data
stored procedure to create database in sql server
advanced stored procedure examples in sql server
create or replace procedure sql server
stored procedures examples
create dynamic table in sql stored procedure
stored procedure in sql server 2018

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!

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

T-SQL How to create tables dynamically in stored procedures , How do I create a stored procedure in SQL Server? Search for Create A Sql Server on the New KensaQ.com

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

View the Definition of a Stored Procedure, How do I open a stored procedure in SQL Server? Get How To Build Sql Database. Get Instant Quality Info!

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

SQL Server stored procedures for beginners, as, and then click one of the following: Create To, Alter To, or Drop and Create To. SQL Server Management Studio. Transact-SQL. Using SQL Server Management Studio. To create a procedure in Object Explorer. In Object Explorer, connect to an instance of Database Engine and then expand that instance. Expand Databases, expand the AdventureWorks2012 database, and then expand Programmability. Right-click Stored Procedures, and then click New Stored Procedure.

Create Table and Stored Procedure in SQL Server and connect it , I am creating sample tables that will be used in the examples in this article Let us create a SQL Server stored procedure that accepts the input  1. This is a way to create tables dynamically using T-SQL stored procedures: declare @cmd nvarchar (1000), @MyTableName nvarchar (100); set @MyTableName = 'CustomerDetails'; set @cmd = 'CREATE TABLE dbo.' + quotename (@MyTableName, ' [') + ' (ColumnName1 int not null,ColumnName2 int not null);'; Execute it as:

Create table from stored procedure - MSDN, We will create a Table and Stored Procedure in Sql Server 2008R2 and data will be inserted and retrieved by using Asp.net using C#. Copying a SQL Server Stored Procedure’s Results Set to a Local Temp Table You can copy the results set from a stored procedure to a local temp table in a three-step process. In the first step, create a fresh copy of the stored procedure with a select statement that generates a results set whose output you want to persist.

https://doc.nuodb.com/Latest/Content/Creating-Data, How would I create a table from the following stored procedure and /wiki/​contents/articles/37872.sql-server-installation-on-centos-linux.aspx. Stored procedues in SQL allows us to create SQL queries to be stored and executed on the server. Stored procedures can also be cached and reused. The main purpose of stored procedures to hide direct SQL queries from the code and improve performance of database operations such as select, update, and delete data.

Comments
  • Welcome to SO! But...is this a homework assignment? We're not supposed to help with those.
  • No. I can see why you would think that though. I'm trying to send someone a table by using a store procedure, so they can just execute it. Unfortunately, I'm struggling to figure it out.
  • There's nothing wrong with helping someone on a homework assignment, but they should have tried something, failed, and ask questions with clear problem statements. Ideally, if that did occur before the assignment due date, they could ask their instructor, but usually these are last minute.
  • Please DO NOT store dates as NVARCHAR! There are date-specific datatypes for a good reason - use DATE if you only need date (no time), or DATETIME2(n) if you need both date and time.
  • You can also use dynamic sql to create a CREATE TABLE script, but this is cleaner.
  • MUCH cleaner then dynamic SQL! But, if the column names/types were to become parameters too, then dynamic SQL would be required.
  • CREATE TABLE IF EXISTS sp_CreateTable_TempTable. SQL Server does not support such construct. Also why would anyone wants to create the table if it already exists ?