how to Concatenate using Cursor in Sql Server

Posted by Jayakumars under Sql Server on 10/16/2012 | Points: 10 | Views : 7824 | Status : [Member] [MVP] | Replies : 2
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


Responses

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

Up
0
Down
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

Up
0
Down
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

Login to post response