Create View failture

Question

Is there a way to write this query as a view? Or other function?

--CREATE VIEW ViewSwitch AS  
  
DECLARE @Course_ID INT = 1  
DECLARE @searchstring NVARCHAR (10) = '%ata%'  
   
  
IF (@Course_ID = 1)  
       BEGIN  
             DROP TABLE IF EXISTS ##test  
             PRINT ' first time create temp table with Data'  
             SELECT 'Data' AS Test INTO ##test  
             SELECT * FROM ##Test WHERE Test like @searchstring  
       END  
ELSE  
       BEGIN  
             PRINT ' Data direct vom temp_table'  
             SELECT * FROM ##Test WHERE Test like @searchstring  
       END  

Answer 1

Hi @Joerg 62
CREATE VIEW Syntax:

CREATE [ OR ALTER ] VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]  
[ WITH <view_attribute> [ ,...n ] ]  
AS select_statement  
[ WITH CHECK OPTION ]  
[ ; ]    

As you can see in this syntax, the View is defined with only select_statement.
If you want IF ELSE statement, then as JingyangLi commented, you could use Stored Procedure.
Check this sample:

CREATE OR ALTER PROCEDURE sproc_name  
   @Course_ID INT , --Bring in parameters  
   @searchstring NVARCHAR (10)   
AS     
 IF (@Course_ID = 1)  
   BEGIN  
     DROP TABLE IF EXISTS ##test  
     PRINT ' first time create temp table with Data'  
     SELECT 'Data' AS Test INTO ##test  
     SELECT * FROM ##Test WHERE Test like @searchstring  
   END  
 ELSE  
   BEGIN  
     PRINT ' Data direct vom temp_table'  
     SELECT * FROM ##Test WHERE Test like @searchstring  
   END  
  
--Call stored procedure  
EXEC sproc_name @Course_ID = 1 , @searchstring = '%ata%'  
--or  
EXEC sproc_name  1,'%ata%' --Pay attention to the order and type of parameters  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Views cannot create objects, like your temp table. Views are simply stored SELECT statements.

For that purpose, you must use a stored procedure.