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 : stackoverflow.com/questions/1687279/… — Harry
– Harry, Commented May 3, 2023 at 2: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), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented May 3, 2023 at 3:58
Are you actually using SQL2008, it doesn't have any JSON functions? — Charlieface
– Charlieface, Commented May 3, 2023 at 10:06

John Cappelletti · Accepted Answer · 2023-05-03 02:57:24Z

0

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)

answered May 3, 2023 at 2:57

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charlieface · Accepted Answer · 2023-05-03 10:04:48Z

0

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.

answered May 3, 2023 at 10:04

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

Collectives™ on Stack Overflow

Create view with if statement

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related