单表的存储过程 可以 调用视图吗/望高手指点
单表的存储过程 可以 调用视图吗/
应大家要求/将代码贴出来/
存储过程如下
if exists (select * from dbo.sysobjects where id = object_id(N'[sp_Util_Page]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [sp_Util_Page]
GO
CREATE PROCEDURE sp_Util_Page
(
@iRecordCount int OUTPUT,
@iPageCurr int,
@iPageSize int,
@sPkey nvarchar(50),
@sField nvarchar(1000),
@sTable nvarchar(100),
@sCondition nvarchar(1000),
@sOrder nvarchar(100)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @sCond1 nvarchar(1000),@sCond2 nvarchar(1000)
DECLARE @iAsc int,@iDesc int,@iType tinyint
DECLARE @sTmp nvarchar(1000),@sOrderTmp nvarchar(1000),@sSQL nvarchar(4000)
IF LEN(@sCondition)>2
SELECT @sCond1=' WHERE '+@sCondition+' ', @sCond2=' WHERE '+@sCondition+' AND '
ELSE
BEGIN
SELECT @sCond1='', @sCond2=' WHERE '
END
IF LEN(@sOrder)<4
SELECT @iType=0, @sOrder=''
Else
BEGIN
SET @sOrderTmp=UPPER(@sOrder)
IF CHARINDEX(UPPER(@sPkey),@sOrderTmp)=1--存在主建
SELECT @iAsc=CHARINDEX('ASC',@sOrderTmp), @iDesc=CHARINDEX('DESC',@sOrderTmp)
ELSE
BEGIN
SELECT @iAsc=0, @iDesc=0
END
IF (@iAsc>0 and @iDesc=0) OR ((@iAsc>0 AND @iDesc>0) AND (@iAsc<@iDesc))
SELECT @iType=1, @sTmp='>(SELECT MAX('
ELSE IF (@iAsc=0 and @iDesc>0) OR ((@iAsc>0 AND @iDesc>0) AND (@iAsc>@iDesc))
SELECT @iType=1, @sTmp='<(SELECT MIN('
ELSE
BEGIN
SELECT @iType=0
END
SET @sOrder=' ORDER BY '+@sOrder
END
IF @iRecordCount<1
BEGIN
SET @sSQL='SELECT @iRecordCount=Count(0) FROM '+@sTable+@sCond1
EXEC sp_executesql @sSQL,N'@iRecordCount int OUT',@iRecordCount OUT
END
IF @iRecordCount<(@iPageCurr-1)*@iPageSize
SET @iPageCurr=CEILING(@iRecordCount/@iPageSize)
ELSE IF @iPageCurr<1
BEGIN
SET @iPageCurr=1
END
IF @iPageCurr=1
SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sCond1+@sOrder
ELSE
BEGIN
IF @iType=1
SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sCond2+@sPkey+@sTmp+@sPkey+') FROM (SELECT TOP '+CAST((@iPageCurr-1)*@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sCond1+@sOrder+') AS tbTemp)'+@sOrder
ELSE
SET @sSQL='SELECT '+@sField+' FROM '+@sTable+@sCond2+@sPkey+' IN (SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sCond2+@sPkey+' NOT IN(SELECT TOP '+CAST((@iPageCurr-1)*@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sCond1+@sOrder+')'+@sOrder+')'+@sOrder
END
--PRINT(@sSQL)
--RETURN(@iRecordCount)
EXEC(@sSQL)
END
GO
应用到单表中是没问题的
但想用他调用视图 不行 没数据
视图代码 (查询分析器中是有数据的)
SELECT dbo.Link.Content, dbo.Link.Title, dbo.Link.LinkTo, dbo.Link.ImagePath,
dbo.Link.SortID, dbo.LinkSort.ID, dbo.LinkSort.Title AS N_SortTitle,
dbo.Link.ID AS L_ID
FROM dbo.Link INNER JOIN
dbo.LinkSort ON dbo.Link.SortID = dbo.LinkSort.ID
我想可能是我的思路错了
可能要将procedure 改成多表的查询 然后再调用