Click here to Skip to main content
12,352,091 members (31,602 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: ASP.NET SQL-Server
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
Edited 8-Jan-13 2:57am
v4
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 J 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 J 8-Jan-13 9:04am
   
check my solution

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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
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
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160621.1 | Last Updated 8 Jan 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100