帮忙看下问题:
存储过程:
CREATE PROCEDURE [GetCustomersDataPage]
@PageIndex INT,
@PageSize INT,
@StrWhere varchar(100),
@RecordCount INT = 0 OUT,
@PageCount INT=0 OUT
AS
DECLARE @SQLSTR1 NVARCHAR(1000)
SET @SQLSTR1=N'SELECT @RecordCount = COUNT(*) FROM Question'+@StrWhere+''
exec sp_executesql @SQLSTR1 ,N'@RecordCount INT OUTPUT',@RecordCount OUTPUT
select @RecordCount
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
DECLARE @SQLSTR NVARCHAR(1000)
IF @PageIndex = 0 OR @PageCount <= 1
SET @SQLSTR =N'SELECT TOP '+STR( @PageSize )+'Q_Id, Q_OrgId,Q_DateTime,Q_FBPerson,Q_FBPhone,Q_FBQuestion,Q_TorF,Q_AMorPM FROM Question '+ @StrWhere+' ORDER BY Q_Id DESC'
ELSE IF
@PageIndex = @PageCount - 1
SET @SQLSTR =N'SELECT * FROM ( SELECT TOP '+STR( @RecordCount - @PageSize * @PageIndex )+'Q_Id, Q_OrgId,Q_DateTime,Q_FBPerson,Q_FBPhone,Q_FBQuestion,Q_TorF,Q_AMorPM FROM Question '+ @StrWhere+' ORDER BY Q_Id ASC ) TempTable ORDER BY Q_Id DESC'
ELSE
SET @SQLSTR =N'SELECT TOP '+STR( @PageSize )+' * FROM ( SELECT TOP '+STR( @RecordCount - @PageSize * @PageIndex )+'Q_Id, Q_OrgId,Q_DateTime,Q_FBPerson,Q_FBPhone,Q_FBQuestion,Q_TorF,Q_AMorPM FROM Question '+ @StrWhere+' ORDER BY Q_Id ASC ) TempTable ORDER BY Q_Id DESC'
EXEC (@SQLSTR)
GO
执行
exec GetCustomersDataPage 0,10,‘’没有问题
但是如果想执行有条件应该怎么写?
exec GetCustomersDataPage 0,10,这里写什么?比如where Q_OrgId='L00797'
存储过程:
CREATE PROCEDURE [GetCustomersDataPage]
@PageIndex INT,
@PageSize INT,
@StrWhere varchar(100),
@RecordCount INT = 0 OUT,
@PageCount INT=0 OUT
AS
DECLARE @SQLSTR1 NVARCHAR(1000)
SET @SQLSTR1=N'SELECT @RecordCount = COUNT(*) FROM Question'+@StrWhere+''
exec sp_executesql @SQLSTR1 ,N'@RecordCount INT OUTPUT',@RecordCount OUTPUT
select @RecordCount
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
DECLARE @SQLSTR NVARCHAR(1000)
IF @PageIndex = 0 OR @PageCount <= 1
SET @SQLSTR =N'SELECT TOP '+STR( @PageSize )+'Q_Id, Q_OrgId,Q_DateTime,Q_FBPerson,Q_FBPhone,Q_FBQuestion,Q_TorF,Q_AMorPM FROM Question '+ @StrWhere+' ORDER BY Q_Id DESC'
ELSE IF
@PageIndex = @PageCount - 1
SET @SQLSTR =N'SELECT * FROM ( SELECT TOP '+STR( @RecordCount - @PageSize * @PageIndex )+'Q_Id, Q_OrgId,Q_DateTime,Q_FBPerson,Q_FBPhone,Q_FBQuestion,Q_TorF,Q_AMorPM FROM Question '+ @StrWhere+' ORDER BY Q_Id ASC ) TempTable ORDER BY Q_Id DESC'
ELSE
SET @SQLSTR =N'SELECT TOP '+STR( @PageSize )+' * FROM ( SELECT TOP '+STR( @RecordCount - @PageSize * @PageIndex )+'Q_Id, Q_OrgId,Q_DateTime,Q_FBPerson,Q_FBPhone,Q_FBQuestion,Q_TorF,Q_AMorPM FROM Question '+ @StrWhere+' ORDER BY Q_Id ASC ) TempTable ORDER BY Q_Id DESC'
EXEC (@SQLSTR)
GO
执行
exec GetCustomersDataPage 0,10,‘’没有问题
但是如果想执行有条件应该怎么写?
exec GetCustomersDataPage 0,10,这里写什么?比如where Q_OrgId='L00797'
我定义一个string aa;
取LBUnit.SelectedValue的值为L00797 要让aa='where Q_OrgId=''L00797'',该怎么写!
谢谢赐教!!!!
我定义一个string aa;
取LBUnit.SelectedValue的值为L00797 要让aa='where Q_OrgId=''L00797''',该怎么写!
谢谢赐教!!!!