[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

SQL dynamique table name and column name as variable - Problem

ina

3/16/2007 10:32:00 AM

Hello guys,

I have a question. Is it possible to pass the table name and column
name as variables like this :

DECLARE @table sysname
DECLARE @tableID int
DECLARE @COLNAME varchar (200)

SET @tableID = 196532963
SET @table = dbo.UDF_TABLE_NAME (@tableID)

SELECT @COLNAME = dbo.udf_select_colname(tag) FROM @table

select @colname

I had problem with the table name
Server: Msg 137, Level 15, State 2, Line 9
Must declare the variable '@table'.

Any Suggestions?

Ina
11 Answers

DC

3/16/2007 10:37:00 AM

0

Hi,

I don't have you UDF's but this should work:

DECLARE @table sysname
DECLARE @tableID int
DECLARE @COLNAME varchar (200)
DECLARE @sql nvarchar(1023)

SET @tableID = 196532963
SET @table = dbo.UDF_TABLE_NAME(@tableID)

set @sql = 'SELECT @COLNAME = dbo.udf_select_colname(tag) FROM ' +
@table
exec sp_executesql @sql,
'@colname varchar(200)',
@colname = @colname

select @colname

Regards
DC

On 16 Mrz., 11:31, "ina" <roberta.inal...@gmail.com> wrote:
> Hello guys,
>
> I have a question. Is it possible to pass the table name and column
> name as variables like this :
>
> DECLARE @table sysname
> DECLARE @tableID int
> DECLARE @COLNAME varchar (200)
>
> SET @tableID = 196532963
> SET @table = dbo.UDF_TABLE_NAME (@tableID)
>
> SELECT @COLNAME = dbo.udf_select_colname(tag) FROM @table
>
> select @colname
>
> I had problem with the table name
> Server: Msg 137, Level 15, State 2, Line 9
> Must declare the variable '@table'.
>
> Any Suggestions?
>
> Ina

ina

3/16/2007 11:02:00 AM

0

On 16 Mar, 11:36, "DC" <d...@upsize.de> wrote:
> Hi,
>
> I don't have you UDF's but this should work:
>
> DECLARE @table sysname
> DECLARE @tableID int
> DECLARE @COLNAME varchar (200)
> DECLARE @sql nvarchar(1023)
>
> SET @tableID = 196532963
> SET @table = dbo.UDF_TABLE_NAME(@tableID)
>
> set @sql = 'SELECT @COLNAME = dbo.udf_select_colname(tag) FROM ' +
> @table
> exec sp_executesql @sql,
> '@colname varchar(200)',
> @colname = @colname
>
> select @colname
>
> Regards
> DC
>
> On 16 Mrz., 11:31, "ina" <roberta.inal...@gmail.com> wrote:
>
>
>
> > Hello guys,
>
> > I have a question. Is it possible to pass the table name and column
> > name as variables like this :
>
> > DECLARE @table sysname
> > DECLARE @tableID int
> > DECLARE @COLNAME varchar (200)
>
> > SET @tableID = 196532963
> > SET @table = dbo.UDF_TABLE_NAME (@tableID)
>
> > SELECT @COLNAME = dbo.udf_select_colname(tag) FROM @table
>
> > select @colname
>
> > I had problem with the table name
> > Server: Msg 137, Level 15, State 2, Line 9
> > Must declare the variable '@table'.
>
> > Any Suggestions?
>
> > Ina- Hide quoted text -
>
> - Show quoted text -

Thanks,

But I have this error
Procedure expects parameter '@parameters' of type 'ntext/nchar/
nvarchar'.

What it means?

:)

ina

Razvan Socol

3/16/2007 11:46:00 AM

0

Hi, ina

Try:

[...]
exec sp_executesql @sql,
N'@colname varchar(200) OUTPUT',
@colname = @colname OUTPUT

Razvan

DC

3/16/2007 2:45:00 PM

0

This embarassing burst of errors boldly displays that I am unable to
get two lines of code straight unless query analyser jumps into my
face on any error. Thanks for putting it right, Razvan.

On 16 Mrz., 12:46, "Razvan Socol" <rso...@gmail.com> wrote:
> Hi, ina
>
> Try:
>
> [...]
> exec sp_executesql @sql,
> N'@colname varchar(200) OUTPUT',
> @colname = @colname OUTPUT
>
> Razvan

--CELKO--

3/16/2007 2:52:00 PM

0

>> Is it possible to pass the table name and column name as variables like this : <<

