日期:2014-05-17 浏览次数:20571 次
select * from EA01_1978
union
exec zjsj '10901'
alter proc zjsj --存储过程
@content varchar(30) --接收部门编号
as
declare @sql varchar(2000) --存储拼接的sql语句
declare @name1 varchar(2000)--接收拼接的字段
declare Test_Cursor Cursor scroll for --创建游标
select name from syscolumns where id=object_id(N'a')
open Test_Cursor
declare @name varchar(1000)
fetch next from Test_Cursor into @name
set @name1 = @name
while @@fetch_status = 0
begin
fetch next from Test_Cursor into @name
set @name1 = @name1 + ',' + @name --通过游标和循环将所有字段查询拼接出来
end
set @sql = 'select ' + @name1 + ' from b'
close Test_Cursor
deallocate Test_Cursor
if(@content like '109%')--根据部门拼接相应条件
begin
set @sql = @sql + ' where dept_code not like ''109%'' and (a01 like ''%在职%'' or a01 = ''试用人员'')'
end
else if (@content not like '109%')
begin
set @sql = @sql + ' where dept_code not like ''1%'''
end
exec (@sql)
go
if OBJECT_ID('tempdb..#temp') is not null
drop table #temp
select * into #temp
frm EA01_1978
where 1=0
insert into #temp
exec zjsj '10901'
select * from EA01_1978
union
select * from #temp
ALTER function [dbo].[ListToInt](@values nvarchar(MAX), @delimiter nvarchar(10))
returns @result table (value int)
as
begin
declare @v as nvarchar(MAX);
while charindex(@delimiter,@values) <> 0
begin
set @v = substring(@values,1,charindex(@delimiter,@val