how to create a table in stored procedure

Sindhu Kavuri

8y1k

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Prakash Chasiya8y ago
Accepted 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
Manish Kumar8y ago
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
Rajneesh Chaubey8y ago
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/