Return OPTION elements for a dropdown from a STORED PROCEDURE

Wouldn’t it be cool if your stored proc can return you an XML which has all your <OPTION> tags to fill your dropdown? With SQL2000’s SELECT FOR XML EXPLICIT, you can do it very easily. Run the below query in Query analyzer and you will see what I mean.

DECLARE @States TABLE
(
[Code] char(2),
[State] varchar(100)
)

SET QUOTED_IDENTIFIER OFF
SET NOCOUNT ON
INSERT INTO @States VALUES(“TX”, “Texas”)
INSERT INTO @States VALUES(“AL”, “ALABAMA”)
INSERT INTO @States VALUES(IL, ILLINOIS)
INSERT INTO @States VALUES(NY, NEW YORK”)
INSERT INTO @States VALUES(“CA”, “California”)

/*THE SELECT QUERY WHICH RETURNS <OPTION> elements */
select
1 as tag,
NULL as Parent,
Code AS [Option!1!value],
State AS [Option!1!!element]
from @States
order by State for xml explicit
SET NOCOUNT OFF
SET QUOTED_IDENTIFIER ON

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s