34,838
社区成员




declare @icount int
declare @sql nvarchar(2000)
set @sql = 'select @icount=count(crmcustomerid) from ' + @tablename + ' where crmcustomerid = ' + cast(@pi_iCrmcustomerId as varchar)
exec sp_executesql @sql,N'@icount int output',@icount output
select @icount as a
if @icount > 0
...........
--测试数据准备
if exists (select 1 from sysobjects where id = object_id('test') and type = 'U')
drop table test
CREATE table test(
id int identity(1,1),
name nvarchar(10)
)
insert into test
select 'aaa' union all
select 'bbb' union all
select 'ccc'
--select COUNT(*) from test
--3
--创建存储过程
if exists (select 1 from sysobjects where id = object_id('printCount') and type = 'p')
drop procedure printCount
go
create procedure printCount
as
begin
declare @icount int
,@sql nvarchar(2000)
set @sql = 'select @icount=count(name) from test'
exec sp_executesql @sql,N'@icount int output',@icount output
if @icount > 0
print isnull(@icount,0)
end
go
exec printCount
--3
----清除测试相关数据
--drop procedure printCount
--drop table test
set @sql = 'select @icount=count(crmcustomerid) from ' + @tablename + ' where crmcustomerid = ' + cast(@pi_iCrmcustomerId as varchar)
exec sp_executesql @sql,N'@icount int output',@icount=@icount output
select @icount as a
if @icount > 0
--select @icount as b
begin --如果大于0,表存在则直接更新
set @sql = ''之前没有这一句,导致把上面的SQL也拼到下面来了,所以会报错
set @sql = @sql + 'update '+ @tablename + ' set Customername =''' + @ps_sCustomername +''''
set @sql = @sql + ',MobileNo =''' + @ps_sMobileNo + ''''
set @sql = @sql + ',Customerstatus=''' + @ps_sCustomerstatus + ''''
......
declare @icount int
declare @sql nvarchar(2000)
set @sql = 'select '+cast(@icount as varchar(10))+'=count(crmcustomerid) from ' + @tablename + ' where crmcustomerid = ' + cast(@pi_iCrmcustomerId as varchar)
exec sp_executesql @sql,N'@icount int output',@icount output
select @icount as a
if @icount > 0