dynamic sql server statements

In stright t-sql or sql server 2000 stored procdures, I was wondering if you
can setup dynamic sql statments.
Basically I am going to have a sql server 2000 control table where users
can select via a web page, what columns they want to see with their own
unique (personal) sql server 2000. The control table will be setup to show
what columns
users want to display on their own individual sql server 2000 tables.
Thus basically if a user selects that they want to create a table with
columns #1, #9 and #16 from an option control table, I am wondering if there
is a way to do is a way to set this up with t-sql statements?
Thanks!There is, but there is certainly no magic way to do it. Are you talking
about loads of data? If the volume is relatively low, then I would just use
a regular proc and toss the results the user doesn't want to see.
Otherwise I would save in your settings the string value of the columns and
execute something like:
set @.query = 'select ' + @.columnList + ' from table'
And use sp_executeSQL to execute it. I expect you have a where clause too,
so you would add that.
You could then have a table like (you suggest it too):
create table userScreenPref
(
userId sysname,
screenName varchar(20),
columnList varchar(2000)
constraint PKuserScreenPref primary key (userId, columnList)
) --not exactly tested, but you get the point
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Wendy Elizabeth" <WendyElizabeth@.discussions.microsoft.com> wrote in
message news:603A78FD-D327-4263-8991-4340E6A5F9A5@.microsoft.com...
> In stright t-sql or sql server 2000 stored procdures, I was wondering if
> you
> can setup dynamic sql statments.
> Basically I am going to have a sql server 2000 control table where users
> can select via a web page, what columns they want to see with their own
> unique (personal) sql server 2000. The control table will be setup to show
> what columns
> users want to display on their own individual sql server 2000 tables.
> Thus basically if a user selects that they want to create a table with
> columns #1, #9 and #16 from an option control table, I am wondering if
> there
> is a way to do is a way to set this up with t-sql statements?
> Thanks!