USE [1112b]
GO
declare @in_kemu nvarchar(10)
set @in_kemu = '语文'--这个大段是生成临时表
declare @QueryString nvarchar(500)declare @kemu nvarchar(10)
set @kemu =@in_kemu --设置需传入动态语句的参数的值为@kemu
set @QueryString ='SELECT '+@kemu+' as 科目,
[ROW_NUMBER] = ROW_NUMBER() OVER(ORDER BY '+@kemu+' DESC),
[DENSE_RANK] = DENSE_RANK() OVER(ORDER BY '+@kemu+' DESC),
[PERCENT] = (ROW_NUMBER() OVER(ORDER BY '+@kemu+' DESC))*1./(SELECT COUNT(1) FROM scores_xx where 年级 ='+'1'+')
into #biaozhunfen
FROM scores_xx
where 年级 = '+'1'exec (@QueryString) --请注意参数的顺序--生成临时表结束--在临时表中执行查询
declare @QueryString2 nvarchar(500)declare @kemu2 nvarchar(10)set @QueryString2 = 'SELECT TOP 1 '+@kemu2+' FROM
(
SELECT * FROM #biaozhunfen T1
WHERE NOT EXISTS
(SELECT 1 FROM #biaozhunfen WHERE [DENSE_RANK] = T1.[DENSE_RANK] AND [ROW_NUMBER] > T1.[ROW_NUMBER])
) AS A
ORDER BY ABS([PERCENT]-0.8)'
set @kemu2 =@in_kemu --设置需传入动态语句的参数的值为@kemuexec (@QueryString2) --请注意参数的顺序
GO
declare @in_kemu nvarchar(10)
set @in_kemu = '语文'--这个大段是生成临时表
declare @QueryString nvarchar(500)declare @kemu nvarchar(10)
set @kemu =@in_kemu --设置需传入动态语句的参数的值为@kemu
set @QueryString ='SELECT '+@kemu+' as 科目,
[ROW_NUMBER] = ROW_NUMBER() OVER(ORDER BY '+@kemu+' DESC),
[DENSE_RANK] = DENSE_RANK() OVER(ORDER BY '+@kemu+' DESC),
[PERCENT] = (ROW_NUMBER() OVER(ORDER BY '+@kemu+' DESC))*1./(SELECT COUNT(1) FROM scores_xx where 年级 ='+'1'+')
into #biaozhunfen
FROM scores_xx
where 年级 = '+'1'exec (@QueryString) --请注意参数的顺序--生成临时表结束--在临时表中执行查询
declare @QueryString2 nvarchar(500)declare @kemu2 nvarchar(10)set @QueryString2 = 'SELECT TOP 1 '+@kemu2+' FROM
(
SELECT * FROM #biaozhunfen T1
WHERE NOT EXISTS
(SELECT 1 FROM #biaozhunfen WHERE [DENSE_RANK] = T1.[DENSE_RANK] AND [ROW_NUMBER] > T1.[ROW_NUMBER])
) AS A
ORDER BY ABS([PERCENT]-0.8)'
set @kemu2 =@in_kemu --设置需传入动态语句的参数的值为@kemuexec (@QueryString2) --请注意参数的顺序
exec('select * from #abc123')运行结果:(55 行受影响)
消息 208,级别 16,状态 0,第 1 行
对象名 '#abc123' 无效。从上面的运行结果看出,调用exec执行,所产生的临时表的有效期只是在执行期间,exec执行完成后这个临时表自动释放,就不存在了,所以就导致第二exec语句执行报错,找不到对象名。
USE [1112b]
GO
declare @in_kemu nvarchar(10)
set @in_kemu = '语文'--这个大段是生成临时表
declare @QueryString nvarchar(500)
declare @kemu nvarchar(10)
set @kemu =@in_kemu --设置需传入动态语句的参数的值为@kemucreate table #biaozhunfen(科目 nvarchar(10),[ROW_NUMBER] int,[DENSE_RANK] int, [PERCENT] float)
set @QueryString ='SELECT '+@kemu+' ,
ROW_NUMBER() OVER(ORDER BY '+@kemu+' DESC),
DENSE_RANK() OVER(ORDER BY '+@kemu+' DESC),
(ROW_NUMBER() OVER(ORDER BY '+@kemu+' DESC))*1./(SELECT COUNT(1) FROM scores_xx where 年级 ='+'1'+')
FROM scores_xx
where 年级 = '+'1'
insert into #biaozhunfen
exec (@QueryString) --请注意参数的顺序--生成临时表结束--在临时表中执行查询
declare @QueryString2 nvarchar(500)declare @kemu2 nvarchar(10)set @QueryString2 = 'SELECT TOP 1 '+@kemu2+' FROM
(
SELECT * FROM #biaozhunfen T1
WHERE NOT EXISTS
(SELECT 1 FROM #biaozhunfen WHERE [DENSE_RANK] = T1.[DENSE_RANK] AND [ROW_NUMBER] > T1.[ROW_NUMBER])
) AS A
ORDER BY ABS([PERCENT]-0.8)'
set @kemu2 =@in_kemu --设置需传入动态语句的参数的值为@kemuexec (@QueryString2) --请注意参数的顺序
GO
declare @in_kemu nvarchar(10)
set @in_kemu = '语文'--这个大段是生成临时表
declare @QueryString nvarchar(500)declare @kemu nvarchar(10)
set @kemu =@in_kemu --设置需传入动态语句的参数的值为@kemu
set @QueryString ='SELECT '+@kemu+' as 科目,
[ROW_NUMBER] = ROW_NUMBER() OVER(ORDER BY '+@kemu+' DESC),
[DENSE_RANK] = DENSE_RANK() OVER(ORDER BY '+@kemu+' DESC),
[PERCENT] = (ROW_NUMBER() OVER(ORDER BY '+@kemu+' DESC))*1./(SELECT COUNT(1) FROM scores_xx where 年级 ='+'1'+')
into #biaozhunfen
FROM scores_xx
where 年级 = '+'1'exec (@QueryString) --请注意参数的顺序--生成临时表结束--在临时表中执行查询
declare @QueryString2 nvarchar(500)declare @kemu2 nvarchar(10)
/******放前面獲取參數值******/
set @kemu2 =@in_kemu --设置需传入动态语句的参数的值为@kemu/******置后******/
set @QueryString2 = 'SELECT TOP 1 '+@kemu2+' FROM
(
SELECT * FROM #biaozhunfen T1
WHERE NOT EXISTS
(SELECT 1 FROM #biaozhunfen WHERE [DENSE_RANK] = T1.[DENSE_RANK] AND [ROW_NUMBER] > T1.[ROW_NUMBER])
) AS A
ORDER BY ABS([PERCENT]-0.8)'exec (@QueryString2) --请注意参数的顺序
請看紅色部份
1、用#biaozhunfen作为临时表居然不行,必须得用tempdb.dbo.biaozhunfen
2、给@kemu2赋值语句要提前
这一句的问题,需要提前设置
下面是测试 你看看declare @in_kemu nvarchar(10)
set @in_kemu = '语文'
--在临时表中执行查询
declare @QueryString2 nvarchar(500)declare @kemu2 nvarchar(10)
--提前设置@kemu2 的值
set @kemu2 =@in_kemu --设置需传入动态语句的参数的值为@kemuset @QueryString2 = 'SELECT TOP 1 '+@kemu2+' FROM
(
SELECT * FROM #biaozhunfen T1
WHERE NOT EXISTS
(SELECT 1 FROM #biaozhunfen WHERE [DENSE_RANK] = T1.[DENSE_RANK] AND [ROW_NUMBER] > T1.[ROW_NUMBER])
) AS A
ORDER BY ABS([PERCENT]-0.8)'
--set @kemu2 =@in_kemu --设置需传入动态语句的参数的值为@kemu
PRINT (@QueryString2)
--exec (@QueryString2) --请注意参数的顺序
你可以提前定义你的这个临时表,然后在exec里面不用select ... into ,用insert into ...就可以了