带通配符参数的存储过程 [英] stored procedure with wildcard parameters

问题描述

我有一张桌子

create table user (userId varchar(8) not null, userName varchar(8) not null)
   insert into user
     select 'NAME1','name1'
     union all
     select 'NAME2', 'name2'
     union all
     select 'NAME3','name3'

我对通配符参数使用了存储过程:

I've used stored procedure for wild card parameters as:

create procedure wildcard_name
@userName nchar(8)=  '%'
as
select * from user
where userName like @userName;

exec wildcard_name 'n%';

exec 语句没有给出任何结果，为什么?

the exec statement is not giving any result,why?

推荐答案

您是否再次尝试运行它?我怀疑 exec 调用现在是程序主体的一部分.怎么样:

Did you try running it again? I suspect the exec call is part of the body of your procedure now. How about:

ALTER PROCEDURE dbo.wildcard_name
  @userName NVARCHAR(8) = '%'
AS
BEGIN
  SET NOCOUNT ON;

  SELECT userId, userName
    FROM dbo.user
    WHERE userName LIKE @userName;
END
GO -- <-- this is important! It tells SSMS that your procedure has ended!

EXEC dbo.wildcard_name N'n%';

其他一些我不想提的建议:

Bunch of other suggestions I would be remiss to not mention:

• 在创建和调用对象时，您应该始终指定架构前缀.所以CREATE PROCEDURE dbo.wildcard_name、EXEC dbo.wildcard_name等
• 希望您的生产代码不使用 SELECT *.
• 强烈建议您使用 nvarchar 而不是 nchar 作为参数.
• 用 BEGIN/END 包裹你的过程体，不要害怕使用缩进来提高可读性.
• 您通常希望使用 SET NOCOUNT ON; 来防止 n 行受影响 消息干扰您的结果.
• NVARCHAR 参数应该有一个 N 前缀(虽然我很困惑为什么你首先在 varchar 和 nchar 之间交替- 这是两个班次，我预计为零).
• 根据排序规则(以及您是否希望搜索区分大小写)，您可能需要使用 COLLATE 子句更改 where 子句.

• You should always specify the schema prefix when creating and calling objects. So CREATE PROCEDURE dbo.wildcard_name, EXEC dbo.wildcard_name, etc.
• Hopefully your production code does not use SELECT *.
• Highly recommend using nvarchar instead of nchar for your parameter.
• Wrap your procedure body with BEGIN / END and don't be afraid to use indenting to make it much more readable.
• You'll usually want to use SET NOCOUNT ON; to prevent n row(s) affected messages from interfering with your results.
• NVARCHAR parameters should have an N prefix (though I'm confused why you're alternating between varchar and nchar in the first place - this is two shifts where I'd expect zero).
• Depending on the collation (and whether you want the search to be case sensitive), you may need to change your where clause using the COLLATE clause.

编辑这对我来说似乎很好用，所以请解释你在做什么不同(没有工作"仍然意味着空结果，还是其他什么?):

EDIT this seems to work just fine for me, so please explain what you are doing differently (and does "did not work" still mean empty result, or something else?):

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