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.
| Author |
Topic |
|
bboland
Starting Member
2 Posts |
 Posted - 2005-05-23 : 10:00:16
|
| 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))ASDeclare @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 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-05-23 : 10:19:13
|
| Try printing @sql at the end instead of execing it.You should spot the problem.in fact it's in the building of the string.Select @SQL = @SQL + '''<td>'+convert(varchar(10),rec.ind_number)+'</td>'', 'that is trying to access rec.ind_number but doesn't have a from clause.==========================================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. |
 |
|
|
sureshot
Yak Posting Veteran
72 Posts |
Posted - 2005-05-23 : 10:19:48
|
| Haven't checked it carefully, but make sure you are escaping any single quotes and maybe try SET @SELECT = instead of select @selectAlso, I'd generally be wary of putting your presentation layer HTML in a stored procedure. It's best done in an application layer. |
|
|
|
bboland
Starting Member
2 Posts |
Posted - 2005-05-23 : 11:08:36
|
| Thank you guys for the quick replies. I have a couple of other questions based on your responses. I am used to building the display and manipulating data using ASP (which is what we are still using) to get reports to read out as we want. I am just messing around with this to find a more efficient way to get the data to the web page as some of the reports are quite bulky and take some time to load.nr - Are you saying that all of the Select statements need to have the from assigned to them. I tried this query in Query Analyzer and it ran just as I wanted it to. The Exec(@SQL) has been where I have tripped up. Is there a difference?sureshot - Why would you be wary of doing this? Is it just to keep the data separate from the presentation, or are there any speed issues in doing this? Really my only goal here is to speed up some reports.Thanks again, I really appreciate you helping a newcomer out. |
|
|
|
|
|
|
|
|
Building HTML results into a stored procedure