|
|
|
Author |
Message |
Bu7ch
|
Posted: Thu Aug 25 05:18:14 CDT 2005 |
Top |
SQL Server Developer >> Table name as a variable
Hi,
I am creating a function.
Depending on a value entered I will select records from one of 2 Database
tables.
Instead of writing the code below, can i use a variable for the table name?
BEGIN
Select * from Customers
END
ELSE
BEGIN
Select * from Clients
END
So is it posssible to somehow do the code below instead?
thank you!
CodeRazor
SQL Server58
|
|
|
|
|
Madhivanan
|
Posted: Thu Aug 25 05:18:14 CDT 2005 |
Top |
SQL Server Developer >> Table name as a variable
Use Dynamic SQL
Madhivanan
|
|
|
|
|
Tibor
|
Posted: Thu Aug 25 05:34:42 CDT 2005 |
Top |
|
|
|
CodeRazor
|
Posted: Thu Aug 25 05:46:02 CDT 2005 |
Top |
SQL Server Developer >> Table name as a variable
Thank you Tibor and Madhivanan,
both your answers have helped me in what i am trying to do
cheers,
CR
|
|
|
|
|
Hasan
|
Posted: Thu Aug 25 10:08:10 CDT 2005 |
Top |
|
|
|
--CELKO--
|
Posted: Thu Aug 25 10:14:12 CDT 2005 |
Top |
SQL Server Developer >> Table name as a variable
This is a very poor programming practice. Remember coupling and
cohesion in your very first software engineering class??
The short answer is use slow, proprietrary dynamic SQL to kludge a
query together on the fly with your table name in the FROM clause.
The right answer is never pass a table name as a parameter. You need
to understand the basic idea of a data model and what a table means in
implementing a data model. Go back to basics. What is a table? A
model of a set of entities or relationships. EACH TABLE SHOULD BE A
DIFFERENT KIND OF ENTITY. What having a generic procedure works
equally on automobiles, octopi or Britney Spear's discology is saying
that your applications a disaster of design.
1) This is dangerous because some user can insert pretty much whatever
they wish -- consider the string 'Foobar; DELETE FROM Foobar; SELECT *
FROM Floob' in your statement string.
2) It says that you have no idea what you are doing, so you are giving
control of the application to any user, present or future. Remember
the basics of Software Engineering? Modules need weak coupling and
strong cohesion, etc. This is far more fundamental than just SQL; it
has to do with learning to programming at all.
3) If you have tables with the same structure which represent the same
kind of entities, then your schema is not orthogonal. Look up what
Chris Date has to say about this design flaw. Look up the term
attribute splitting.
4) You might have failed to tell the difference between data and
meta-data. The SQL engine has routines for that stuff and applications
do not work at that level, if you want to have any data integrity.
Yes, you can write a program with dynamic SQL to kludge something like
this. It will last about a year in production and then your data
integrity is shot.
|
|
|
|
|
Tibor
|
Posted: Thu Aug 25 10:21:45 CDT 2005 |
Top |
|
|
|
Aaron
|
Posted: Thu Aug 25 10:33:12 CDT 2005 |
Top |
SQL Server Developer >> Table name as a variable
> the correct form of the following procedure
Let's recap what CodeRazor said.
"I am creating a function."
let us know how it goes.
|
|
|
|
|
Hasan
|
Posted: Fri Aug 26 01:04:45 CDT 2005 |
Top |
SQL Server Developer >> Table name as a variable
sorry , you are right i have passed it .
accept it . But when using with sysname it accepts.
the difference between them is sysname is a type of nvarchar and length is
128 default. What is the difference ? and why sql server doesnt accept?
> Did you read the original post? The OP wants to use dynamic SQL in a
> *function*. Have you tried that? Can you post a user defined function
> which uses dynamic SQL? :-)
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
>
>> Hi ,
>>
>> the correct form of the following procedure is the
>>
>>
>>
>> there is no way Sql Server halts us :)
>>
>>
>>
>>
>>> Dynamic SQL is not allowed in a function. This is because SQL Server
>>> really need to know how the resultset is to look like when it is to
>>> execute the function. There would be now way of knowing that for SQL
>>> Server is dynamic SQL was allowed.
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/default.asp
>>> http://www.solidqualitylearning.com/
>>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>>
>>>
>>>> Use Dynamic SQL
>>>>
>>>>
>>>> Madhivanan
>>>>
>>>
>>
>>
>
|
|
|
|
|
Tibor
|
Posted: Fri Aug 26 03:19:03 CDT 2005 |
Top |
|
|
|
|
|