Select userdefined columns - 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 2005 Forums

Transact-SQL (2005)

Select userdefined columns

Author

Topic

proxteam
Starting Member

2 Posts

Posted - 2010-01-23 : 10:57:47

Hello,

I have a table that contains a large amount of columns. I want to select some columns conditionally based on the given sql procedure parameters. Is there a way to restrict the columns shown even if the columns shown depend on the parameters given? I would like not to concat a sql string because then I have a limit of 8000 characters per sql statement.

Thanks in advance.

Kristen
Test

22859 Posts

Posted - 2010-01-23 : 11:23:28

You mean something like:


SELECT
IF @PARAM = 1 THEN Col1, Col2, Col3
ELSE IF @PARAM = 2 THEN Col1, Col4, Col5, Col6
END IF

then the answer is no

You could have two different queries, and execute one or other depending on a parameter.

You could select the PK from a master query into a Temp Table, and then have similar condition logic to above to JOIN that Temp Table and return different resultsets.

But you can't conditionally have different numbers of columns.

You could always return the same number of columns, but return some of them NULL (depending on your parameters) - which would reduce the amount of data returned.

However, all these routes are not going to be very efficient as the query plan will be "vague".

We tend to do


CREATE PROCEDURE Sproc1
@SomeParam int
AS
CREATE #TempTable
(
   MyPK int
)

EXEC FindTheDataSProc @SomeParam = @SomeParam

SELECT Col1, Col2, Cool3
FROM MyTable AS M
    JOIN #TempTable AS T
        ON T.MyPK = M.MyPK

and then create another procedure SProc2 that calls the same FindTheDataSProc procedure, but has a different SELECT statement thereafter.

That way we encapsulate the "data finder" logic in one SProc, and thus only one place to fix any bugs etc., but then have different Sprocs that return the data, and each will have its own, optimised, query plan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources