Hi All
I'm trying to display a html report from a store procedure but the string is
to long. I try to cut the sting in parts but no luck.
CREATE PROCEDURE createReport
@.orderNumber int
AS
declare @.lineDesc01 varchar(8000)
declare @.lineDesc02 varchar(8000)
declare @.lineDesc03 varchar(8000)
set @.lineDesc01 = 'select lineDesc = ''<html><head><title>blabla...
<td > ''+ cast(b.orderID as varchar) +''</td> bla...'
set @.lineDesc02 = '</td>
<td>''+ isnull(b.myDesc, '') +''</td>
</tr>
</table><br> bla.bla...
set @.lineDesc03 = '</td>
<td>como esta</td>
</tr>
</table></body></html>''
FROM orders b
WHERE b.orderID = @.orderNumber'
exec( @.lineDesc01 + @.lineDesc02 + @.lineDesc03)
How can i fix this problem?
Is other way to do this?
Tks in advance.
JFBWhy are you creating the presentation (HTML) in the stored procedure? You
should always try to minimize the amount of data you pass server <-> client
and you should leave as much of the presenation as you can where it belongs,
at the presentation layer.
But aside from that, why are you using variables at all?
SELECT '<html>... '+RTRIM(OrderID)+'...up to about 8000',
'</td><td>'+COALESCE(myDesc,'')+'... up to about 8000',
'</td><td>como esta...up to about 8000'
FROM orders
WHERE orderID = @.orderNumber
Now the presentation tier just has to write out however many columns you end
up with (and I only suggest using columns because you need to concatenate).
There are much, much, much better was to do this, even if you are going to
insist on the bad practice of missing data operations with presentation
details...
"JFB" <help@.jfb.com> wrote in message
news:uAKXgsfkFHA.2792@.TK2MSFTNGP10.phx.gbl...
> Hi All
> I'm trying to display a html report from a store procedure but the string
> is to long. I try to cut the sting in parts but no luck.
> CREATE PROCEDURE createReport
> @.orderNumber int
> AS
> declare @.lineDesc01 varchar(8000)
> declare @.lineDesc02 varchar(8000)
> declare @.lineDesc03 varchar(8000)
> set @.lineDesc01 = 'select lineDesc = ''<html><head><title>blabla...
> <td > ''+ cast(b.orderID as varchar) +''</td> bla...'
> set @.lineDesc02 = '</td>
> <td>''+ isnull(b.myDesc, '') +''</td>
> </tr>
> </table><br> bla.bla...
> set @.lineDesc03 = '</td>
> <td>como esta</td>
> </tr>
> </table></body></html>''
> FROM orders b
> WHERE b.orderID = @.orderNumber'
> exec( @.lineDesc01 + @.lineDesc02 + @.lineDesc03)
> How can i fix this problem?
> Is other way to do this?
> Tks in advance.
> JFB
>|||How about learning something new and useful?
Keywords: SQLXML, XSL.
If it really needs to be done straight from the server.
ML|||Tks...You right I will try to use my normal way.
Can you tell me about other ways?
Rgds
JFB
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uMABvxfkFHA.320@.TK2MSFTNGP09.phx.gbl...
> Why are you creating the presentation (HTML) in the stored procedure? You
> should always try to minimize the amount of data you pass server <->
> client and you should leave as much of the presenation as you can where it
> belongs, at the presentation layer.
> But aside from that, why are you using variables at all?
> SELECT '<html>... '+RTRIM(OrderID)+'...up to about 8000',
> '</td><td>'+COALESCE(myDesc,'')+'... up to about 8000',
> '</td><td>como esta...up to about 8000'
> FROM orders
> WHERE orderID = @.orderNumber
> Now the presentation tier just has to write out however many columns you
> end up with (and I only suggest using columns because you need to
> concatenate).
> There are much, much, much better was to do this, even if you are going to
> insist on the bad practice of missing data operations with presentation
> details...
>
> "JFB" <help@.jfb.com> wrote in message
> news:uAKXgsfkFHA.2792@.TK2MSFTNGP10.phx.gbl...
>|||JFB,
Generally you would return only data from the database, usually in the form
of a recordset. For example, your stored proc would be like this (contents
only):
SELECT OrderID, [Description]
FROM dbo.Orders
WHERE OrderID = @.OrderId
--
Then say you were presenting the report via an ASP web page, it would look
something like this:
// This code would be in your ASP file
// Assume command has been executed and returned a recordset "rs"
for (; !rs.EOF; rs.MoveNext())
{
Response.Write( "<tr><td>" );
Response.Write( rs.Fields("OrderID").Value );
Response.Write( "</td><td>" );
Response.Write( rs.Fields("Description").Value );
Response.Write( "</td></tr>" );
}
"JFB" wrote:
> Tks...You right I will try to use my normal way.
> Can you tell me about other ways?
> Rgds
> JFB
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:uMABvxfkFHA.320@.TK2MSFTNGP09.phx.gbl...
>
>|||mmm... do you have any exmples? or any web sites to check it out?
Tks for you reply
JFB
"ML" <ML@.discussions.microsoft.com> wrote in message
news:44494029-B841-4A9E-8005-9CDDA3557DDD@.microsoft.com...
> How about learning something new and useful?
> Keywords: SQLXML, XSL.
> If it really needs to be done straight from the server.
>
> ML|||> any web sites to check it out?
www.sqlxml.org
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment