Doing more than a query in Stored Procedure

Posted by Thiru under Sql Server on 11/29/2011 | Points: 10 | Views : 1074 | Status : [Member] | Replies : 4
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




Responses

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

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

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

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

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

Login to post response