How to add a column in a table using stored procedure ?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

hi i want to add column in a table using Stored procedure but when i am trying getting error

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter @statement; of type ;ntext/nchar/nvarchar;

What I have tried:

SQL

alter proc SpgetEMP
as
begin
exec sp_executeSQL  'alter table emp add adrs varchar(20) null'
end

Posted 25-Nov-16 7:03am

Sql_Lover_rahul_Singh

Updated 25-Nov-16 7:20am

Add a Solution

Comments

Richard Deeming 25-Nov-16 14:10pm

That procedure will only work once. As soon as it works, the next time you call it you'll get an error telling you that the column already exists.

Modifying the database structure from a stored procedure is almost always a sign of a bad design.

And you application should not be connecting as a user which has permissions to modify the structure of the database. It should be connecting as a user which has the minimum permissions necessary for your application to run.

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

manu_dhobale · Accepted Answer · 2016-11-25T07:20:00

Solution 1

Modify Proc as below

SQL

declare @statement as nvarchar(100);
set @statement='alter table emp add adrs varchar(20) null'
exec sp_executeSQL @statement

Posted 25-Nov-16 7:20am

manu_dhobale

Comments

Sql_Lover_rahul_Singh 26-Nov-16 12:22pm

Thanks its is work :)

How to add a column in a table using stored procedure ?

1 solution

Solution 1

Add your solution here

Preview 0

How to add a column in a table using stored procedure ?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us