set nocount on 
create table #DISTINCTC(
  [name] nvarchar(120)
)
insert into #DISTINCTC SELECT Column_Name FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'AGEN'
------------------------
--select * from #DISTINCTC

declare Dist cursor for
select [name]from #DISTINCTC 

open Dist	
	DECLARE @z int
	declare @y nvarchar(120)
	declare @SQLString nvarchar(4000)
	fetch next from Dist into @y
	while @@fetch_status=0
	begin	
		SET @SQLString = N'select count(distinct('+@y+N')) from AGEN'
		EXEC sp_executesql @SQLString, N'@n int OUTPUT', @z OUTPUT
		if ((select @z)<= 10)
			begin
				create table #DISTINCTC1( DistinctVal nvarchar(4000))
				insert into #DISTINCTC1 EXEC('select distinct '+@y+' from AGEN')
				select * from #DISTINCTC1
				declare Insertion cursor for select * from #DISTINCTC1
				open Insertion
				Declare @I nvarchar(4000)
				FETCH NEXT FROM Insertion INTO @I
				WHILE @@FETCH_STATUS = 0
					begin
					insert into SATIS values('AGEN',@y,@I) 
					FETCH NEXT FROM Insertion INTO @I
					end
				close Insertion
				Deallocate Insertions
			end
	fetch next from Dist into @y
	end
close Dist 
Deallocate Dist 

drop table #DISTINCTC1
drop table #DISTINCTC  

select * from SATIS

 

SET @SQLString = N'select @z=count(distinct('+@y+N')) from AGEN'

quote:

---

Originally posted by visakh16

where are you grabbing value of @z? you've just declared it in sp_executesql but havent used it at all in query so it will still be NULL and hence block under if wont get executed at all.
probably, you meant below query

SET @SQLString = N'select @z=count(distinct('+@y+N')) from AGEN'

---

quote:

---

Originally posted by Ali.M.Habib

quote:

---

Originally posted by visakh16

where are you grabbing value of @z? you've just declared it in sp_executesql but havent used it at all in query so it will still be NULL and hence block under if wont get executed at all.
probably, you meant below query

SET @SQLString = N'select @z=count(distinct('+@y+N')) from AGEN'

---

it worked but after the first insertion it give this error
A cursor with the name 'Insertions' does not exist. what shall I do please

---

quote:

---

Originally posted by visakh16

can you explain what you're trying to do here?

---

quote:

---

Originally posted by visakh16

raise what error?

---

exec('insert into SATIS SELECT ''AGEN'','+@y+','+@I+' FROM Yourtable')