今天,客服让我实现一个功能,根据不同游戏显示不同电话和邮箱的问题。由于,这是个小程序,用数据库或配置文件,都挺累人的。我打算在js 中直接写死。打开客服给我的excel文档。乖乖,八十多个游戏,一个一个写 case 分支,非出人命不可。因此,想借助isqlw用异类查询帮我实现,考虑片刻,写出下面代码,呵呵,终于不用一个一个写case分支了。
EXEC sp_configure 'show advanced option', '1'
RECONFIGURE
EXEC sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE
select cast([应用编码] as varchar(3)) as id,cast([电话] as varchar(50) ) as tel ,cast([邮箱] as varchar(50) ) as mail into temp from
openrowset('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;DATABASE=d:/aa.xls',sheet1$)
where len([电话])>0 and len([邮箱])> 0
declare @id varchar(3)
declare @tel varchar(50)
declare @mail varchar(50)
declare @str varchar(7000)
declare my_cursor cursor for select * from temp
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @id, @tel,@mail
set @str=''
WHILE @@FETCH_STATUS = 0
BEGIN
set @str = @str + 'case '+ @id + ':' + char(13) + char(10) + ' str="'+ @tel + '<br/>'+ @mail + '";' + char(13) + char(10) + ' break;' + char(13) + char(10)
FETCH NEXT FROM my_cursor INTO @id, @tel,@mail
end
CLOSE my_cursor
DEALLOCATE my_cursor
drop table temp
print @str
EXEC sp_configure 'Ad Hoc Distributed Queries',0
RECONFIGURE
EXEC sp_configure 'show advanced option', '0'
RECONFIGURE