case when then 加顺序列

set @sql='select x.*,index2=identity(int,1,1)into #temp from #pr_tmd_wkpmx x '
print(@sql)
exec(@sql +' select * from #temp')

 

如果在上面@sql 中加条件的话，由于本身已经有单引号了，但是值有必须有‘’表示varchar

so for example

  set @col1='''a''';  //要用三个单引号

   set @sql ="select * from table where col1="+@col1

 

 

下面是一个游标和存储过程在一起的例子，其中包含了两种给列加序列的思路

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[pr_tmd_wkpmx] 
	-- Add the parameters for the stored procedure here
	@c_nf varchar(10),@c_yf varchar(10) ,@c_ssbm1 varchar(50),@c_km1 varchar(50),@c_yhlx1 varchar(50)
AS
create table #pr_tmd_wkpmx(
index1 int,
mc varchar(50),
tdh1 varchar(50),
mz varchar(50),
tddw numeric(18,2),
st numeric(18,2),
wkp numeric(18,2), 
sy numeric(18,2), 
rq1 datetime,
ssbm1 varchar(50),
km1 varchar(50),
yhlx1 varchar(50)
)
declare @index int,@mc varchar(50),@c_tdh varchar(50),@mz varchar(50),@tddw numeric(18,2), 
@st numeric(18,2), @wkp numeric(18,2), @sy numeric(18,2), @d_rq datetime,@c_ssbm2 varchar(50),@c_km2 varchar(50),@c_yhlx2 varchar(50),@sql varchar(500),@result int,@text1 varchar(50)
select @index =0;
  declare Cur cursor for 
  select (select c_khmc from yjmis.dbo.TB_YX_KHXX_DATA where  t.c_khmc = c_khid)khmc,c_tdh,
(select mc from yjmis.dbo.tb_yx_mzwhb_dd where c_pz=id)mz,n_tddw,n_byst,n_wkp,(n_tddw-n_byst)sy,d_tdrq ,c_ssbm,c_km,c_yhlx
from yjmis.dbo.TB_YX_YFYSWKPMLMX_DATA t
 where year(d_tdrq)=@c_nf and month(d_tdrq)=@c_yf  and c_ssbm = @c_ssbm1 and c_km = @c_km1 and c_yhlx=@c_yhlx1
  union all
 select '小计','','',sum(n_tddw),sum(n_byst),sum(n_wkp),sum(sy),null,'','','' from (
select (select c_khmc from yjmis.dbo.TB_YX_KHXX_DATA where  t.c_khmc = c_khid)khmc,c_tdh,
(select mc from yjmis.dbo.tb_yx_mzwhb_dd where c_pz=id)mz,n_tddw,n_byst,n_wkp,(n_tddw-n_byst)sy,d_tdrq ,c_ssbm,c_km,c_yhlx
from yjmis.dbo.TB_YX_YFYSWKPMLMX_DATA t
 where year(d_tdrq)=@c_nf and month(d_tdrq)=@c_yf and c_ssbm = @c_ssbm1 and c_km = @c_km1 and c_yhlx=@c_yhlx1)tx
open Cur
fetch next from Cur into @mc,@c_tdh,@mz,@tddw,@st,@wkp,@sy,@d_rq,@c_ssbm2,@c_km2,@c_yhlx2
while (@@fetch_status = 0)
BEGIN
   SELECT @index=@index+1  
   insert into #pr_tmd_wkpmx
   select @index,@mc,@c_tdh,@mz,@tddw,@st,@wkp,@sy,@d_rq ,@c_ssbm2,@c_km2,@c_yhlx2
fetch next from Cur into @mc,@c_tdh,@mz,@tddw,@st,@wkp,@sy,@d_rq,@c_ssbm2,@c_km2,@c_yhlx2
END
close Cur 
deallocate Cur
set @sql='select x.*,index2=identity(int,1,1)into #temp from #pr_tmd_wkpmx x '
print(@sql)
exec(@sql +' select * from #temp')