ASP.Net Stored Procedure

Question

1.00/5 (1 vote)

See more:

SQL

Alter procedure VisitorDetails1
@name nvarchar(50),
@City nvarchar(100),
@Dept nvarchar(max),
@TableName nvarchar(50)
 
as
 
begin
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=@TableName))
begin
if(Exists(Select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@TableName))
begin
print 'Datas Already Exists'
end
else
begin
Declare @set nvarchar(max)
set @set='insert into '+@TableName+' values('''+@name+''','''+@City+''','''+@Dept+''')'
 
exec(@set)
Print 'Successfully inserted...'
end
end
else
begin
 
Declare @set1 nvarchar(max)
set @set1='create table '+@TableName+' (Name nvarchar(50),City nvarchar(50),Dept nvarchar(50))'
exec(@set1)
set @set='insert into '+@TableName+' values('''+@name+''','''+@City+''','''+@Dept+''')'
exec(@set)
print 'Table is Not there So Created Successfully and Inserted Datas...'
end
end
GO

-----------
exec VisitorDetails1 Perumal1234,Chennai4,IT4,VSampleData1(TableName)
--------------

----------------
Above is my stored procedure..

1.Actually i need to check if the table is exist or not.if not it should Create the table dynamically at the same time insert the values in that table.
2.if table exist means only insert the values is enough.
3.here we check if the one or more column values are exist or not. if exist it should display 'Datas Already Exists' else values should insert in that table.

pls Help Me sir ...
Advance thanks a lot..

Posted 8-Jan-13 2:08am

Perumalkutti

Updated 8-Jan-13 2:57am

Santhosh Kumar Jayaraman

v4

Add a Solution

Comments

Sandeep Mewara 8-Jan-13 8:29am

This is not a well framed question! We cannot work out what you are trying to do/ask from the post. Please elaborate and be specific.
Use the "Improve question" link to edit your question and provide better information.

Zafar Sultan 8-Jan-13 8:38am

Do you want to check if the table exist or not? If it exist the procedure should check if the values you are passing exist or not? If not, it should insert the values. If yes, it should show the message that the data already exist. And if the table does not exist it should create the table and insert the data. Right?

Perumalkutti 8-Jan-13 8:54am

ya absolutely correct sir... help me sir

Mr. Mahesh Patel 8-Jan-13 8:39am

Please improve your question and try to specify that what do you want to do with this stored procedure.

Santhosh Kumar Jayaraman 8-Jan-13 8:58am

I believe

You want to check table already exists in the schema
if exists, then check whether values exists else insert.
If table is not there in schema, create and insert table, right?

check solution 1

Perumalkutti 8-Jan-13 9:02am

ya correct help me sir..

Santhosh Kumar Jayaraman 8-Jan-13 9:04am

check my solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santhosh Kumar Jayaraman · Answer 1 · 2013-01-08T02:57:00

I believe

You want to check table already exists in the schema
if exists, then check whether values exists else insert.
If table is not there in schema, create and insert table, right?

try this

SQL

Alter procedure VisitorDetails1
@name nvarchar(50),
@City nvarchar(100),
@Dept nvarchar(max),
@TableName nvarchar(50)
 
as
 
begin
	IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=@TableName))
	begin		
		Declare @set nvarchar(max)
		set @set='select 1 from '+@TableName+' where name='''+@name+''' and city='''+@City+''' and dept='''+@Dept+''''
		exec(@set)
		if (@@rowcount>0)
			Print 'Data already exists'
		else
		begin	
			set @set='insert into '+@TableName+' values('''+@name+''','''+@City+''','''+@Dept+''')' 
			exec(@set)
			Print 'Successfully inserted...'
	
		end		
	end
	else
	begin
 
		Declare @set1 nvarchar(max)
		set @set1='create table '+@TableName+' (Name nvarchar(50),City nvarchar(50),Dept nvarchar(50))'
		exec(@set1)
		set @set='insert into '+@TableName+' values('''+@name+''','''+@City+''','''+@Dept+''')'
		exec(@set)
		print 'Table is Not there So Created Successfully and Inserted Datas...'
	end
end
GO

ASP.Net Stored Procedure

1 solution

Solution 1

Add your solution here

Preview 0

ASP.Net Stored Procedure

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us