how to Concatenate using Cursor in Sql Server

Posted by Jayakumars under Sql Server on 10/16/2012 | Points: 10 | Views : 13472 | Status : [Member] [MVP] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

hi

how to Concatenate dynamic string Query using Cursor in Sql Server.

My Query like this
==================
SELECT column_name FROM information_schema.COLUMNS WHERE table_name LIKE 'TblStudens'
and ordinal_position>=3

this Query output this
========================
column_name
=============
S1
S2
S3
S4
S5

here i need how to concatenate in my Query s1=1 and s2<=3 and s3>=4 and s4=4 and s5=7
how to achieve this using Cursor.

i m using cursor but showing row vise i need concatenate to dynamic string

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Krv on: 11/6/2012 [Member] Starter | Points: 25

0	declare a varchar variable top of the query and keep add the values/ queries to it like Declare @str varchar(max) - at the top SET @str = @str + columname - inside cursor Thanks, R.Vasanth MCTS(SQL Server & BI) http://bigdatatech.blogspot.com Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sourabh07 on: 11/6/2012 [Member] Starter | Points: 25

0	Hi..to all please find the sample code for this....and change it accordingly... Create table #temp ( id int, condition varchar(50) ) Insert into #temp (id,condition) Select 1,'S1' Union Select 2,'S2' Union Select 3,'S3' Union Select 4,'S4' Union Select 5,'S5' Select * from #temp Declare cr_sellall_temp cursor for Select id,condition from #temp open cr_sellall_temp Declare @id int Declare @condition varchar(50) Fetch next from cr_sellall_temp into @id,@condition while @@FETCH_STATUS=0 begin if @condition='S1' set @condition='S1=1' else if @condition='S2' set @condition='S2<=3' else if @condition='S3' set @condition='S3>=4' else if @condition='S4' set @condition='S4=4' else if @condition='S5' set @condition='S5=7' Update #temp set condition=@condition where id=@id Fetch next from cr_sellall_temp into @id,@condition end close cr_sellall_temp deallocate cr_sellall_temp select * from #temp drop table #temp Please let me know if there is any problem in this......:-) Sourabh07 Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

Hi..to all

please find the sample code for this....and change it accordingly...

Create table #temp

(

	id int,

	condition varchar(50)

)



Insert into #temp (id,condition)

Select 1,'S1'

Union

Select 2,'S2'

Union

Select 3,'S3'

Union

Select 4,'S4'

Union

Select 5,'S5'



Select * from #temp



Declare cr_sellall_temp cursor 

for Select id,condition from #temp



open cr_sellall_temp 



Declare @id int

Declare @condition varchar(50)



Fetch next from cr_sellall_temp into @id,@condition



while @@FETCH_STATUS=0

begin

	

	if @condition='S1'

		set @condition='S1=1'

	else if @condition='S2'

		set @condition='S2<=3'

	else if @condition='S3'

		set @condition='S3>=4'

	else if @condition='S4'

		set @condition='S4=4'

	else if @condition='S5'

		set @condition='S5=7'

		

	Update #temp

	set condition=@condition

	where id=@id

	

	Fetch next from cr_sellall_temp into @id,@condition



end





close cr_sellall_temp 



deallocate cr_sellall_temp 



select * from #temp



drop table #temp

Please let me know if there is any problem in this......:-)

Sourabh07

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts