You can use Dynamic SQL ... To achieve the same ...

--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
SCT Software Solutions
[url]http://www32.brinkster.com/sqlvinod[/url]


"DC" <dc@upsize.de> wrote in message news:5b7bac12.0307100535.36ce77c1@posting.google.c om...
> Hi,
>
> depending on the permissions of the requester, I need to return
> different columns. Example table "testvals":
>
> id value1 value2 time1 time2 permissionmask
> 1 A B 20030101 20030102 1
> 2 C D 20030103 20030104 4
>
> The stored procedure that queries the values should look like this:
>
> create procedure getdata
>
> @perms int
>
> as
>
> select
> value = case when @perms & permissionreq = @pers then value1 else
> values 2 end,
> time = case when @perms & permissionreq = @pers then time1 else time2
> end
> from testvals
>
> return 0
>
>
> This way, if a permission is set, value1 and time1 will be retrieved.
> I could make this a tiny bit nicer by using a funktion like
> "fnchoose(@persm, permissionreq, value1, value2)" but I think it is
> still quiet a slack approach, especially since the select clauses will
> acutally be dynamic sql and my app is already in use but without the
> alternative column.
>
> So I have to rewrite all the dynamic sql statements and also instruct
> my customers to instead write "value" as part of a dynamic sql clause
> to insert a long case construct or a function call.
>
> So my question is, if I already have select clauses like
>
> select value, time from testvals
>
> can I somehow manipulate this query throught the use of a view or
> something to work out my column selection requirement?
>
> Any comments would be great,
>
> regards
>
> DC