SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Dynamic Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shanmugaraj
Posting Yak Master

219 Posts

Posted - 04/17/2013 :  03:15:35  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
help me on fine tuning the sp

/*
SELECT Name, dept_id, col_Name FROM temp_main
SELECT Name FROM temp_dynamic

exec sp_alter_table_column (Name,dept_id,salary,Age)

*/
create procedure sp_alter_table_column (@csv_column nvarchar(max))
as
declare @str_col_main nvarchar(max), @str_col_dynamic nvarchar(max) , @str_sql varchar(max)

begin

Select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='temp_main'

Select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='temp_dynamic'

--if the new column name are not found in temp_main, add new column in temp_dynamic . make sure temp_dynamic also doesnot have the new column

@str_sql = "Alter table temp_dynamic Add Column Dept_Id int NOT NULL, Desig_Id int NOT NULL"
exec @strsql
end

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 04/17/2013 :  08:54:31  Show Profile  Reply with Quote
I didn't quite follow what you are trying to do, and why you are trying to do it - but could it be something like this?
create procedure sp_alter_table_column (@csv_column nvarchar(max))
as
declare @str_col_main nvarchar(max), @str_col_dynamic nvarchar(max) , @str_sql varchar(max)


if not exists
(	select * from INFORMATION_SCHEMA.COLUMNS 
	where TABLE_NAME='temp_main' and column_name = @csv_column 
)
and not exists 
(	select * from INFORMATION_SCHEMA.COLUMNS 
	where TABLE_NAME='temp_dynamic' and column_name = @csv_column 
)
begin
	set @str_sql = 'Alter table temp_dynamic Add Column ''' + @csv_column + ''' int NOT NULL, Desig_Id int NOT NULL';
	exec @str_sql
end
Go to Top of Page

shanmugaraj
Posting Yak Master

219 Posts

Posted - 04/17/2013 :  09:54:11  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
Ok fine

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000