22,209
社区成员
发帖
与我相关
我的任务
分享
确切的说应该是拼装字符串的问题
查询语句为:
SELECT date = '日期',
no = '编号',
project = '项目',
flag = '是否成交'
UNION ALL
SELECT date,
no,
project,
flag = CASE WHEN STATE = 'T' THEN '成交'
ELSE '未成交'
END
FROM testdb..vi_project_del
WHERE TIME BETWEEN '2011-02-10 00:00:00' AND '2011-02-10 23:59:59'
动态拼装如下:
DECLARE @sql NVARCHAR(max)
SET @sql='select date=''日期'',no=''编号'',project=''项目'',flag=''是否成交''
union all
select date,no,project,flag=case when state=''T'' then ''成交'' else ''未成交'' end
from testdb..vi_project_del where time between '''+@time+' 00:00:00'' and '''+@time+' 23:59:59'''
问题1:虽然拼装出来了sql不过还是不明白这里各处的字符剥取方式
问题2:不明白问题1,所以出现
exec ('exec MASTER..xp_cmdshell ''bcp "'+@sql+'" queryout "'+@filename+'" -c -Sdb -U"sa" -P"pwd"'')
引号不完整
DECLARE @sql NVARCHAR(max)
declare @time varchar(20)
set @time = '2011-01-11'
SET @sql='select date=''日期'',no=''编号'',project=''项目'',flag=''是否成交''
union all
select date,no,project,flag=case when state=''T'' then ''成交'' else ''未成交'' end
from testdb..vi_project_del where time between '''+@time+' 00:00:00'' and '''+@time+' 23:59:59'''
set @sql = 'exec MASTER..xp_cmdshell ''bcp "'+@sql+'" queryout " aa " -c -Sdb -U"sa" -P"pwd"'''
print @sql
/*
exec MASTER..xp_cmdshell 'bcp "select date='日期',no='编号',project='项目',flag='是否成交'
union all
select date,no,project,flag=case when state='T' then '成交' else '未成交' end
from testdb..vi_project_del where time between '2011-01-11 00:00:00' and '2011-01-11 23:59:59'" queryout " aa " -c -Sdb -U"sa" -P"pwd"'
*/
exec ('exec MASTER..xp_cmdshell ''bcp "'+@sql+'" queryout "'+@filename+'" -c -Sdb -U"sa" -P"pwd"'')
--看你最后那个括号都是红色的,说明拼的字符还没结束,最后再加个 '
exec ('exec MASTER..xp_cmdshell ''bcp "'+@sql+'" queryout "'+@filename+'" -c -Sdb -U"sa" -P"pwd"''')
exec ('exec MASTER..xp_cmdshell ''bcp "'+@sql+'" queryout "'+@filename+'" -c -Sdb -U"sa" -P"pwd"''')
单引号中的2个单引号表示一个单引号
--看不清楚的就print出来看看