Building HTML results into a stored procedure

Question

Building HTML results into a stored procedure

Brent B

Newbie

Points: 3
More actions
May 23, 2005 at 8:09 am

#86399

I am trying to build html into my stored procedure output and am not having much luck. I get this error almost any way that I try to format this which makes me think that maybe I am trying to do something that isn't possible with dynamic table names.
"A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."
The SP itself is below. Any help that anyone could give me would be freatly appreciated.
Thanks,
Brent
'//******************************************************
ALTER      PROC passSoldTrList (
    @tblPOSReceiptItem varchar (50),
    @tblPOSInventory varchar (50),
    @colPOSInventorySKU varchar (50),
    @tblPOSIndividual varchar (50),
    @iLoc_id varchar (50),
    @iYear varchar (50),
    @iMonth varchar (50))
AS
Declare @sql VarChar(8000)
Select @sql = 'SELECT ''<table width="100%" cellpadding="2" cellspacing="0" border="1" bordercolor="blue">'' as row1, '
Select @sql = @sql + '''<tr>'' as row2, '
Select @sql = @sql + '''<th align="left" width="5%">Ind #</th>'' as row3, '
Select @sql = @sql + '''<th align="left" width="11%">Name</th>'' as row4, '
Select @sql = @sql + '''<th align="left" width="8%">Date</th>'' as row5, '
Select @sql = @sql + '''<th align="left" width="22%">(Sku) Description</th>'' as row6, '
Select @sql = @sql + '''<th align="right" width="3%">Qty</th>'' as row7, '
Select @sql = @sql + '''<th align="right" width="7%">Price</th>'' as row8, '
Select @sql = @sql + '''<th>Pay Type</th>'' as row9, '
Select @sql = @sql + '''<th>2nd Amt. Pd.</th>'' as row10, '
Select @sql = @sql + '''<th>Issued To</th></tr>'' as row11 '
Select @sql = @sql + 'UNION ALL '
Select @sql = @sql + 'SELECT '', '
Select @sql = @sql + '''<tr>'', '
Select @sql = @sql + '''<td>'+convert(varchar(10),rec.ind_number)+'</td>'', '
Select @sql = @sql + '''<td>'+vc.par_fname+' '+vc.par_lname+'</td>'', '
Select @sql = @sql + '''<td>'+convert(varchar(20),rec.[date])+'</td>'', '
Select @sql = @sql + '''<td>('+convert(varchar(10),rec.sku)+') '+inv.description+'</td>'', '
Select @sql = @sql + '''<td>'+convert(varchar(10),rec.quantity)+'</td>'', '
Select @sql = @sql + '''<td>'+convert(varchar(10),rec.adjusted_price)+'</td>'', '
Select @sql = @sql + '''<td>'+rec.pmttype+'</td>'', '
Select @sql = @sql + '''<td>'+convert(varchar(10),rec.sndamtpd)+'</td>'', '
Select @sql = @sql + '''<td>'+rec.memo+'</td></tr>'', '
Select @sql = @sql + 'FROM rec_receipt rec '
Select @sql = @sql + 'INNER JOIN recreation_inventory inv '
Select @sql = @sql + 'ON rec.sku = inv.rec_sku '
Select @sql = @sql + 'INNER JOIN vcFeb10_individual vc '
Select @sql = @sql + 'ON rec.ind_number = vc.ind_number '
Select @sql = @sql + 'WHERE (inv.pass_period > 0) AND '
Select @sql = @sql + '(rec.loc_id =5) '
Select @sql = @sql + 'AND ({ fn YEAR(rec.[date]) } =2004) '
Select @sql = @sql + 'AND ({ fn MONTH(rec.[date]) } =2) '
Select @sql = @sql + 'UNION ALL '
Select @sql = @sql + 'SELECT '
Select @sql = @sql + '''</table>','','','','','','','','','',''''
Exec (@SQL)
GO

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 1

You don't have a FROM clause for the first SELECT. You need the same FROM in both (i.e. they both need to be complete SELECT statements before being UNIONed).

Without the FROM clause, SQL Server seems to be thinking you're using it to assign values to variables.

HTH.

Brent B Newbie Points: 3 More actions · Answer 2

Thanks for the info Paul. I am new to T-SQL and it has been suggested to me in a different forum that I keep the data from the presentation separate. While I see the benefit in that, my only goal here is to speed up some reports. At any rate, I altered the opening and closing select statements to read:

Select @sql = 'SELECT ''<table width="100%" cellpadding="2" cellspacing="0" border="1" bordercolor="blue">'' as row1, '

Select @sql = @sql + '''<tr>'' as row2, '

Select @sql = @sql + '''<th align="left" width="5%">Ind #</th>'' as row3, '

Select @sql = @sql + '''<th align="left" width="11%">Name</th>'' as row4, '

Select @sql = @sql + '''<th align="left" width="8%">Date</th>'' as row5, '

Select @sql = @sql + '''<th align="left" width="22%">(Sku) Description</th>'' as row6, '

Select @sql = @sql + '''<th align="right" width="3%">Qty</th>'' as row7, '

Select @sql = @sql + '''<th align="right" width="7%">Price</th>'' as row8, '

Select @sql = @sql + '''<th>Pay Type</th>'' as row9, '

Select @sql = @sql + '''<th>2nd Amt. Pd.</th>'' as row10, '

Select @sql = @sql + '''<th>Issued To</th></tr>'' as row11 '

Select @sql = @sql + 'FROM rec_receipt'

AND

Select @sql = @sql + 'SELECT '

Select @sql = @sql + '''</table>','','','','','','','','','',''''

Select @sql = @sql + 'FROM rec_receipt'

but I continue to get the same error. If you have any other thoughts I would love to hear them, otherwise, thank you for your suggestion.

Brent

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 3

Spotted it: in the penultimate line below. Double up all the single quotes?

So:

Select @sql = @sql + '''','','','','','','','','','',''''

Becomes:

Select @sql = @sql + ''''','''','''','''','''','''','''','''','''','''','''''

That seemed to work for me (as I managed to replicate your error and correct it). I initially thought you were getting the error on executing the code, but it actually occurs in constructing the SQL statement on the penultimate line.

Brent B Newbie Points: 3 More actions · Answer 4

You are the man Paul! That seemed to get me over that hurdle. I am now getting this error on all of my aliases:

The column prefix 'rec' does not match with a table name or alias name used in the query.

I appreciate your help, I will see if I can't figure out what I may have done wrong here and will post again if I can't seem to dig up the answer.

Thanks again, I really appreciate your help.

Brent