using COALESCE *in* a SELECT - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

using COALESCE *in* a SELECT

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-09-06 : 10:12:20

David writes "So this one is killing me and everyone else I've asked. I have a stored procedure (below) that works perfectly. It returns the data I want in HTML format for displaying on a page. I've utilized a trick from this site (sqlindex.com) called COALESCE to manage the options items in the drop-down combo boxes. As it is, it is almost what I want. I'm trying to get the options lists (called @OptionsListA, B, and C) to be dynamically generated based on the row, so that I can add additional code to return a selected="selected" string if the option is the one set.
Do you have any idea how, basically, I can replace the @OptionsListA that appears *in* the main SELECT statement with the code at the top of the sp so that I can do this?
Thanks!!!

SELECT @OptionsListA = COALESCE(@OptionsListA + ' ', '') + '<option value="' + Cast(template_id AS varchar) + '">' + strName + '</option>' FROM tblTemplate WHERE template_type = 1
SELECT @OptionsListB = COALESCE(@OptionsListB + ' ', '') + '<option value="' + Cast(template_id AS varchar) + '">' + strName + '</option>' FROM tblTemplate WHERE template_type = 7
SELECT @OptionsListC = COALESCE(@OptionsListC + ' ', '') + '<option value="' + Cast(template_id AS varchar) + '">' + strName + '</option>' FROM tblTemplate WHERE template_type = 3
SELECT
@CrLf +
'<TR>' + @CrLf +
'<TD>' + @CrLf +
'<form name="frmDesignEdit" action="foo.asp" method="post">' + @CrLf +
'<table border="0" cellpadding="0" cellspacing="0" width="100%">' + @CrLf +
'<TR>' + @CrLf +
'<TD>' + @CrLf +
'Name:<br />' + @CrLf +
'<input type="text" name="template_name" maxlength="50" value="' + (SELECT name FROM tblClasses WHERE tblClasses.class_id = tblDesigns.class_id) + '" />' + @CrLf +
'</TD>' + @CrLf +
'<TD>' + @CrLf +
'Header:<br /><select name="header_id">' + @OptionsListA
+ '</select>' + @CrLf +
'</TD>' + @CrLf +
'<TD>' + @CrLf +
'Footer:<br /><select name="footer_id">' + @OptionsListC + '</select>' + @CrLf +
'</TD>' + @CrLf +
'<TD>' + @CrLf +
'Enabled:<input type="checkbox" name="enabled" ' + Replace(bEnabled, "1", "checked=""checked""") + ' />' + @CrLf +
'</TD>' + @CrLf +
'<TD>' + @CrLf +
'<input type="Submit" name="cmdEdit" value="Update Design" />' + @CrLf +
'</TD>' + @CrLf +
'</TR>' + @CrLf +
'<TR>' + @CrLf +
'<TD>' + @CrLf +
'Design ID: ' + Cast(design_id AS varchar) + @CrLf +
'<input type="hidden" name="template_id" value="0" />' + @CrLf +
'<input type="hidden" name="modType" value="Add" />' + @CrLf +
'</TD>' + @CrLf +
'<TD>' + @CrLf +
'Menu:<br /><select name="menu_id">' + @OptionsListB + '</select>' + @CrLf +
'</TD>' + @CrLf +
'<TD>' + @CrLf +
'Formatting:<br /><select name="css_format_id"><option>CSS_FORMAT</option></select>' + @CrLf +
'</TD>' + @CrLf +
'</TR>' + @CrLf +
'</TABLE>' + @CrLf +
'</form>' + @CrLf +
'</TD>' + @CrLf +
'</TR>' + @CrLf
FROM tblDesigns ORDER BY design_id DESC"

Edited by - robvolk on 09/06/2002 10:12:43

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-06 : 10:24:33

In other words (I hope!) you want to specify an option and have the HTML automatically mark it as selected in the drop-down box. I had a similar situation and I used a stored procedure that accepted parameters for this. Here's an example:

CREATE PROCEDURE DropDowns @optA int=Null, @optB int=Null, @optC int=Null AS
SET NOCOUNT ON
DECLARE @OptionsListA varchar(8000), @OptionsListB varchar(8000), @OptionsListC varchar(8000)

--the following line resets the parameters that were not passed
SELECT @OptionsListA='', @OptionsListB='', @OptionsListC='',
@optA=IsNull(@optA, -1), @optB=IsNull(@optB, -1), @optC=IsNull(@optC, -1)

SELECT @OptionsListA = COALESCE(@OptionsListA + ' ', '') + '<option value="' + Cast(template_id AS varchar) + CASE template_id WHEN @optA THEN ' selected' ELSE '' END + '">' + strName + '</option>'
FROM tblTemplate WHERE template_type = 1

SELECT @OptionsListB = COALESCE(@OptionsListB + ' ', '') + '<option value="' + Cast(template_id AS varchar) + CASE template_id WHEN @optB THEN ' selected' ELSE '' END + '">' + strName + '</option>'
FROM tblTemplate WHERE template_type = 7

SELECT @OptionsListC = COALESCE(@OptionsListC + ' ', '') + '<option value="' + Cast(template_id AS varchar) + CASE template_id WHEN @optC THEN ' selected' ELSE '' END + '">' + strName + '</option>'
FROM tblTemplate WHERE template_type = 3

You can use it like this:

EXECUTE DropDowns @optA=4, @optC=6

The output for List A with have ID 4 selected, List B with have nothing selected, and List C with have ID 6 selected (that is, if I read your question right!)

HTH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources