how to create a table in stored procedure

Question

i want to create a table with stored procedure and that table should consist PK ,which i want to use as aFK in another table By the Way What is the SCOPE_IDENTITY. should we hav to use scope_identity to make the ID as primary key can any on explain me this Thanks in Advance

Prakash Chasiya · Answer

Hello sindhu Kavuri; Please check this. Create PROCEDURE sp_createtable @tablename nvarchar(10) AS BEGIN --Check that if your desired table is already in system or not. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@tablename) AND type in (N 'U' )) --If yes than drop it DROP TABLE [dbo].[@tablename] --Create table Declare @Query as nvarchar(max) set @Query = 'create table ' + @tablename + '( ID int not null primary key identity(1,1), FirstName varchar(50), LastName varchar(50), Salry money );' --Execute query will create table as per your requirement Exec(@Query); END GO You can execute this SP by below command. Exec sp_createtable 'tb_test' Let me know if more help needed. Thanks in advance. Prakash

Rajneesh Chaubey · Answer

You can't create a table inside stored procedure. We can make use of stored procedure to perform operation on existing table. SCOPE_IDENTITY will give you the ID of last inserted row of particular table and to do that you have to create a column and make it IDENTITY. (It is not necessary to make it primary key). For detailed practical explanation on Stored procedure visit this:-http://www.c-sharpcorner.com/UploadFile/rohatash/select-insert-update-delete-using-stored-procedure-in-sql/ Visit this to clear your concept on scope identity: http://www.c-sharpcorner.com/UploadFile/rohatash/identity-and-scope_identity-in-sql-server-2012/

Manish Kumar · Answer

Not understand this.... You can't create a table inside stored procedure. We can make use of stored procedure to perform operation on existing table. I think we can create a table inside a procedure. use master create proc uptest as begin if not exists (select 1 from sys.tables where name='dttest') begin create table dttest (id int) end end

how to create a table in stored procedure

Answers (3)