SQL Server : how to create tables in stored procedures?
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
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!
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.
- 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
DATEif 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 Serverdoes not support such construct. Also why would anyone wants to create the table if it already exists ?