CODEWITHSUNDEEP
sqlsql-servert-sql
Kobojunkie
Kobojunkie

Reputation: 6545

IF EXIST Drop Table in View results in Incorrect syntax near the keyword 'IF'. message

The following code in my view products the error message Incorrect syntax near the keyword 'IF'

ALTER VIEW [dbo].[IDW_vwGetProductOutPut] 
AS  
IF EXISTS
( SELECT * FROM tempdb.dbo.sysobjects 
  WHERE ID = OBJECT_ID(N'tempdb..#TempPackaging')) 
BEGIN
    DROP TABLE #TempPackaging 
END  . . . . . .     

--code to create temp table goes here . .  and so on

How do I code this please?

Upvotes: 0

Views: 2736

Answers (2)

Rodney Adams
Rodney Adams

Reputation: 291

I don't believe this functionality is available with SQL Server. As a workaround you may want to execute your logic in a stored procedure.

Upvotes: 0

CodeNaked
CodeNaked

Reputation: 41393

A view can only contain SELECT statements, what you have is more like a stored procedure.

CREATE PROC [dbo].[IDW_spGetProductOutPut] 
AS  
IF EXISTS
( SELECT * FROM tempdb.dbo.sysobjects 
  WHERE ID = OBJECT_ID(N'tempdb..#TempPackaging')) 
BEGIN
    DROP TABLE #TempPackaging 
END  . . . . . .     

--code to create temp table goes here . .  and so on

Upvotes: 6

Related Questions