SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
Search:  
 
Home Articles Editorials Forums Scripts Blogs QotD Books SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Alter Table within a Stored Procedure

Alter Table within a Stored Procedure Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
madala
Posted Monday, May 19, 2008 7:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 29, 2009 7:36 AM
Points: 13, Visits: 234
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
Post #502806
Steve Jones - Editor
Posted Monday, May 19, 2008 8:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: Administrators
Last Login: Today @ 11:21 AM
Points: 19,448, Visits: 5,033
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?
Post #502846
Grant Fritchey
Posted Monday, May 19, 2008 10:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:20 PM
Points: 5,822, Visits: 10,705
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
The Scary DBA
Author of SQL Server 2008 Query Performance Tuning Distilled

For better & quicker help read:
How to Post Performance Problems
Post #502975
madala
Posted Monday, May 19, 2008 10:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 29, 2009 7:36 AM
Points: 13, Visits: 234
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'
Post #503292
« Prev Topic | Next Topic »


Permissions Expand / Collapse

 
 
Copyright © 2002-2009 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use