April 19, 2010 at 2:01 pm
Hi All,
This might be a basic thing i am overlooking but i have a Stored procedure in which i have some
ALTER TABLE myTable add col1 int
ALTER TABLE myTable add col2 int
statements. I then use a insert statement right after the alter statements but i get a Col1 not found error. If i put a GO after the ALTER table statements, my variables declared at the start go out of scope.
What's a workaround for this?
Thanks
April 19, 2010 at 2:21 pm
May I ask for the business reason for that requirement?
It sounds like a denormalized table (e.g. a monthly summary table with one column per month...)...
April 19, 2010 at 6:59 pm
Its nothing complicated, simply a previous developer left code which i am converting to a stored proc. it has 2 parts and not all fields can be present during the first part. i could simply create a separate table before the stored proc but i thought i'd ask if its simply a basic thing i am overlooking or is it a bad coding principle to do so
April 19, 2010 at 11:04 pm
It could be done with a little dynamic SQL provided that either the dynamic SQL was protected from SQL Injection or simply had nothing to do with any external parameters.
But, I have to agree... adding columns to a table in a stored procedure usually isn't the best idea. If you told us a bit more about why that's necessary, someone might be able to come up with a work around for you.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 21, 2010 at 6:43 am
You can use this
EXEC sp_executesql N'ALTER TABLE myTable add col1 int'
but a better solution would be to modify the "part 1" code so you don't need to do this.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy