sql server 2000, problems with dynamic sql 2

[duchovnick]

Hi,
I'm having problems with dynamic sql.
I have a table which name i send as a parameter to a stored procedure that runs dynamic sql
code.
I also send a number as a second parameter to that procedure.
What i want the procedure to do is to create a new table that would contain as many rows as
that of which i sent the name as parameter and fill one column with the number i sent as 2nd
parameter.
I wrote the following code:

/*EXEC normalize_old j_9,9*/
ALTER PROCEDURE normalize_old @oldy VARCHAR(20), @magazine VARCHAR(10)
AS
BEGIN TRANSACTION


DECLARE @mydynamicSQL VARCHAR(3000)
SET @mydynamicSQL=
'
SELECT
['+@magazine+']

into
mytable

FROM
['+@oldy+'] 
'
exec(@mydynamicSQL)

COMMIT
GO

Running it i get the following error message:

Invalid column name '9'.

Which is the second param i sent.
Can anyone tell me please how to get rid of that error message ?
Thanks a lot !

 

[bborissov]

You got exactly what you ask. Your query look like this:

SELECT 9 into mytable FROM j_9

You must pass the column name as parameter. If the table j_9 has a column name [Test9] you must pass it:

EXEC normalize_old 'j_9', 'Test9'

The the query will look like this:

SELECT Test9 into mytable FROM j_9

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[philhege]

You'll have to start the field name with something other than a number, like

SELECT
[mag_'+@magazine+']


Search for "Rules for Regular Identifiers" in BOL for details.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[duchovnick]

Thanks a lot !