Pages

Wednesday, May 19, 2010

alter-table-using-stored-procedure

Views
Alter table can't take dynamic parameters. To do this you will have to concatenate the whole TSQL command as a string dynamically and then execute it. Also you don't need the COLUMN keyword.

tsql Syntax (Toggle Plain Text)
  1.  CREATE PROCEDURE sp_addcolumn 
  2.  -- Add the parameters for the stored procedure here
  3.  @tablename VARCHAR(50) = 0, 
  4.  @columnname VARCHAR(50) = 0,
  5.  @datatype VARCHAR (50) = 0
  6.  
  7. AS
  8.  
  9. SET @tsql = 'ALTER TABLE [' + @tablename + '] ADD [' + @columnname + '] ' + @datatype 
  10.  
  11. EXEC(@tsql)




MS SQL Syntax (Toggle Plain Text)
  1. CREATE PROCEDURE sp_addcolumn 
  2.  -- Add the parameters for the stored procedure here
  3.  @TABLE_NAME varchar(50) = 0, 
  4.  @columnname varchar(50) = 0,
  5.  @datatype varchar (50) = 0
  6.  
  7. AS
  8. DECLARE @tsql varchar (200)
  9. SET @tsql = 'ALTER TABLE [ '+@TABLE_NAME+'] ADD ['+@columnname+@datatype+']'
  10. EXEC(@tsql)





I now have this:-
MS SQL Syntax (Toggle Plain Text)
  1. CREATE PROCEDURE sp_addcolumn 
  2.  -- Add the parameters for the stored procedure here
  3.  @TABLE_NAME varchar(50) = 0, 
  4.  @columnname varchar(50) = 0,
  5.  @datatype varchar (50) = 0
  6.  
  7. AS
  8. DECLARE @tsql varchar (200)
  9. SET @tsql = 'ALTER TABLE [ '+@TABLE_NAME+'] ADD ['+@columnname+']' + @datatype
  10. EXEC(@tsql)



Posted by Unknown at 7:54 AM
Labels:

0 comments:

Post a Comment

 

Web Design Company karimnagar, Web Designing warangal, Logo Design Company nizamabad, Indian Website Design Company, maddysoft.co.in