存储过程里临时表可能出错,清指教
下面是我写的一个存储过程,用于一个ASP.NET网站。功能是把两个表的内容合起来,写到一个临时表中,统一输出结果,最后删除临时表。
一般情况运行正常,但有时会有出错信息,估计是临时表#myTempTable的问题。请问高手,有没有好的解决方案?谢谢!
ALTER PROCEDURE [dbo].[spTeacherNoticesByYear]
@year integer,
@OrderByClause varchar(50) = 'ORDER BY X_Date DESC'
AS
BEGIN
SET NOCOUNT ON;
SELECT * INTO #myTempTable FROM (
SELECT X_Type=99, N_ID AS X_ID, N_Title AS X_Title, N_Date AS X_Date FROM tblTeacherNotices WHERE YEAR(N_Date)=@year
UNION ALL
SELECT X_Type=1, D_ID AS X_ID, D_Title AS X_Title, D_Date AS X_Date FROM tblTeacherDocs WHERE YEAR(D_Date)=@year
) AS temp
-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)
SELECT @SQLStatement = 'SELECT * FROM #myTempTable ' + @OrderByClause
EXEC(@SQLStatement)
drop table #myTempTable
END