存储过程中的ASP.NET Sql Server [英] ASP.NET Sql Server In Stored Procedure

问题描述

Alter procedure VisitorDetails1
@name nvarchar(50),
@City nvarchar(100),
@Dept nvarchar(max),
@TableName nvarchar(50)
as

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=@TableName)) 
--IF db_id('@TabName') IS NOT NULL
--IF OBJECT_ID('@TabName','U') is not null
begin

Declare @set nvarchar(50)
set @set='insert into '+@TableName+'(Name,City,Dept) values(@name,@City,@Dept)'

--set @set='insert into '+@TableName+' values(@name,@City,@Dept)'

exec(@set)
Print 'Success'
end
else
begin
print 'Table is Not there'
end

我的结果是在Exectuing之后：

My Result IS After Exectuing:

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@name".
Success..

请帮助我我需要做什么..

提前谢谢。

Please Help Me What I need to Do..
Advance Thanks.

推荐答案

Alter procedure VisitorDetails1
@name nvarchar(50),
@City nvarchar(100),
@Dept nvarchar(max),
@TableName nvarchar(50)
as
 
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=@TableName)) 
--IF db_id('@TabName') IS NOT NULL
--IF OBJECT_ID('@TabName','U') is not null
begin
 
Declare @set nvarchar(50)
set @set='insert into '+@TableName+'(Name,City,Dept) values(''' + @name + ''',''' + @City + ''',''' + @Dept + ''')'
 
exec(@set)
Print 'Success'
end
else
begin
print 'Table is Not there'
end

快乐编码！

：）

Happy Coding!
:)

这不起作用

Exec语句在变量不可用的不同上下文中执行。



而不是将值连接到字符串和exectuee



for exgs



声明@str nVarchar（50）



set @str =''插入Tb1值（''+ @r +' '）''

exec（@str）

This will not work
Exec statement execute in different context where the variables are not available.

instead concatenate the values to the string and exectuee

for exgs

Declare @str nVarchar(50)

set @str =''Insert into Tb1 values('' + @r + '')''
exec(@str)

更改此

set @set='insert into '+@TableName+'(Name,City,Dept) values(@name,@City,@Dept)'

改为

to

set @set='insert into '+@TableName+'(Name,City,Dept) values('+@name+','+@City+','+@Dept+')'

根据您的要求更改上面的sql语句。您需要将字符串包装在撇号中。

Change the above sql statement as per your requirement. You will need to wrap your string in apostrophes.

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