我现在要得到' SDA2021[女歪头鞋]'中SDA2021部分或者是[]中部分请问该怎么做呢,代码的长度和名称的长度都不固定的
解决方案 »
- SQL 触发器
- 我电脑的名称是jjpy,在SQLSERVER服务管理器中可以见到4个服务器实例jjpy,jjpy\uh,jjpy\SQLEXPRESS、PROJECTWISE。请问,在程序中如何得到这4个服务器实例字符串。
- 如何检测数据表中已经存在的记录
- 求教MSQL Server查询数据耗时的问题,望大家指教
- sql语句因语句太长或者太复杂
- 关于ltrim和isnull组合使用的问题
- SQL2000 查询数据
- 数据复制报错
- SuperSocket 信息: (SpnRegister) : Error 1355。这个错误怎么解决?
- 定义表约束条件
- 计算时间差的函数返回null!
- sql2000中except语法错误
set @temp = 'SDA2021[女歪头鞋]'select substring(@temp,charindex('[',@temp)+1,charindex(']',@temp)-charindex('[',@temp)-1)女歪头鞋
set @s = 'SDA2021[女歪头鞋]'select left(@s,charindex('[',@s) -1 ),right(@s,len(@s) - charindex('[',@s) +1 )
-------------------------------------------------- --------------------------------------------------
SDA2021 [女歪头鞋]
set @s='SDA2021[女歪头鞋]'select left(@s,charindex('[',@s)-1) col1,
substring(@s,charindex('[',@s)+1,charindex(']',@s)-(charindex('[',@s)+1)) col2
/*
col1 col2
------------------------------ ------------------------------
SDA2021 女歪头鞋(1 行受影响)
*/
--go
--sp_helptext usp_hbbbalter procedure dbo.usp_hbbb
@rq_S varchar (10),
@rq_E varchar (10),
@spinfo varchar (50),
@tmptable varchar(1000)
as
declare
@str nvarchar (4000)
--检查数据是否存在,存在则删除该表。
exec('if exists(select * from tempdb.dbo.sysobjects where name='''+@tmptable+''')
drop table '+@tmptable)
set @str=
' select kchz.ckdm as khdm,kchz.kwdm,kchz.kwmc,spdm,case when kcsl is null then 0 else kcsl end as
kcsl,'+
' case when xssl is null then 0 else xssl end as xssl, case when xsje is null then 0 else xsje end as xsje
into '+@tmptable+'
from (select vw_ckjxcmx.ckdm,vw_ckjxcmx.kwdm as kwdm,kuwei.kwmc,spdm, sum(sl)as kcsl from vw_ckjxcmx left join kuwei on vw_ckjxcmx.kwdm=kuwei.kwdm where
vw_ckjxcmx.qrrq <='''+@rq_E+''' and qr=''1'' and spdm='''+left(@spinfo,charindex('[',@spinfo) -1 )+''' group by spdm,vw_ckjxcmx.ckdm,vw_ckjxcmx.kwdm,kuwei.kwmc ) as kchz left
JOIN
(select spdm as spdm2,dm2,sum(sl) as xssl,sum(je) as xsje from vw_lsxhmx where
rq >='''+@rq_S+''' and rq<= '''+@rq_E+''' and vw_lsxhmx.sh=''1'' group by dm2,spdm) AS lshz
ON kchz.ckdm = lshz.dm2 and kchz.spdm=lshz.spdm2'
print @str
exec(@str)
return
--exec usp_hbbb '2008-10-30','2008-10-30','SDA2021[女歪头鞋]','temp'
--drop table temp
--select * from temp order by spdm --select spdm+'['+spmc+']' from shangpin但是我这样执行后没有结果返回,请问是哪里错了吗
--drop table temp
--select * from temp order by spdm 你把temp删了后,还从里面查询数据?print @str的结果是什么
--use bserp
--go
--sp_helptext usp_hbbb
--use bserp
--go
--sp_helptext usp_hbbb
CREATE procedure dbo.usp_hbbb
@rq_S varchar (10),
@rq_E varchar (10),
@spinfo varchar (100),
@tmptable varchar(1000)
as
declare
@str nvarchar (4000)
--检查数据是否存在,存在则删除该表。
exec('if exists(select * from tempdb.dbo.sysobjects where name='''+@tmptable+''')
drop table '+@tmptable)
--if @spinfo<>'全部' set @spinfo=''
set @str=
' select kchz.ckdm as khdm,kchz.kwdm,kchz.kwmc,spdm,case when kcsl is null then 0 else kcsl end as
kcsl,'+
' case when xssl is null then 0 else xssl end as xssl, case when xsje is null then 0 else xsje end as xsje
into '+@tmptable+'
from (select vw_ckjxcmx.ckdm,vw_ckjxcmx.kwdm as kwdm,kuwei.kwmc,spdm, sum(sl)as kcsl from vw_ckjxcmx left join kuwei on vw_ckjxcmx.kwdm=kuwei.kwdm where
vw_ckjxcmx.qrrq <='''+@rq_E+''' and qr=''1'''
if @spinfo!='全部' --set @spinfo='%%'
set @str=@str+ 'and spdm = '''+substring(@spinfo,1,7)+'''
group by spdm,vw_ckjxcmx.ckdm,vw_ckjxcmx.kwdm,kuwei.kwmc ) as kchz left
JOIN
(select spdm as spdm2,dm2,sum(sl) as xssl,sum(je) as xsje from vw_lsxhmx where
rq >='''+@rq_S+''' and rq<= '''+@rq_E+''' and vw_lsxhmx.sh=''1'' group by dm2,spdm) AS lshz
ON kchz.ckdm = lshz.dm2 and kchz.spdm=lshz.spdm2'
print @str
exec(@str)
return
--
--exec usp_hbbb '2009-05-03','2009-05-03','SDA2021[女歪头鞋]','##temp'
--drop table temp
--select * from temp order by spdm
--select spdm+'['+spmc+']' from shangpin
当@spinfo='全部商品'时这个存储过程就没有@spinfo这个参数条件区也就没有'and spdm = '''+substring(@spinfo,1,7)+''' 这个存储过程该怎么写了 谢谢了