use mytest
go
exec p_city 2,4
exec p_city_cnt 2,3
select stuff((select ',' + city_id from cities for xml path('')),1,1,'') concatStr
select stuff((select ',' + city_name from cities for xml path('')),1,1,'') concatStr
USE mytest
GO
IF EXISTS ( select * from dbo.sysobjects where id=OBJECT_ID('dbo.p_city') and type = 'P')
drop procedure p_city
GO
create procedure p_city
@idx int,
@idy int
--,@cnt int output --solution 2.3
AS
BEGIN
declare @sql nvarchar(2000)
declare @param nvarchar(2000)
/*
--print 'solution - 1.1 - common situation'
set @sql = N'select city_name from cities where city_id>=@id_start and city_id<=@id_end'
set @param = N'@id_start int, @id_end int'
EXEC sp_executesql @sql, @param, @id_start=@idx, @id_end=@idy
*/
--print 'solution - 1.2 - for some special situation'
create table #tmp (city_name varchar(100))
set @sql = N'insert into #tmp(city_name) select city_name from cities where city_id between @id_start and @id_end'
set @param = N'@id_start int, @id_end int'
EXEC sp_executesql @sql, @param, @id_start=@idx, @id_end=@idy
select * from #tmp
--set @cnt = (select count(1) from #tmp) --solution 2.3
IF EXISTS (select name from tempdb..sysobjects where id=OBJECT_ID('tempdb..#tmp') and type='U')
drop table #tmp
/*
-- not ok
print 'solution - 1.3 - use table variable'
exec( N'declare @ctname table(city_name varchar(100))')
set @sql = N'insert into @ctname(city_name) select city_name from cities where city_id between @id_start and @id_end'
set @param = N'@id_start int, @id_end int'
EXEC sp_executesql @sql, @param, @id_start=@idx, @id_end=@idy
exec(N'select * from @ctname')
*/
END
USE mytest
GO
IF EXISTS ( select name from sysobjects where name = 'p_city_cnt' and type = 'P')
drop procedure p_city_cnt
GO
create procedure p_city_cnt
@idx int,
@idy int
AS
BEGIN
--print 'solution - 2.1'
create table #tmp (
city_name varchar(100)
)
insert into #tmp(city_name) exec p_city @idx, @idy
--select count(1) as number from #tmp
select @@ROWCOUNT as number
drop table #tmp
/*
--print 'solution - 2.2'
declare @ctname table(
city_name varchar(100)
)
insert into @ctname (city_name) exec p_city @idx, @idy
select count(1) as number from @ctname
*/
/*
-- solution 2.3.1, will response 2 result sets.
declare @cnt int
exec p_city @idx, @idy, @cnt out
select @cnt as number
-- solution 2.3.2, will response only one result set.
create table #tmp (
city_name varchar(100)
)
declare @cnt int
insert into #tmp(city_name) exec p_city @idx, @idy, @cnt out
select @cnt as number
drop table #tmp
*/
END
