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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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