Dynamic Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic Query

Author

Topic

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-04-17 : 03:15:35

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-17 : 08:54:31

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

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-04-17 : 09:54:11

Ok fine

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources