USE [1112b]
GO
/****** Object:  StoredProcedure [dbo].[xiaoxuebiaozhunfen]    Script Date: 07/25/2012 15:16:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[xiaoxuebiaozhunfen]
(@in_nianji nvarchar(10),@in_kemu nvarchar(10))
as

IF object_id('[tempdb].[dbo].#biaozhunfen') IS NOT NULL --判断临时表#biaozhunfen是否存在,存在则删除
begin
drop table #biaozhunfen
end




--这个大段是生成临时表
declare @QueryString nvarchar(500)
declare @paramstring nvarchar(500)
declare @input_kemu nvarchar(10)
declare @input_nianji nvarchar(10)
set @paramstring='@kemu nvarchar(10),@nianji nvarchar(10)' --设置动态语句中参数的定义的字符串,多个参数用","隔开
set @input_kemu =@in_kemu  --设置需传入动态语句的参数的值为@kemu
set @input_nianji=@in_nianji   --设置需传入动态语句的参数的值为@nianji

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 年级 = @nianji)
  into #biaozhunfen  FROM scores_xx  where 年级 = @nianji'

exec sp_executesql @QueryString,@paramstring,@kemu=@input_kemu,@nianji=@input_nianji --请注意参数的顺序
--生成临时表结束

--在临时表中执行查询
declare @QueryString2 nvarchar(500)
declare @paramstring2 nvarchar(500)
declare @input_kemu2 nvarchar(10)
set @QueryString2 = 'SELECT TOP 1 @kemu 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 @input_kemu2 =@in_kemu  --设置需传入动态语句的参数的值为@kemu

exec sp_executesql @QueryString,@paramstring,@kemu=@input_kemu2 --请注意参数的顺序

(1505 行受影响)
消息 8178，级别 16，状态 1，第 0 行
参数化查询 '(@kemu nvarchar(10),@nianji nvarchar(10))SELECT @kemu as 科目, 
 ' 需要参数 '@nianji'，但未提供该参数。

(1 行受影响)