Why would you ever want to write such bad code?? You never had a
course in basic software engineering, have you? YOu might want to do
that immediately and pay attention to the parts about coupling and
cohesion -- that will be covered in the first few weeks because it is
so fundamental.

Oh yes, the old "Britney Spearas, Automoible and Squid" code Module!!

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. And
add a comment that you are a bad programmer and this needs to be
replaced as soon as possible.

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. When you have many tables that model the
same entity, then you have a magnetic tape file system written in SQL,
and not an RDBMS at all.

If the tables are different, then having a generic procedure which
works equally on automobiles, octopi or Britney Spear's discology is
saying that your application is 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.

DC

3/16/2007 3:13:00 PM

0

While we're at it, Mr Celko: I adopted your identity approach from
"SQL for Smarties" and I
stumbled when I needed to do an INSERT... SELECT. This does not work
because obviously the max() statement is only being evaluated once:

insert into foo (pk, data)
select coalesce((select max(pk) + 1 from foo), 1), bar.data
from bar


I am in doubt about the "not using identity" directive, or am I
missing some point?


Regards
DC


On 26 Feb., 21:28, "--CELKO--" <jcelko


On 16 Mrz., 15:52, "--CELKO--" <jcelko...@earthlink.net> wrote:
> >> Is it possible to pass the table name and column name as variables like this : <<
>
> Why would you ever want to write such bad code?? You never had a
> course in basic software engineering, have you? YOu might want to do
> that immediately and pay attention to the parts about coupling and
> cohesion -- that will be covered in the first few weeks because it is
> so fundamental.
>
> Oh yes, the old "Britney Spearas, Automoible and Squid" code Module!!
>
> 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. And
> add a comment that you are a bad programmer and this needs to be
> replaced as soon as possible.
>
> 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. When you have many tables that model the
> same entity, then you have a magnetic tape file system written in SQL,
> and not an RDBMS at all.
>
> If the tables are different, then having a generic procedure which
> works equally on automobiles, octopi or Britney Spear's discology is
> saying that your application is 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.

Tony Rogerson

3/16/2007 3:26:00 PM

0

Not only that but I doubt he mentions that using MAX(pk)+1 in an INSERT
statement yeilds duplicates if multiple connections are concurrently
inserting rows.

MAX(..)+1 is a very bad approach for adding a next number approach.

I'll let Celko get you out of the mess he has got you in, but if he fails
then send me mail directly and I'll help.

Tony

"DC" <dc@upsize.de> wrote in message
news:1174058000.436989.116730@b75g2000hsg.googlegroups.com...
> While we're at it, Mr Celko: I adopted your identity approach from
> "SQL for Smarties" and I
> stumbled when I needed to do an INSERT... SELECT. This does not work
> because obviously the max() statement is only being evaluated once:
>
> insert into foo (pk, data)
> select coalesce((select max(pk) + 1 from foo), 1), bar.data
> from bar
>
>
> I am in doubt about the "not using identity" directive, or am I
> missing some point?
>
>
> Regards
> DC
>
>
> On 26 Feb., 21:28, "--CELKO--" <jcelko
>
>
> On 16 Mrz., 15:52, "--CELKO--" <jcelko...@earthlink.net> wrote:
>> >> Is it possible to pass the table name and column name as variables
>> >> like this : <<
>>
>> Why would you ever want to write such bad code?? You never had a
>> course in basic software engineering, have you? YOu might want to do
>> that immediately and pay attention to the parts about coupling and
>> cohesion -- that will be covered in the first few weeks because it is
>> so fundamental.
>>
>> Oh yes, the old "Britney Spearas, Automoible and Squid" code Module!!
>>
>> 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. And
>> add a comment that you are a bad programmer and this needs to be
>> replaced as soon as possible.
>>
>> 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. When you have many tables that model the
>> same entity, then you have a magnetic tape file system written in SQL,
>> and not an RDBMS at all.
>>
>> If the tables are different, then having a generic procedure which
>> works equally on automobiles, octopi or Britney Spear's discology is
>> saying that your application is 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.
>
>

ina

3/16/2007 5:35:00 PM

0

