当前位置: 代码迷 >> ASP.NET >> 怎么写查询的存储过程
  详细解决方案

怎么写查询的存储过程

热度:9952   发布时间:2013-02-26 00:00:00.0
如何写查询的存储过程?
如何写查询的存储过程?   即当userName不为空时,把UserName拼写到where   条件后.(模糊查询)   自己写了,在Where   UserName   like   '   +   '% '   +   @userName   +   '% '
处报错.请大家帮忙看看.
CREATE   PROCEDURE   [dbo].[FindUser]  
--   Add   the   parameters   for   the   stored   procedure   here
@userName   varchar(50)
AS
BEGIN
--   SET   NOCOUNT   ON   added   to   prevent   extra   result   sets   from
--   interfering   with   SELECT   statements.
SET   NOCOUNT   ON;

              --   Insert   statements   for   procedure   here
DECLARE   @_SelectSql   nvarchar(1000)
SET   @_SelectSql   =   N 'SELECT   *   from   UserInfor '

IF   @userName   IS   not   null   AND   LEN(@userName)   >   0
BEGIN
SET   @_SelectSql   =   @_SelectSql   +   N '   Where   UserName   like   '   +   '% '   +   @userName   +   '% '
END

EXEC   sp_executesql   @_SelectSql
END

------解决方案--------------------------------------------------------
试一下: 一个 ' > > ' '
CREATE PROCEDURE [dbo].[FindUser]
-- Add the parameters for the stored procedure here
@userName varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
DECLARE @_SelectSql nvarchar(1000)
SET @_SelectSql = N 'SELECT * from UserInfor '

IF @userName IS not null AND LEN(@userName) > 0
BEGIN
SET @_SelectSql = @_SelectSql + N ' Where UserName like ' + ' '% ' + @userName + '% ' ' '
END

EXEC sp_executesql @_SelectSql
END

------解决方案--------------------------------------------------------
在存储过程中的拼接查询中, '要用 ' '来代替,比如 '0 ',要写成 ' ' '0 ' ' '
不知LZ明白了不?
  相关解决方案