ASP.Net存储过程 [英] ASP.Net Stored Procedure

问题描述

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）

--------------



---- ------------

以上是我的存储过程..



1.实际上我需要检查如果表存在或不存在。如果它不应该动态创建表，同时在该表中插入值。

2.如果表存在意味着只插入值就足够了。 />
3.我们检查一个或多个列值是否存在。如果存在，它应该显示''Datas Already Exists''其他值应插入该表中。





请帮助我先生.. 。

预付谢谢..

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

推荐答案

我相信



你想检查模式中已经存在的表

如果存在，然后检查值是否存在，否则插入。

如果表中没有模式，则创建并插入表，对吗？



试试这个

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

这篇关于ASP.Net存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！