I have a test table named tblTest with two columns: name (nvarchar), age (tinyint), with values: 'My name', 10
我有一个名为tblTest的测试表,有两列:name(nvarchar),age(tinyint),值为:'my name',10
Then, I create the following procedure:
然后,我创建以下过程:
create procedure procTest @n nvarchar as
select * from tblTest where name=@n
When I run this procedure, it return empty result:
当我运行此过程时,它返回空结果:
exec procTest @n='My name'
But when I change procedure to
但是当我改变程序时
alter procedure procTest @n tinyint as
select * from tblTest where age=@n
and run
exec procTest @n=10
It return one row
它返回一行
So, what happen here? Do I miss anything?
那么,这里发生了什么?我想念什么吗?
1 个解决方案
#1
1
Add a length spec to your nvarchar in the stored procedure:
在存储过程中向nvarchar添加长度规范:
create procedure procTest (@n nvarchar(50)) as
select * from tblTest where name=@n
Copying it to a local variable will improve performance, and is generally good practice in stored procedures (see SQL Server: Query fast, but slow from procedure for more information) - as is using BEGIN and END statements:
将其复制到本地变量将提高性能,并且通常是存储过程中的良好实践(请参阅SQL Server:快速查询,但程序运行缓慢以获取更多信息) - 就像使用BEGIN和END语句一样:
create procedure procTest (@n nvarchar(50)) as
BEGIN
DECLARE @name nvarchar(50) = @n;
select * from tblTest where name=@name
END
#1
1
Add a length spec to your nvarchar in the stored procedure:
在存储过程中向nvarchar添加长度规范:
create procedure procTest (@n nvarchar(50)) as
select * from tblTest where name=@n
Copying it to a local variable will improve performance, and is generally good practice in stored procedures (see SQL Server: Query fast, but slow from procedure for more information) - as is using BEGIN and END statements:
将其复制到本地变量将提高性能,并且通常是存储过程中的良好实践(请参阅SQL Server:快速查询,但程序运行缓慢以获取更多信息) - 就像使用BEGIN和END语句一样:
create procedure procTest (@n nvarchar(50)) as
BEGIN
DECLARE @name nvarchar(50) = @n;
select * from tblTest where name=@name
END