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