http://topic.csdn.net/t/20040629/23/3132813.html
DECLARE @cursor INT
DECLARE @rowcount INT
EXEC sp_cursoropen @cursor OUTPUT, N'SELECT * FROM customers where cust_id=@id', 2, 8193, @rowcount output, N'@id', @id=10
EXEC sp_cursorclose @cursor
这个@id如何传入?
DECLARE @cursor INT
DECLARE @rowcount INT
EXEC sp_cursoropen @cursor OUTPUT, N'SELECT * FROM customers where cust_id=@id', 2, 8193, @rowcount output, N'@id', @id=10
EXEC sp_cursorclose @cursor
这个@id如何传入?
N'SELECT * FROM customers where cust_id='+@id+'',
CREATE procedure sp_SplitPage(
@Tables varchar( 1000 ) = ' ', --表名称或级联名称
@PrimaryKey varchar(100)= ' ', --主键名称
@Sort varchar(200)= ' ', --排序列,含ASC/DESC
@CurrentPage int = 1, --当前页码
@PageSize int = 10, --每页显示记录数
@Fields varchar(1000) =' ', --读取的字段列表
@Filter varchar(1000)= ' ', --查询条件
@Group varchar(1000) =' ' --聚合查询信息
)
AS
/*存储查询字符串*/
DECLARE @strSQL nvarchar( 2048 )
set @strSQL = ' '/*存储查询条件*/
DECLARE @strFilters nvarchar( 1000 )
set @strFilters = ' '
/*存储排序字段*/
DECLARE @strSort nvarchar( 300 )
set @strSort = ' '
/*存储聚合查询*/
DECLARE @strGroup nvarchar( 1100 )
set @strGroup = ' '
if( @Filter IS NOT NULL AND @Filter != '' )
BEGIN
if( @CurrentPage = 1 )
set @strFilters = ' WHERE ( ' + @Filter + ' ) '
ELSE
set @strFilters = ' ( ' + @Filter + ' ) '
END
if( @Sort IS NOT NULL AND @Sort != '' )
BEGIN
set @strSort = ' ORDER BY ' + @Sort
ENDif( @Group IS not null AND @Group != '' )
BEGIN
set @strGroup = ' Group By ' + @Group
END
ELSE
BEGIN
set @strGroup = ' '
END/*纠正查询字段信息*/
if( @Fields IS NULL OR @Fields = '' )
set @Fields = ' * '/*存储每页提取的记录数*/
DECLARE @strTop nvarchar( 10 )
set @strTOP = Convert( nvarchar(4),@PageSize )print @strTOPprint @strGroup/*如果是第一页,则直接提取*/
if( @CurrentPage = 1 )
BEGIN
set @strSQL = 'SELECT Top ' +@strTop + ' ' + @Fields + ' FROM ' + @Tables + @strFilters + @strSort + @strGroup
END
ELSE
BEGIN
set @strSQL = 'SELECT Top ' + convert( nvarchar(4),@PageSize ) + ' ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strFilters + ' AND (' + @PrimaryKey + ' NOT IN ( SELECT TOP ' + Convert( nvarchar(4),( ( @CurrentPage - 1 ) * @PageSize ) ) + ' ' + @PrimaryKey + ' FROM ' + @Tables + ' WHERE ' + @strFilters + @strSort + ' ) ) ' + @strSort
ENDprint @strSQL
/*执行查询*/
exec sp_executesql @strSQL
GO
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);/* Build the SQL string one time.*/
SET @SQLString =
N'SELECT EmployeeID, NationalIDNumber, Title, ManagerID
FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID = @ManagerID';
SET @ParmDefinition = N'@ManagerID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@ManagerID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@ManagerID = @IntVariable;
declare @id nvarchar(15),@sql nvarchar(100)
set @id='1'
set @sql=' SELECT * FROM customers where cust_id='''+@id+''''
这样写可以的
这样看看
DECLARE @cursor INT
DECLARE @rowcount INT
DECLARE @ID INT
EXEC sp_cursoropen @cursor OUTPUT, N'SELECT * FROM customers where cust_id='+@id+'', 2, 8193, @rowcount output, N'+@id+', @id=10
EXEC sp_cursorclose @cursor
这样看看
DECLARE @cursor INT
DECLARE @rowcount INT
DECLARE @ID INT
EXEC sp_cursoropen @cursor OUTPUT, N'SELECT * FROM customers where cust_id=''+@id+''', 2, 8193, @rowcount output, N''+@id+'', @id=10
EXEC sp_cursorclose @cursor