Create view with if statement

Question

I have a query where the result changes according to the parameter. I have created a stored procedure to show what I need. I want it in a view so that I can join with other tables.

CREATE PROCEDURE [SP_getTableByInputParameter]
    @inputParam INT
AS
BEGIN 
    IF (@inputParam = 1)
    BEGIN 
        SELECT
            cp.CompanyId, x.value AS Leavetypeid 
        FROM
            CompProperty CP 
        CROSS APPLY
            OPENJSON(cp.Properties, '$.Manageid') AS x
        WHERE
            CP.Properties IS NOT NULL 
            AND CP.CategoryId = 2 
            AND CP.Priority = 1 
            AND CP.Properties LIKE '%Manageid%'
    END
    ELSE IF (@inputParam = 2)
    BEGIN 
        SELECT 
            cp.CompanyId, x.value AS Leavetypeid 
        FROM
            CompProperty CP 
        CROSS APPLY
            OPENJSON(cp.Properties, '$.Unmanageid') AS x
        WHERE
            CP.Properties IS NOT NULL 
            AND CP.CategoryId = 2 
            AND CP.Priority = 1 
            AND CP.Properties LIKE '%UnManageid%'
    END
END

The view I need is something like

SELECT *
FROM company c
INNER JOIN SP_getTableByInputParameter(l) J ON c.companyid = J.companyid

I tried to create view as same as the procedure. But it didn't work.

Any idea?

have a look at this : https://stackoverflow.com/questions/1687279/can-we-pass-parameters-to-a-view-in-sql — Harry, May 03 '23 at 02:47
**Side note**: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, May 03 '23 at 03:58
Are you actually using SQL2008, it doesn't have any JSON functions? — Charlieface, May 03 '23 at 10:06

score 0 · Answer 1 · answered May 03 '23 at 02:57

Just to expand on my comment AND offer a little twist ... Note the CHOOSE() function.

The dynamic JSON attribute will fail in 2016 but works in 2017+

Example

CREATE FUNCTION [dbo].[tvf_getTableByInputParameter]( @inputParam INT )

Returns Table 
As
Return (  

select cp.CompanyId
      ,x.value as Leavetypeid 
 from CompProperty CP 
 cross apply openjson(cp.Properties,choose(@inputParm,'$.Manageid','$.Unmanageid')) as x
 where  CP.Properties IS NOT NULL 
   and CP.CategoryId = 2 
   and CP.Priority = 1 
   and CP.Properties like choose(@inputParm,'%Manageid%','%UnManageid%')
)

Usage

Select *
 From [dbo].[tvf_getTableByInputParameter](1)

score 0 · Answer 2 · answered May 03 '23 at 10:04

You can just use an IIF or CASE within OPENJSON

CREATE PROCEDURE [SP_getTableByInputParameter]
    @inputParam INT
AS

SELECT
  cp.CompanyId,
  x.value AS Leavetypeid 
FROM
    CompProperty CP 
CROSS APPLY
    OPENJSON(CP.Properties, IIF(@inputParam = 1, '$.Manageid', '$.Unmanageid')) AS x
WHERE CP.CategoryId = 2 
  AND CP.Priority = 1;

The CROSS APPLY will automatically remove any row which do not have the relevant JSON property, or are null.

Create view with if statement

2 Answers2