Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ahmedauk
Starting Member
4 Posts |
 Posted - 2007-03-29 : 12:38:17
|
| Hi,Can anyone help with this query. I'm hitting a problem when i try to use the alter table statement (adding a column) within a declared string. Code below - is there an easy way round this?Thanks,AA.-----------------------------------------CREATE PROCEDURE Process_SP@This_Month_File varchar(200)ASset nocount ondeclare @sqla as varchar(8000)SET @SQLa = 'select substring(col001,1,12) as col001 into '+@This_Month_File+'_v2 from '+@This_Month_File+'delete from '+@This_Month_File+'_v2 where col001 = '' ''delete from '+@This_Month_File+'_v2 where len(col001) <> 12delete from '+@This_Month_File+'_v2 where left(col001,2) in (''PA'',''#0'',''`2'')alter table @This_Month_File_v2 add nid int identity (1,1)select col001, max(nid) as nid into '+@This_Month_File+'_v3from '+@This_Month_File+'_v2 group by col001alter table '+@This_Month_File+'_v3 drop column nid'exec(@sqla)GOexecutable code:exec Process_SP @This_Month_File = POL0703 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-03-29 : 12:48:51
|
do a PRINT @sqlabefore exec(@sqla) and check the code. Post the output here with the error messages
KH |
 |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-03-29 : 12:49:08
|
| couple of things:(1) Use quotes around the string when you pass the value. exec Process_SP@This_Month_File = 'POL0703'(2) This line : >>alter table @This_Month_File_v2 add nid int identity (1,1)is incorrect. You need to concatenate. If you are trying to create a table variable then its not possible using dynamic sql. The table variable will be out of scope in the dynamic sql. Finally, you have more than one SQL statements in the dynamic SQL. You are better off EXEC'ing each one individually. ************************Life is short. Enjoy it.************************ |
|
|
|
ahmedauk
Starting Member
4 Posts |
Posted - 2007-03-30 : 04:43:17
|
thanks for that, makes sense. if i stick to the code as it is, i assume i'll have to create the table/structure and insert into, rather than trying to alter the table!! i'm sure i'll find a way around it. thanks.quote:
Originally posted by dinakar
couple of things:(1) Use quotes around the string when you pass the value. exec Process_SP@This_Month_File = 'POL0703'(2) This line : >>alter table @This_Month_File_v2 add nid int identity (1,1)is incorrect. You need to concatenate. If you are trying to create a table variable then its not possible using dynamic sql. The table variable will be out of scope in the dynamic sql. Finally, you have more than one SQL statements in the dynamic SQL. You are better off EXEC'ing each one individually. ************************Life is short. Enjoy it.************************
|
|
|
|
|
|
|
|
|
Can anyone help with this query please??