Doing more than a query in Stored Procedure

Posted by Thiru under Sql Server on 11/29/2011 | Points: 10 | Views : 2016 | Status : [Member] | Replies : 4

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi Friends,

Generally we used to concatenate sometime as follows in coding.

 c1.Open()
                cmd = New SqlCommand("select * from tblintroimg order by id", c1)
                dr = cmd.ExecuteReader
                While dr.Read
                    str = str & "<li><a href='#'><img src='intropageimg/" & dr("imgname") & "' alt='' /></a></li>"
                End While
                cmd.Dispose()
                c1.Close()
label1.text=str

I like to do the same thing using strored procedure.
i mean concatenating the string str and displaying in label1

regards,
Thiru

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Arefin on: 11/29/2011 [Member] Starter | Points: 25

0	Hi Thiru, Use Cursor to store a SELECT query and then fetch the CURSOR to concatenate data to a variable. Finally return the variable. Thanks, Arefin Thiru, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Thiru on: 11/30/2011 [Member] Starter | Points: 25

0	Thanks for your reply Arefin. Can you give any reference article in this regard. i like to see some samples to do the same. Thiru, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Arefin on: 11/30/2011 [Member] Starter | Points: 25

0	Hi, You can see the below links: http://therelentlessfrontend.com/2010/05/11/basic-wcf-tutorial-for-beginners/ http://blog.sqlauthority.com/2007/01/01/sql-server-simple-example-of-cursor/ You will get a clear idea. Thanks, Arefin Thiru, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: PandianS on: 12/1/2011 [Member] [MVP] Silver | Points: 25

0	Hi Thiru I think you can do this without any LOOP / CURSOR! Sample Data Insert tblintroimg(imgname) Values('One.jpg') Insert tblintroimg(imgname) Values('Two.jpg') Insert tblintroimg(imgname) Values('Three.jpg') Insert tblintroimg(imgname) Values('Four.jpg') Insert tblintroimg(imgname) Values('Five.jpg') Stored Procedure Create Proc Usp_ImgName As Begin Set Nocount On Declare @str Varchar(Max) Select @str = Coalesce(@str + Char(10),'') + '<li><a href=''#''><img src=''intropageimg/' + imgname + ''' alt=''''/></a></li>' from tblintroimg with(nolock) order by id Select @str [ImgPath] End Go Result ImgPath ------------------------------------------------------------------ <li><a href='#'><img src='intropageimg/One.jpg' alt=''/></a></li> <li><a href='#'><img src='intropageimg/Two.jpg' alt=''/></a></li> <li><a href='#'><img src='intropageimg/Three.jpg' alt=''/></a></li> <li><a href='#'><img src='intropageimg/Four.jpg' alt=''/></a></li> <li><a href='#'><img src='intropageimg/Five.jpg' alt=''/></a></li> Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Thiru, if this helps please login to Mark As Answer. \| Alert Moderator

Hi Thiru

I think you can do this without any LOOP / CURSOR!

Sample Data

Insert tblintroimg(imgname) Values('One.jpg')

Insert tblintroimg(imgname) Values('Two.jpg')

Insert tblintroimg(imgname) Values('Three.jpg')

Insert tblintroimg(imgname) Values('Four.jpg')

Insert tblintroimg(imgname) Values('Five.jpg')

Stored Procedure

Create Proc Usp_ImgName

As

Begin

	Set Nocount On

	

	Declare    @str	Varchar(Max)				



	Select  @str = Coalesce(@str + Char(10),'') + '<li><a href=''#''><img src=''intropageimg/' + imgname + ''' alt=''''/></a></li>' from tblintroimg with(nolock) order by id



	Select @str [ImgPath]

End

Go

Result

ImgPath

------------------------------------------------------------------

<li><a href='#'><img src='intropageimg/One.jpg' alt=''/></a></li>

<li><a href='#'><img src='intropageimg/Two.jpg' alt=''/></a></li>

<li><a href='#'><img src='intropageimg/Three.jpg' alt=''/></a></li>

<li><a href='#'><img src='intropageimg/Four.jpg' alt=''/></a></li>

<li><a href='#'><img src='intropageimg/Five.jpg' alt=''/></a></li>

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

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

Latest Posts