sql server 2000 dynamic sql: passing tables' names 1

[duchovnick]

Hi,
The following code works OK

/*EXEC dynamic_table create v_9, f_9, 9*/

CREATE PROCEDURE dynamic_table_create @t1 VARCHAR(15), @t2 VARCHAR(15), @m VARCHAR(5)

AS
BEGIN TRANSACTION

DECLARE @myDynamicSQL VARCHAR(900)
SET @myDynamicSQL =
'
SELECT
accountno "accountno",
phone1 "phone1",
phone1 "phone2",
phone1 "phone3",
'+@m+' as [magazine]

INTO
'+@t2+'

FROM
mytable

'

EXEC(@myDynamicSQL)

COMMIT
GO

The trouble is that "mytable" should be inserte a parameter and i need to change it into "t1". When i write this code:

CREATE PROCEDURE dynamic_table_create @t1 VARCHAR(15), @t2 VARCHAR(15), @m VARCHAR(5)

AS
BEGIN TRANSACTION

DECLARE @myDynamicSQL VARCHAR(900)
SET @myDynamicSQL =
'

SELECT
accountno "accountno",
phone1 "phone1",
phone1 "phone2",
phone1 "phone3",
'+@m+' as [magazine]

INTO
'+@t2+'

FROM
'+@t1+'
'

EXEC(@myDynamicSQL)

COMMIT
GO

i get an error message saying:

Invalid object name 'v_9'

Any idea what can be wrong with me writing:

FROM
'+@t1+'

?
Hoping to find someone to help me with that !
Thanks !

 

[gmmastros]

I dunno why you are getting an error using V_9, but I do know that you should be using square brackets. Like this...

/*EXEC dynamic_table create v_9, f_9, 9*/

CREATE PROCEDURE dynamic_table_create @t1 VARCHAR(15), @t2 VARCHAR(15), @m VARCHAR(5)

AS
BEGIN TRANSACTION

DECLARE @myDynamicSQL VARCHAR(900)
SET @myDynamicSQL =
'
SELECT
accountno [accountno],
phone1 [phone1],
phone1 [phone2],
phone1 [phone3],
'+@m+' as [magazine]

INTO [!][[/!]'+@t2+'[!]][/!]

FROM
mytable

'

EXEC(@myDynamicSQL)

COMMIT
GO

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

I just noticed that the problem was with @t1, but that same thing applies.

CREATE PROCEDURE dynamic_table_create @t1 VARCHAR(15), @t2 VARCHAR(15), @m VARCHAR(5)

AS
BEGIN TRANSACTION

DECLARE @myDynamicSQL VARCHAR(900)
SET @myDynamicSQL =
'

SELECT
accountno "accountno",
phone1 "phone1",
phone1 "phone2",
phone1 "phone3",
'+@m+' as [magazine]

INTO
[!][[/!]'+@t2+'[!]][/!]

FROM
[!][[/!]'+@t1+'[!]][/!]
'

EXEC(@myDynamicSQL)

COMMIT
GO

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

do a print and paste output here

does v_9 exist?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[SQLBill]

Notice the first part of the code the poster supplied. They are executing the procedure with this:

EXEC dynamic_table create v_9, f_9, 9

-SQLBill

Posting advice: FAQ481-4875

 

[SQLDenis]

yes missing underscore between dynamic_table and create, good catch SQLBill

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[gmmastros]

Bill, are you suggesting that the space (between table and create) should actually be an underscore?



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

yes look

/*EXEC dynamic_table create v_9, f_9, 9*/

CREATE PROCEDURE dynamic_table_create

see they don't match

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[SQLBill]

Yes, they are only using @t2 in their procedure.

Run the way I showed it:
@t1 = create
@t2 = v_9
@t3 = f_9

Which might not exist yet. I'm guessing the poster is creating tables as they test and only have one table right now and it is f_9.

I'm surprised they don't get an error about too many parameters being supplied. But maybe SQL Server just overlooks any extra ones.

-SQLBill

Posting advice: FAQ481-4875

 

[duchovnick]

Hi gmmyastros,
and thanks. I always thought that dynamic sql parameters are revealed by: '+@param+' so what square brackets stand for ? I also thought that "select...into" goes to a name while the "from" indicates a table thats why the 2 parameters are of different sort. Anyway i cannot test the code at this moment and i hope it works when i get to the server. if it doesnt i may be bothering you again.
Thanks a lot !

 

[SQLDenis]

Lookup QUOTENAME in BOL

SELECT QUOTENAME('abc def')


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[duchovnick]

Hi SQLBill and SQLDenid,
I'm sorry, I omitted an underscore. First row is:

CREATE PROCEDURE dynamic_table_create @t1 VARCHAR(15), @t2 VARCHAR(15), @m VARCHAR(5)

and 3 parameters are: t1, t2 and m.
I execute it by sending 3 parameters:v_9, f_9, 9.
The error message is about the first param but the problem doesnt lay there because when i use "from" without a parameter i dont get an error message. The error is at the "from" and the table's name sent as parameter (v_9)
Thanks a lot !

 

[gmmastros]

The square brackets can be used around database objects. Sometimes, they need to be used. The important thing to remember is that it is ALWAYS safer to use them.

Square brackets can be used around database names, table names, column names and other things also. Here's an example of 2 queries that are the same.

Select *
From Table
Where Column = 1

Select *
From

Where [Column] = 1

It's good programming practice to NOT used reserved words for tables and column. Some developers don't always follow good programming practices. When a reserved word is used for a table or column, then you MUST surround it with square brackets.

Ex:

[tt][blue]Create Table [Create Table]
([Field Name] int, [select] int, [from] int)
[/blue][/tt]

If you run this, it will successfully create a table. This tables name would be Create Table and it would have 3 fields Field Name , select , and from .

Without the square brackets, it would look like...
[tt][blue]
Create Table Create Table
(Field Name int, select int, from int)[/blue][/tt]

This would not be valid syntax.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[duchovnick]

Thank you very much George