SQL Server Create view with if statement

kyvafyod 于 2023-05-05 发布在其他

关注(0)|答案(2)|浏览(150)

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?

sql-server

来源：https://stackoverflow.com/questions/76160050/create-view-with-if-statement

2条答案

按热度按时间

agyaoht71#

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)

赞(0）回复(0）举报 2023-05-05

wj8zmpe12#

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.

赞(0）回复(0）举报 2023-05-05

我来回答

SQL Server Create view with if statement

2条答案

相关问题

热门标签

最新问答