alter procedure P_test
(
@selectdate nvarchar(50),
@StrWhere nvarchar(500)
)
as
begin
select count(*) from salesinfo where Orgeh in (@StrWhere) and convert(nvarchar(7),cast(substring(salesDate,0,7)+'01' as datetime),121) = @selectdate
end;在查询分析器执行:
提示:指定的参数过多
exec P_test '2011-04','601001','601002'如何将类似于('601001','601002')作为一个参数对@StrWhere来赋值?
(
@selectdate nvarchar(50),
@StrWhere nvarchar(500)
)
as
begin
select count(*) from salesinfo where Orgeh in (@StrWhere) and convert(nvarchar(7),cast(substring(salesDate,0,7)+'01' as datetime),121) = @selectdate
end;在查询分析器执行:
提示:指定的参数过多
exec P_test '2011-04','601001','601002'如何将类似于('601001','601002')作为一个参数对@StrWhere来赋值?
(
@selectdate nvarchar(50),
@StrWhere nvarchar(500)
)
as
begin
select count(*) from salesinfo where charindex(Orgeh+',',@StrWhere+',')>0
and convert(nvarchar(7),cast(substring(salesDate,0,7)+'01' as datetime),121) = @selectdate
end;exec P_test '2011-04','601001,601002'
select count(*) from salesinfo where Orgeh in (@StrWhere) and convert(nvarchar(7),cast(substring(salesDate,0,7)+'01' as datetime),121) = @selectdate
参数必须放入到in条件内的.
您写得很规范,但还是离我想要差一点点.
select count(*) from salesinfo where Orgeh in (@StrWhere)我想要的结果:
例如:
select count(*) from salesinfo where Orgeh in ('601001','600002','601003')
也就是查询出条件在601001,601002,601003中总记录数,有可能in条件内的个数不止1个,可能有多个?
union
select '600001'
union
select '601003'
union
select '601004'
select * from #tempdeclare @parm nvarchar(50)
set @parm = '601001, 600001, 601003'select count(*) from #temp where charindex(orgeh, @parm, 1) > 0
returns @temp table(a varchar(100))--实现split功能 的函数
as
begin declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>''
insert @temp values(@SourceSql)
returnendselect count(*) from salesinfo where Orgeh in (select a from f_split(@StrWhere,',')) and convert(nvarchar(7),cast(substring(salesDate,0,7)+'01' as datetime),121) = @selectdate
set @StrWhere='601001'+','+'601002'
exec P_test '2011-04',@StrWhere
--有区别吗?
declare @StrWhere nvarchar(1000)
set @StrWhere='601001'+','+'601002'
print @StrWhere
set @StrWhere='601001,601002'
print @StrWhere
alter procedure P_test
(
@selectdate nvarchar(50),
@StrWhere nvarchar(500)
)
as
begin
select count(*) from salesinfo where charindex(Orgeh+',',@StrWhere+',')>0
and convert(nvarchar(7),cast(substring(salesDate,0,7)+'01' as datetime),121) = @selectdate
end;exec P_test '2011-04','601001,601002'您写的,'601001,601002'作为一个查询条件.
我想要是:select count(*) from salesinfo where Orgeh in('601001','601006','601007','601009',....,'60118')
里面条件是动态的.
区别:
而不是'601001,601002'作为一个整体参数,彼此无独立.
要的是'601001','601006','601007','601009',....,'60118' 作为一个整体参数,各自相互独立.
charindex(orgeh, '601001, 601007, .......') > 0
這兩個查詢的條件得出的結果應該是一樣的﹐除非orgeh的值會有'601001'和'60100101'這種情況﹐那么﹐第一個條件只會出來'601001'﹐第二個條件就會'601001'和'60100101'都會出來。
另外﹐不明白為什么一定要相互獨立呢?
你创建一个表A看一下,这两个SQL语句,结果是否一样.
A:
ORGEH Info
601001 aaa
601002 bbb
601003 ccc
601004 ddd
下面是SQL语句:
select * from A where orgeh in(601001,601003,601004)
go
select * from A where charindex(orgeh, 601001,601003,601004')>0查出的结果是不相同.
in条件,可以查出对应的信息
而charindex查不出任何信息.
怎么说结果相同.
select '601001' as orgeh, 'aaa' as info into #temp
union
select '601002', 'bbb'
union
select '601003', 'ccc'
union
select '601004', 'ddd'
select * from #tempdeclare @parm nvarchar(50)
set @parm = '601001, 600001, 601003'select * from #temp where orgeh in ('601001', '601002', '601003')
select * from #temp where charindex(orgeh, '601001,601002,601003', 1) > 0
你試一試看。
charindex,好像有点类似于like.
select * from #temp where '601001,601002,601003' like '%' + orgeh + '%'
条件是包含的关系,不是相似的关系.alter procedure P_test
(
@selectdate nvarchar(50),
@StrWhere nvarchar(500)
)
as
begin
select count(*) from salesinfo where Orgeh in (@StrWhere) and convert(nvarchar(7),cast(substring(salesDate,0,7)+'01' as datetime),121) = @selectdate
end;在查询分析器执行:
提示:指定的参数过多
exec P_test '2011-04','601001','601002'如何将('601001','601002','601002',.....,'601012')作为一个参数对@StrWhere来赋值,且每个小项都是独立的?
不知道邹建大哥,去那里了?
如f_table(@StrWhere nvarchar(500)
)
然后再修改存储过程为类似如下形式alter procedure P_test
(
@selectdate nvarchar(50),
@StrWhere nvarchar(500)
)
as
begin
select count(*) from salesinfo where Orgeh in (select Orgeh from dbo.f_table(@StrWhere)) and convert(nvarchar(7),cast(substring(salesDate,0,7)+'01' as datetime),121) = @selectdate
end;
create procedure P_test
@strWhere nvarchar(500)
as
begindeclare @sql nvarchar(1000)
set @sql = 'select * from salesinfo where StoreRkey in ('+@strWhere+')'exec sp_executesql @sql
end
调用:
exec P_test '''605004'',''60400107'',''60400109'''
在数据访问方法里:
//这个方法只写了个关键点,public void Dal(Ilist<string> partyNameList)
{
System.Collections.Generic.List<SqlDataRecord> partyName_list = new System.Collections.Generic.List<SqlDataRecord>(); SqlMetaData[] tvp_definition = { new SqlMetaData("n", SqlDbType.NVarChar, 250) }; foreach (string patentDisplayName in partyNameList)
{
SqlDataRecord rec = new SqlDataRecord(tvp_definition);
rec.SetString(0, patentDisplayName);
partyName_list.Add(rec);
}_SQLCommand.Parameters.Add("@PartyNameList", SqlDbType.Structured);
_SQLCommand.Parameters["@PartyNameList"].Direction = ParameterDirection.Input;
_SQLCommand.Parameters["@PartyNameList"].TypeName = "nvarchar_list_tblType";
_SQLCommand.Parameters["@PartyNameList"].Value = partyName_list;}alter procedure P_test
(
@selectdate nvarchar_list_tblType READONLY,
@StrWhere nvarchar(500)
)
as
begin
select count(*) from salesinfo where Orgeh in (@StrWhere) and convert(nvarchar(7),
cast(substring(salesDate,0,7)+'01' as datetime),121) IN @selectdate
end;