March 19, 2015 at 9:18 am
I'm having trouble getting the following syntax to work correctly... I need to find out if a VIEW exists. If it does, I need to alter the syntax, if it doesn't, I need to create the view.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_equipment]') and OBJECTPROPERTY(id, N'IsView') = 1)
BEGIN
ALTER VIEW [dbo].[v_equipment]
AS SELECT a.acct_nbr,
a.name,
b.id,
b.tmde_id,
b.acct_id,
b.instrument_id,
b.serial_nbr,
b.dept AS dept_a,
b.location,
b.on_site,
c.cal_procedure,
b.cal_interval,
b.cal_cycle,
c.noun,
c.model,
c.mfr,
c.accuracy,
c.dept,
c.category,
c.class,
c.data_required AS data_included,
d.description
FROM dbo.account AS a
INNER JOIN dbo.equipment AS b
ON b.acct_id = a.id
INNER JOIN dbo.tmde AS c
ON b.tmde_id = c.id
LEFT JOIN dbo.price_code AS d
ON c.type_id = d.id;
END
ELSE
BEGIN
CREATE VIEW [dbo].[v_equipment]
AS SELECT a.acct_nbr,
a.name,
b.id,
b.tmde_id,
b.acct_id,
b.instrument_id,
b.serial_nbr,
b.dept AS dept_a,
b.location,
b.on_site,
c.cal_procedure,
b.cal_interval,
b.cal_cycle,
c.noun,
c.model,
c.mfr,
c.accuracy,
c.dept,
c.category,
c.class,
c.data_required AS data_included,
d.description
FROM dbo.account AS a
INNER JOIN dbo.equipment AS b
ON b.acct_id = a.id
INNER JOIN dbo.tmde AS c
ON b.tmde_id = c.id
LEFT JOIN dbo.price_code AS d
ON c.type_id = d.id;
END
GO
March 19, 2015 at 9:33 am
ALTER VIEW and CREATE VIEW need to be the first/only statements in a batch, so the IF..ELSE construct won't work here.
The usual way to do this is to drop the view if it exists and then (in a separate batch, after a GO) create the view.
March 19, 2015 at 9:37 am
TracyLamb0902 (3/19/2015)
I'm having trouble getting the following syntax to work correctly... I need to find out if a VIEW exists. If it does, I need to alter the syntax, if it doesn't, I need to create the view.
You can't do it quite like you posted. The typical pattern is to check for existence and drop. Then just always create.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_equipment]') and OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW v_equipment
GO
CREATE VIEW [dbo].[v_equipment]
AS SELECT a.acct_nbr,
a.name,
b.id,
b.tmde_id,
b.acct_id,
b.instrument_id,
b.serial_nbr,
b.dept AS dept_a,
b.location,
b.on_site,
c.cal_procedure,
b.cal_interval,
b.cal_cycle,
c.noun,
c.model,
c.mfr,
c.accuracy,
c.dept,
c.category,
c.class,
c.data_required AS data_included,
d.description
FROM dbo.account AS a
INNER JOIN dbo.equipment AS b
ON b.acct_id = a.id
INNER JOIN dbo.tmde AS c
ON b.tmde_id = c.id
LEFT JOIN dbo.price_code AS d
ON c.type_id = d.id;
Off topic but I would think about using a meaningful alias instead of a,b,c. Aside from shorter code an alias can, and should, help make the code cleaner. When the alias means nothing it is actually quite a bit more difficult to work with.
For your code I would have chosen something more like a, e, t, p (in that order). If you do something like that you will soon find that every table will have a somewhat natural alias appear and your code across the entire system will be more consistent and easier to maintain.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 19, 2015 at 9:56 am
Thank you for your quick and informative response!
I have a script the does look for the existence of the view, and if it exists, it drops it. Then, I execute a create statement. No IF... ELSE. It works perfectly.
I'm concerned because several of my customers use replication, and I think if I drop a replicated object and recreate it the new object won't be incluced in the existing publication. That's why I thought IF... ALTER... ELSE... CREATE would be better. Any thoughts?
I also appreciate your feedback on using table aliases... they should be better named (if used at all) to make it more legible. It's hard to tell that b is the equipment table, etc!
~~~Tracy
March 20, 2015 at 2:35 am
There are ways to create or update a view without disturbing an existing version, but they involve using dynamic SQL to a greater or lesser extent.
If you're comfortable with that (and the relevant permissions are available) then one of the following will work:
IF OBJECT_ID('dbo.v_equipment', 'V') IS NULL EXEC('CREATE VIEW dbo.v_equipment AS SELECT 1 AS One')
GO
ALTER VIEW [dbo].[v_equipment]
AS SELECT a.acct_nbr,
a.name,
b.id,
b.tmde_id,
b.acct_id,
b.instrument_id,
b.serial_nbr,
b.dept AS dept_a,
b.location,
b.on_site,
c.cal_procedure,
b.cal_interval,
b.cal_cycle,
c.noun,
c.model,
c.mfr,
c.accuracy,
c.dept,
c.category,
c.class,
c.data_required AS data_included,
d.description
FROM dbo.account AS a
INNER JOIN dbo.equipment AS b
ON b.acct_id = a.id
INNER JOIN dbo.tmde AS c
ON b.tmde_id = c.id
LEFT JOIN dbo.price_code AS d
ON c.type_id = d.id
DECLARE @ViewDef varchar(max) = '
CREATE VIEW [dbo].[v_equipment]
AS SELECT a.acct_nbr,
a.name,
b.id,
b.tmde_id,
b.acct_id,
b.instrument_id,
b.serial_nbr,
b.dept AS dept_a,
b.location,
b.on_site,
c.cal_procedure,
b.cal_interval,
b.cal_cycle,
c.noun,
c.model,
c.mfr,
c.accuracy,
c.dept,
c.category,
c.class,
c.data_required AS data_included,
d.description
FROM dbo.account AS a
INNER JOIN dbo.equipment AS b
ON b.acct_id = a.id
INNER JOIN dbo.tmde AS c
ON b.tmde_id = c.id
LEFT JOIN dbo.price_code AS d
ON c.type_id = d.id'
IF OBJECT_ID('dbo.v_equipment', 'V') IS NOT NULL SET @ViewDef = REPLACE(@ViewDef, 'CREATE VIEW', 'ALTER VIEW')
EXEC(@ViewDef)
March 20, 2015 at 9:14 am
Thank you so much! I used the 2nd solution you sent me... works like a charm! 😛
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply