Stored Procedures return ing mutiple rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Procedures return ing mutiple rows

Author

Topic

neojapanese
Starting Member

13 Posts

Posted - 2006-07-29 : 12:22:07

hello. I am trying to speed up my asp.net caledar.

I need a Stored Procedure that takes a Parameter.
the Parameter will be a Date.

then I want sql server to return all the classes that will happen on that date in formated string so i can display in the Asp.net caledar.

the table looks like this
classid
classname
classdate
classtime
(there are more than one class happening on the same day)

I need the retuen value to look like this

"Yoga 9:00am" & "" & "Jazz 11:00am" & 

can someone help me or point me to a sample. this is really hard. thank you

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-29 : 13:28:58

declare @s varchar(8000)
select @s = coalesce(@s + ' & ', '') + '"' + classname + ' ' + convert(varchar(10),classtime) + '" & ""
from tbl where classdate = @date
select @s

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

neojapanese
Starting Member

13 Posts

Posted - 2006-07-29 : 14:03:37

thank you so much!!! i am going to try it right now!! this should speed it up lots!

quote:
Originally posted by nr

declare @s varchar(8000)
select @s = coalesce(@s + ' & ', '') + '"' + classname + ' ' + convert(varchar(10),classtime) + '" & ""
from tbl where classdate = @date
select @s

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

neojapanese
Starting Member

13 Posts

Posted - 2006-07-29 : 17:02:00

thanks again but can you help me

i go this error "System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'getlistofspclass'." below is my newB sql code

CREATE PROCEDURE getlistofspclass
(
@date datetime
)

AS
declare @s varchar(8000)

select @s = coalesce(@s + ' & ', '') + '"' + convert(varchar(10),dbo.calader.timestart)+ ' ' + dbo.classT.classname + ''
FROM dbo.calader INNER JOIN dbo.classT ON dbo.calader.classid = dbo.classT.classcode
WHERE dbo.calader.caledardatedates = @date
select @s
GO

quote:
Originally posted by nr

declare @s varchar(8000)
select @s = coalesce(@s + ' & ', '') + '"' + classname + ' ' + convert(varchar(10),classtime) + '" & ""
from tbl where classdate = @date
select @s

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-29 : 18:49:56

How are you trying to run it?
Should be query analyser or management studio.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

neojapanese
Starting Member

13 Posts

Posted - 2006-07-29 : 19:23:25

asp.net
when i call it thats what i get.
is my syntax right?

quote:
Originally posted by nr

How are you trying to run it?
Should be query analyser or management studio.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-29 : 20:15:45

The create procedure script is ok - it's the asp.net code that's giving the error.
See
http://www.nigelrivett.net/DOTNET/DotNetDBAccess.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources