CREATE PROCEDURE G_GetExam
@IdList VarChar(4000),
@TypeFlag Char(1),
--@Kindid Char(10),
@Group Nvarchar(4000)
-- @Class NVarChar(10),
-- @PostId NVarChar(1000)
AS
IF EXISTS (SELECT * FROM TEMPDB..SYSOBJECTS WHERE ID = OBJECT_ID(N'[TEMPDB]..[##TMP3]')) DROP TABLE ##TMP3
--DECLARE @Id NVarChar(32)
DECLARE @str NVarChar(2000)
DECLARE @IDX INT SET @IDX = 0 --DECLARE CUR CURSOR FOR
--SELECT * FROM DBO.FUN_SplitString(@PostId) --OPEN CUR
--FETCH NEXT FROM CUR INTO @Id
--WHILE (@@FETCH_STATUS <> -1)
--BEGIN
IF (@IDX = 0)
BEGIN
SET @str='SELECT * INTO ##TMP3 FROM #TMP2 '
END
ELSE BEGIN
SET @str='SELECT * FROM #TMP2 UNION SELECT * FROM ##TMP3'
END
DECLARE @strSQL NVarChar(4000)
DECLARE @strFilter NVarChar(4000) SET @strFilter = '' IF ISNULL(@Group,'') <> '' SET @strFilter = 'AND CHARINDEX(cast(GROUP_ID as varchar) ,'''+ @Group +''') > 0'
--IF ISNULL(@Class,'') <> '' SET @strFilter = @strFilter + ' AND CHARINDEX('''+ @Class +''',CLASS_ID) > 0'
--IF ISNULL(@PostId,'') <> '' SET @strFilter = @strFilter+ ' AND CHARINDEX('''+ @Id +''', POST) > 0' SET @strSQL = '
SELECT
ROWID = IDENTITY(INT, 1, 1),
ID*1,
QUESTION,
ANSWER
INTO #TMP1
FROM EXAM
WHERE Type = ''' + @TypeFlag + '''
'+ @strFilter +' SELECT
ROWID = IDENTITY(INT, 1, 1),
ID,
QUESTION,
ANSWER
INTO #TMP2
FROM #TMP1' + CASE WHEN @IdList <> '' THEN ' WHERE CHARINDEX(''['' + LTRIM(str(ROWID)) + '']'', ''' + @IdList + ''') > 0' ELSE '' END + '
ORDER BY ROWID
'+@str+'
'
EXEC SP_EXECUTESQL @strSQL
SET @IDX = @IDX+1
-- FETCH NEXT FROM CUR INTO @Id
--END --CLOSE CUR
--DEALLOCATE CUR
--SELECT * FROM ##TMP3
GO
@IdList VarChar(4000),
@TypeFlag Char(1),
--@Kindid Char(10),
@Group Nvarchar(4000)
-- @Class NVarChar(10),
-- @PostId NVarChar(1000)
AS
IF EXISTS (SELECT * FROM TEMPDB..SYSOBJECTS WHERE ID = OBJECT_ID(N'[TEMPDB]..[##TMP3]')) DROP TABLE ##TMP3
--DECLARE @Id NVarChar(32)
DECLARE @str NVarChar(2000)
DECLARE @IDX INT SET @IDX = 0 --DECLARE CUR CURSOR FOR
--SELECT * FROM DBO.FUN_SplitString(@PostId) --OPEN CUR
--FETCH NEXT FROM CUR INTO @Id
--WHILE (@@FETCH_STATUS <> -1)
--BEGIN
IF (@IDX = 0)
BEGIN
SET @str='SELECT * INTO ##TMP3 FROM #TMP2 '
END
ELSE BEGIN
SET @str='SELECT * FROM #TMP2 UNION SELECT * FROM ##TMP3'
END
DECLARE @strSQL NVarChar(4000)
DECLARE @strFilter NVarChar(4000) SET @strFilter = '' IF ISNULL(@Group,'') <> '' SET @strFilter = 'AND CHARINDEX(cast(GROUP_ID as varchar) ,'''+ @Group +''') > 0'
--IF ISNULL(@Class,'') <> '' SET @strFilter = @strFilter + ' AND CHARINDEX('''+ @Class +''',CLASS_ID) > 0'
--IF ISNULL(@PostId,'') <> '' SET @strFilter = @strFilter+ ' AND CHARINDEX('''+ @Id +''', POST) > 0' SET @strSQL = '
SELECT
ROWID = IDENTITY(INT, 1, 1),
ID*1,
QUESTION,
ANSWER
INTO #TMP1
FROM EXAM
WHERE Type = ''' + @TypeFlag + '''
'+ @strFilter +' SELECT
ROWID = IDENTITY(INT, 1, 1),
ID,
QUESTION,
ANSWER
INTO #TMP2
FROM #TMP1' + CASE WHEN @IdList <> '' THEN ' WHERE CHARINDEX(''['' + LTRIM(str(ROWID)) + '']'', ''' + @IdList + ''') > 0' ELSE '' END + '
ORDER BY ROWID
'+@str+'
'
EXEC SP_EXECUTESQL @strSQL
SET @IDX = @IDX+1
-- FETCH NEXT FROM CUR INTO @Id
--END --CLOSE CUR
--DEALLOCATE CUR
--SELECT * FROM ##TMP3
GO
没有为第 2 列(属于 '#TMP1')指定列。
@IdList VarChar(4000),
@TypeFlag Char(1),
--@Kindid Char(10),
@Group Nvarchar(4000)
-- @Class NVarChar(10),
-- @PostId NVarChar(1000)
AS
IF EXISTS (SELECT * FROM TEMPDB..SYSOBJECTS WHERE ID = OBJECT_ID(N'[TEMPDB]..[##TMP3]')) DROP TABLE ##TMP3
--DECLARE @Id NVarChar(32)
DECLARE @str NVarChar(2000)
DECLARE @IDX INT SET @IDX = 0 --DECLARE CUR CURSOR FOR
--SELECT * FROM DBO.FUN_SplitString(@PostId) --OPEN CUR
--FETCH NEXT FROM CUR INTO @Id
--WHILE (@@FETCH_STATUS <> -1)
--BEGIN
IF (@IDX = 0)
BEGIN
SET @str='SELECT * INTO ##TMP3 FROM #TMP2 '
END
ELSE BEGIN
SET @str='SELECT * FROM #TMP2 UNION SELECT * FROM ##TMP3'
END
DECLARE @strSQL NVarChar(4000)
DECLARE @strFilter NVarChar(4000) SET @strFilter = '' IF ISNULL(@Group,'') <> '' SET @strFilter = 'AND CHARINDEX(cast(GROUP_ID as varchar) ,'''+ @Group +''') > 0'
--IF ISNULL(@Class,'') <> '' SET @strFilter = @strFilter + ' AND CHARINDEX('''+ @Class +''',CLASS_ID) > 0'
--IF ISNULL(@PostId,'') <> '' SET @strFilter = @strFilter+ ' AND CHARINDEX('''+ @Id +''', POST) > 0' SET @strSQL = '
SELECT
ROWID = IDENTITY(INT, 1, 1),
ID*1 ID,
QUESTION,
ANSWER
INTO #TMP1
FROM EXAM
WHERE Type = ''' + @TypeFlag + '''
'+ @strFilter +' SELECT
ROWID = IDENTITY(INT, 1, 1),
ID,
QUESTION,
ANSWER
INTO #TMP2
FROM #TMP1' + CASE WHEN @IdList <> '' THEN ' WHERE CHARINDEX(''['' + LTRIM(str(ROWID)) + '']'', ''' + @IdList + ''') > 0' ELSE '' END + '
ORDER BY ROWID
'+@str+'
'
EXEC SP_EXECUTESQL @strSQL
SET @IDX = @IDX+1
-- FETCH NEXT FROM CUR INTO @Id
--END --CLOSE CUR
--DEALLOCATE CUR
--SELECT * FROM ##TMP3
GO