SQL临时表

来源：互联网发布：暗黑钻油井升级数据编辑：程序博客网时间：2024/06/05 18:00

1、

调用：(临时表、调用存储过程)

create table #5( ID int, ProjectName varchar(200), ContractNumber varchar(100),)insert #5 EXEC [USP_Product_GetPaged2]   '','ID  DESC',0,10select *  from #5

存储过程：

print @sql ：打印消息可在SQL结果窗口-消息里查看：

USE [ZKWebForm121920170831(删减版)]GO/****** Object:  StoredProcedure [dbo].[USP_Product_GetPaged2]    Script Date: 09/28/2017 11:01:53 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- Batch submitted through debugger: SQLQuery2.sql|7|0|C:\Users\JM\AppData\Local\Temp\~vs60C9.sqlALTER PROCEDURE [dbo].[USP_Product_GetPaged2]    @WhereClause VARCHAR (2000),    @OrderBy VARCHAR (2000),    @PageIndex INT,    @PageSize INTAS    BEGIN        DECLARE @PageLowerBound INT, @PageUpperBound INT        SET @PageLowerBound = @PageSize * @PageIndex        SET @PageUpperBound = @PageLowerBound + @PageSize        CREATE TABLE #PageIndex        (            [IndexID] INT IDENTITY (1, 1) NOT NULL,            [ID] INT         )        DECLARE @SQL AS NVARCHAR(4000)        SET @SQL = 'INSERT INTO #PageIndex ([ID])'        SET @SQL = @SQL + ' SELECT'        IF @PageSize > 0            SET @SQL = @SQL + ' TOP ' + CONVERT(NVARCHAR, @PageUpperBound)        SET @SQL = @SQL + ' [ID]'        SET @SQL = @SQL + ' FROM [dbo].[LbtProjectInfo]'        IF LEN(@WhereClause) > 0            SET @SQL = @SQL + ' WHERE ' + @WhereClause        IF LEN(@OrderBy) > 0            SET @SQL = @SQL + ' ORDER BY ' + @OrderByprint @sql        EXEC (@SQL)        SELECT            TempTable.[ID],             TempTable.[ProjectName],             TempTable.[ContractNumber]        FROM            [dbo].[LbtProjectInfo] TempTable        INNER JOIN            #PageIndex PageIndex        ON            TempTable.[ID] = PageIndex.[ID]        WHERE            PageIndex.IndexID > @PageLowerBound        AND            PageIndex.IndexID <= @PageUpperBound        ORDER BY            PageIndex.IndexID---       查询项目信息的总条数--        SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'--        SET @SQL = @SQL + ' FROM [dbo].[LbtProjectInfo]'--        IF LEN(@WhereClause) > 0--        SET @SQL = @SQL + ' WHERE ' + @WhereClause---        打印sql语句--         print @sql--         EXEC (@SQL)       END

阅读全文

0 0