Create View on Condition

[peterb58]

I have been handed a bunch of scripts which were written a couple of years ago. They are falling at the first hurdle and I don't have the experiences to understand why. I normally work with Sybase SQL Anywhere, but MSSQLServer new area for me.

This attempt to create a view dependant on a variable is failing with 'syntax error near keyword view'.
It si being run in SQLServer2005. The view itself will execure if run independantly, it is just the fact that it is in an If - Else clause that seems to be a problem.

Many thanks

Pete

*********************************************************
use [Core];

if (select VarValue from [Variables] where VarName = 'UseAreas') = 'True'
begin
create view [dbo].[XV_Authorities] as
select convert(varchar(12),[AreaID]+100)+','''+[AreaDesc]+'''' as DataOut
from [dbo].[Area]
where AreaID > 1
end
else
begin
create view [dbo].[XV_Authorities] as
select convert(varchar(12),[RegionID]+100)+','''+[RegionDesc]+'''' as DataOut
from [dbo].[Region]
where RegionID > 1
end;

 

[born2program]

Try the following code. I'm assuming if there are any variables with "UseAreas" as the VarName then create view 1, if not then create view2.

use [Core];

BEGIN

declare @sql1 as nvarchar(max);
declare @sql2 as nvarchar(max);


set @sql1='create view [dbo].[XV_Authorities] as '
set @sql1=@sql1+'select convert(varchar(12),[AreaID]+100)+'',''+[AreaDesc] as DataOut '
set @sql1=@sql1+'from [dbo].[Area] '
set @sql1=@sql1+'where AreaID > 1'

set @sql2='create view [dbo].[XV_Authorities] as '
set @sql2=@sql2+'select convert(varchar(12),[RegionID]+100)+'',''+[RegionDesc] as DataOut '
set @sql2=@sql2+'from [dbo].[Region] '
set @sql2=@sql2+'where RegionID > 1'


if EXISTS(select VarValue from [Variables] where VarName = 'UseAreas')
	BEGIN
		exec(@sql1)
	END
ELSE
	BEGIN
		exec(@sql2)
	END		
		
END