Re: Using variables in Select Statements

On Thu, 21 Jul 2005 06:36:04 -0700, Dunner wrote:

>Hi guys,
>
>I was wondering if it is possible to use a variable in the from part of a
>select statement as in...
>
>select *
>from @myVariable
>
>Basically I am writing stored procedure that the user passes a table name to
>and then it goes off and grabs info from this table and does some funky
>stuff. Is it possible to make the statements semi-dynamic in that manner?
>
>Cheers

Hi Dunner,

The short answer is: no this is not possible.

The slightly longer answer is: you can use dynamic SQL to kludge your
way around this limitation - lookup EXECUTE and sp_executesql in Books
Online if you must.

The long answer is that you should not want to do this. Each table will
have a structure that is different from other tables: the number of
columns, their names and datatypes, the keys and the other constraints
will all be different. I'm not sure how you propose to do "some funky
stuff" on rows without even knowing the number of the columns or their
names and datatypes, but I do hope that I'll never get to maintain the
nightmare.

In most cases, questions like yours are an indication of a design flaw.
If you can post some more information about your tables and your
business problem, then maybe I or others can propose a better design.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
.