Click here to Skip to main content
15,903,203 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
How to modify the view when the database name and SQL command are variables
An example as below:
declare @sqlcommand nvarchar(max),@db_py nvarchar(50)
set @db_py='PY202'
set @sqlcommand='alter view test as
select * from PY_S01
union all
select * from PY_S02'
exec(N'use '''+@db_py+''';exec sp_executesql N'''+@sqlcommand+'''')

What I have tried:

1.I change the last commmand to
exec(N'use PY202;exec sp_executesql N'''+@sqlcommand+'''')
execute successfull!
2.set @sqlcommand='use '+@db_py+char(10)+'GO'+char(10)+'alter view test as
select * from PY_S01
union all
select * from PY_S02'
exec(@sqlcommand)

execute fail
but i print @sqlcommand and copy the result to execute ,success
Posted
Updated 1-Aug-18 19:36pm
Comments
Santosh kumar Pithani 2-Aug-18 1:08am    
AS i know "Go" statement is creating the issue due to "Go" in query line.

1 solution

SQL
--i hope your view name is same and existed in all db .If yes, below query will help you.
DECLARE @sqlcommand NVARCHAR(MAX),@db_py NVARCHAR(50)
SET @db_py='PY202'
SET @sqlcommand='USE '+QUOTENAME(@db_py)+' 
                  GO
				 ALTER VIEW test AS
                    SELECT * FROM PY_S01
                     UNION ALL
                    SELECT * FROM PY_S02';

--you can directly use DB name in alter view command as like below query.

SET @sqlcommand='ALTER VIEW test AS SELECT * FROM PY_S01  UNION ALL SELECT * FROM PY_S02';
SET @sqlcommand=REPLACE(@sqlcommand,'ALTER VIEW test','ALTER VIEW'+SPACE(1)+QUOTENAME(@db_py)+'.DBO.test ');

EXEC(@sqlcommand)
 
Share this answer
 
Comments
Santosh kumar Pithani 2-Aug-18 1:43am    
i want to add one more thing i.e If you add columns on table but its not reflected on views so in this case you have use :exec sp_refreshview command.
Member 13908127 2-Aug-18 4:52am    
Thanks for your advice,i have solved this issue,is a question of quotation mark
exec(N'use '+@db_py+';exec sp_executesql N'''+@sqlcommand
Santosh kumar Pithani 2-Aug-18 8:25am    
Always Welcome!

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900