Alter Table within a Stored Procedure

Question

Post reply

Alter Table within a Stored Procedure

madala

SSC Veteran

Points: 261
More actions
May 19, 2008 at 7:47 am

#182575

I have the following Stored Procedure. When I run it, it executes without errors, but it does not alter the table to add the new column? Any ideas?
CREATE PROCEDURE sp_MyStoredProcedure AS
BEGIN
exec sp_executesql 'alter table MyTable add dte datetime null'
END

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

First I think you need

... sp_executeSQL N' alter table ...'

And it works for me, SS2K5, dev, SP2.

Columns added to table.

Do you have permissions to add columns?

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 2

It should work. Are you sure you're in the right database? Are you looking through Management Studio? You might need to refresh in order to see the change.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

madala SSC Veteran Points: 261 More actions · Answer 3

Thanks Steve,

Indeed the N is needed before the string otherwise you get the following error:

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'

Bob Pinella SSCommitted Points: 1737 More actions · Answer 4

Also, on a side note, it is not recommended to use the prefix "sp" for user-defined stored procedures. This prefix is used with system stored procedures and can affect performance by causing an extra lookup in the master database. You can use whatever naming conventions you feel comfortable with but the common convention is to use "usp". I know that you gave that name just as an example, but just in case.....

Bob Pinella