ALTER PROCEDURE [dbo].[uspGetUsers]
(
@userName NVARCHAR(40), --用户名称
@sex TINYINT, --性别(0 : 全部, 1 : 男, 2 : 女)
@sortField NVARCHAR(255), --排序字段
@sortOrder NVARCHAR(4), --排序顺序
@pageIndex INT, --待读取的页索引
@pageSize INT, --每页显示的记录数
@recordCount INT OUTPUT --总记录数
)
AS
BEGIN
DECLARE @sqlRC NVARCHAR(4000) --RecordCount SQL
DECLARE @sqlRS NVARCHAR(4000) --ResultSet SQL
SET @sqlRC =
'SELECT @recordCount = Count(*)
FROM Account_Users
WHERE Username LIKE ''%'' + REPLACE(''' + @userName + ''', ''%'', ''/%'') + ''%'' ESCAPE ''/'''
IF @sex <> 0
BEGIN
SET @sqlRC =
@sqlRC + ' AND Sex = ' + CONVERT(CHAR(1), @sex)
END
EXEC sp_executesql @sqlRC, N'@recordCount INT OUTPUT', @recordCount OUTPUT SET @sqlRS =
'SELECT SerialNumber,
LoginID,
Username,
Sex,
Birthday
FROM
(
SELECT LoginID,
Username,
CASE Sex
WHEN 1 THEN ''男''
WHEN 2 THEN ''女''
END AS Sex,
CONVERT(NVARCHAR(10), Birthday, 120) AS Birthday,
ROW_NUMBER() OVER (ORDER BY ' + @sortField + ' ' + @sortOrder + ') AS SerialNumber
FROM Account_Users
WHERE Username LIKE ''%'' + REPLACE(''' + @userName + ''', ''%'', ''/%'') + ''%'' ESCAPE ''/'''
IF @sex <> 0
BEGIN
SET @sqlRS =
@sqlRS + ' AND Sex = ' + CONVERT(CHAR(1), @sex)
END SET @sqlRS =
@sqlRS +
') AS T
WHERE T.SerialNumber > ' + CONVERT(NVARCHAR(100), (@pageIndex - 1) * @pageSize) +
' AND T.SerialNumber <= ' + CONVERT(NVARCHAR(100), @pageIndex * @PageSize)
EXEC (@sqlRS)
END
GOSET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
求一下部分解释IF @sex <> 0
BEGIN
SET @sqlRC =
@sqlRC + ' AND Sex = ' + CONVERT(CHAR(1), @sex)
END
EXEC sp_executesql @sqlRC, N'@recordCount INT OUTPUT', @recordCount OUTPUTSET @sqlRS =
这几个的解释,先谢谢各位了
(
@userName NVARCHAR(40), --用户名称
@sex TINYINT, --性别(0 : 全部, 1 : 男, 2 : 女)
@sortField NVARCHAR(255), --排序字段
@sortOrder NVARCHAR(4), --排序顺序
@pageIndex INT, --待读取的页索引
@pageSize INT, --每页显示的记录数
@recordCount INT OUTPUT --总记录数
)
AS
BEGIN
DECLARE @sqlRC NVARCHAR(4000) --RecordCount SQL
DECLARE @sqlRS NVARCHAR(4000) --ResultSet SQL
SET @sqlRC =
'SELECT @recordCount = Count(*)
FROM Account_Users
WHERE Username LIKE ''%'' + REPLACE(''' + @userName + ''', ''%'', ''/%'') + ''%'' ESCAPE ''/'''
IF @sex <> 0
BEGIN
SET @sqlRC =
@sqlRC + ' AND Sex = ' + CONVERT(CHAR(1), @sex)
END
EXEC sp_executesql @sqlRC, N'@recordCount INT OUTPUT', @recordCount OUTPUT SET @sqlRS =
'SELECT SerialNumber,
LoginID,
Username,
Sex,
Birthday
FROM
(
SELECT LoginID,
Username,
CASE Sex
WHEN 1 THEN ''男''
WHEN 2 THEN ''女''
END AS Sex,
CONVERT(NVARCHAR(10), Birthday, 120) AS Birthday,
ROW_NUMBER() OVER (ORDER BY ' + @sortField + ' ' + @sortOrder + ') AS SerialNumber
FROM Account_Users
WHERE Username LIKE ''%'' + REPLACE(''' + @userName + ''', ''%'', ''/%'') + ''%'' ESCAPE ''/'''
IF @sex <> 0
BEGIN
SET @sqlRS =
@sqlRS + ' AND Sex = ' + CONVERT(CHAR(1), @sex)
END SET @sqlRS =
@sqlRS +
') AS T
WHERE T.SerialNumber > ' + CONVERT(NVARCHAR(100), (@pageIndex - 1) * @pageSize) +
' AND T.SerialNumber <= ' + CONVERT(NVARCHAR(100), @pageIndex * @PageSize)
EXEC (@sqlRS)
END
GOSET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
求一下部分解释IF @sex <> 0
BEGIN
SET @sqlRC =
@sqlRC + ' AND Sex = ' + CONVERT(CHAR(1), @sex)
END
EXEC sp_executesql @sqlRC, N'@recordCount INT OUTPUT', @recordCount OUTPUTSET @sqlRS =
这几个的解释,先谢谢各位了
-------
把字符串赋值给变量@sqlRS
IF @sex <> 0
BEGIN
SET @sqlRC =
@sqlRC + ' AND Sex = ' + CONVERT(CHAR(1), @sex)
END
-----------------
如果变量@sex不等于0(即不是所有人),则加上性别的条件判断
EXEC sp_executesql @sqlRC, N'@recordCount INT OUTPUT', @recordCount OUTPUT
--------------
执行动态SQL语句,输出符合条件的记录数,数值保存在变量@recordCount里
declare @i int,@con int,@s nvarchar(1000)
set @i=5
select @s='select @con=count(1) from sysobjects where ID>@i'
exec sp_executesql @s,N'@con int output,@i int',@con output ,@i
select @con
相当于解释这个sql语句段,谢谢啦
http://blog.csdn.net/xys_777/archive/2010/06/24/5690909.aspx
回答:网上不是说EXEC和 sp_executesql可以替换么,这里怎么连用啦,请教 EXEC可以执行动态sql语句、存储过程或系统存储过程。sp_executesql是一个系统存储过程,起到的作用是参数替换功能。所谓的参数替换功能是这样的,假设我有一个product表,表中有一个字段是proID,我写了如下的动态sql语句要获取传入id的产品信息,本来proID是int类型,但我写字符串的时候不指定类型,等到执行的时候再具体指定传入参数的类型,方法看红色字体的部分
declare @str nvarchar(1000)
set @str=N'select * from product where proID=@pID'
exec sp_executesql @str,N'@pID INT',3
希望对你有帮助