Just checking - 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)

Just checking

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-17 : 15:32:04

A Stored Proc returns 5 fields (generally). Some customers are convincing me that they don't want to see 1 of the fields, so I've got to clip it somehow.

My client-side ASP software is a datagrid. The easiest location to remove the column would be in the stored procedure. I can test for the boolean (show the field / don't show the field) using a select on a customer preferences table.

I'm a little stuck on how to best code the return recordset since SQL 2000 doesn't allow conditional statements to select recordset columns. I'm left coding something like:

IF condition BEGIN -- Return all 5 columns
SELECT A, B, C, D, E FROM ..
END ELSE BEGIN -- Return only 4 columns
SELECT A, B, C, D FROM ...
END

This isn't so bad, except it quickly gets out of hand if additional conditional columns must be applied.

Is there a better way to solve this?

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2003-11-17 : 15:34:04

You could put the resultset into a temp table and use dynamic sql to return the relevant columns.
Could use a table to define the columns returned in each situation.

Another option is to return to the client which columns to display from the resultset and let the client deal with it.

==========================================
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.

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-11-17 : 15:46:22

Maybe you could do this on the ASP side of things. You could code this "ui" specific thing in there and do a Rs.fields.remove depending
on the conditions, and then render your grid.

You could get really fancy and use XSLT to transform your RS into a grid. Depending on different conditions, you could load a different XSLT style sheet and get differing outputs easy by changing a XSLT text file.

RS-->XML-->XSLT-->HTML Grid

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-17 : 18:24:09

rs.fields.remove ("fieldname")

Object does not support this property or method....

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-11-17 : 18:28:49

My bad, it's like this:

You need the ordinal posistion of the field you want to delete