On 16 Mar, 16:26, "Tony Rogerson" <tonyroger...@torver.net> wrote:
> Not only that but I doubt he mentions that using MAX(pk)+1 in an INSERT
> statement yeilds duplicates if multiple connections are concurrently
> inserting rows.
>
> MAX(..)+1 is a very bad approach for adding a next number approach.
>
> I'll let Celko get you out of the mess he has got you in, but if he fails
> then send me mail directly and I'll help.
>
> Tony
>
> "DC" <d...@upsize.de> wrote in message
>
> news:1174058000.436989.116730@b75g2000hsg.googlegroups.com...
>
>
>
> > While we're at it, Mr Celko: I adopted your identity approach from
> > "SQL for Smarties" and I
> > stumbled when I needed to do an INSERT... SELECT. This does not work
> > because obviously the max() statement is only being evaluated once:
>
> > insert into foo (pk, data)
> > select coalesce((select max(pk) + 1 from foo), 1), bar.data
> > from bar
>
> > I am in doubt about the "not using identity" directive, or am I
> > missing some point?
>
> > Regards
> > DC
>
> > On 26 Feb., 21:28, "--CELKO--" <jcelko
>
> > On 16 Mrz., 15:52, "--CELKO--" <jcelko...@earthlink.net> wrote:
> >> >> Is it possible to pass the table name and column name as variables
> >> >> like this : <<
>
> >> Why would you ever want to write such bad code?? You never had a
> >> course in basic software engineering, have you? YOu might want to do
> >> that immediately and pay attention to the parts about coupling and
> >> cohesion -- that will be covered in the first few weeks because it is
> >> so fundamental.
>
> >> Oh yes, the old "Britney Spearas, Automoible and Squid" code Module!!
>
> >> 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. And
> >> add a comment that you are a bad programmer and this needs to be
> >> replaced as soon as possible.
>
> >> 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. When you have many tables that model the
> >> same entity, then you have a magnetic tape file system written in SQL,
> >> and not an RDBMS at all.
>
> >> If the tables are different, then having a generic procedure which
> >> works equally on automobiles, octopi or Britney Spear's discology is
> >> saying that your application is 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.- Hide quoted text -
>
> - Show quoted text -


Thanks for all this information.

Ina

--CELKO--

3/17/2007 2:24:00 PM

0

>> This does not work because obviously themax() statement is only being evaluated once:

INSERT INTO BaseTable (pk, data)
SELECT COALESCE((SELECT MAX(pk) + 1 FROM BaseTable), 1),
WorkingTable.data
FROM WorkingTable;

See if this skeleton will help. Use a scalar subquery to pull up the
current maximum key, then use the OLAP function ROW_NUMBER() to sort
the working data with the new data on some column(s). Finally, insert
the entire working table into the basr table.

INSERT INTO BaseTable (key_col, sort_col, ..)
SELECT COALESCE ((SELECT MAX(key_col) FROM BaseTable), 0)
+ ROW_NUMBER()OVER (ORDER BY W.sort_col), ..
FROM WorkingTable AS W
WHERE ..

Since ROW_NUMBER() starts with 1, you coalesce an empty table count to
zero. The hard part is in the ORDER BY; you will need to pick a good
rule for assigning the numbers so you can validate the results.
Random numbering is bad design.

Tony Rogerson

3/17/2007 3:32:00 PM

0

Wrong - you totally miss the point and the problem.

The SELECT query is executed first and a result set formed and then
exclusive locks are applied as part of the INSERT, so on a multiple user
system if more than one connection executes the MAX ( ) they get the same
result and a PK violation results.

I'm suprised in the 35 years you have not come across this as its the
default behaviour in SQL Server and always has been.

Why did you not know this? Why post a solution that still suffers from the
problem?


"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1174141444.859221.109310@y66g2000hsf.googlegroups.com...
>>> This does not work because obviously themax() statement is only being
>>> evaluated once:
>
> INSERT INTO BaseTable (pk, data)
> SELECT COALESCE((SELECT MAX(pk) + 1 FROM BaseTable), 1),
> WorkingTable.data
> FROM WorkingTable;
>
> See if this skeleton will help. Use a scalar subquery to pull up the
> current maximum key, then use the OLAP function ROW_NUMBER() to sort
> the working data with the new data on some column(s). Finally, insert
> the entire working table into the basr table.
>
> INSERT INTO BaseTable (key_col, sort_col, ..)
> SELECT COALESCE ((SELECT MAX(key_col) FROM BaseTable), 0)
> + ROW_NUMBER()OVER (ORDER BY W.sort_col), ..
> FROM WorkingTable AS W
> WHERE ..
>
> Since ROW_NUMBER() starts with 1, you coalesce an empty table count to
> zero. The hard part is in the ORDER BY; you will need to pick a good
> rule for assigning the numbers so you can validate the results.
> Random numbering is bad design.
>