sql - 向 SQL Server 中的存储过程添加参数

Question

我是新来的 K2和 SQL Server .

我想向存储过程添加一个参数，该参数稍后将绑定(bind)到 K2相应 View 和表单的智能对象。

目前它接受 1 个参数，lang ，这是来自 K2 的标签的输入智能表单 View 。

我添加了标签 labelHideInactiveCompany在同一个 View 中，我想将该值传递到我的存储过程中，但我不知道该怎么做。

有人告诉我，我需要更改的第一件事是存储过程，然后更新 smart object .

我可以知道我应该采取哪些步骤吗？谢谢你。

以下是我的查询:

USE [K2_Database]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [Config].[usp_ListBusinessUnit] 
@lang varchar(2) = null
as

SELECT EntityId
      ,EntityCode
      ,EntityName
      ,CO.OrganizationDesc
      ,EntityAbbreviation
      ,PBU.ParentBusinessUnitName
      ,EntityAttribute 
      ,EntityOwnedCompany
      ,EntityPrincipal
      ,EntityAccountingProgram 
      ,EntityCurrency
      ,EntityCurrenyExchRate 
      ,BU.CountryRegion
      ,EntityNCOwnedIndustry
      ,BU.IsActive 
      ,BU.CreatedBy 
      ,CreateOn 
      ,BU.ModifiedBy
      ,BU.ModifiedOn 
      ,BU.Lang AS LangAbbr
  FROM Config.BusinessUnit BU
  LEFT JOIN Config.Organization CO on BU.OrganizationId = CO.OrganizationId
  LEFT JOIN Config.ParentBusinessUnit PBU on BU.ParentBusinessUnitId = PBU.ParentBusinessUnitId 
  ORDER BY CASE WHEN @lang = 'cn' THEN BU.Lang END,
           CASE WHEN @lang = 'en' THEN BU.Lang END DESC, 
           EntityName

Answer 1

听起来你想要这个参数和这个 WHERE 子句:

ALTER procedure [Config].[usp_ListBusinessUnit] 
@lang varchar(2) = null, 
@hideInactiveCompany bit = 0 -- Add this parameter!
as

SELECT EntityId
      ,EntityCode
      ,EntityName
      ,CO.OrganizationDesc
      ,EntityAbbreviation
      ,PBU.ParentBusinessUnitName
      ,EntityAttribute 
      ,EntityOwnedCompany
      ,EntityPrincipal
      ,EntityAccountingProgram 
      ,EntityCurrency
      ,EntityCurrenyExchRate 
      ,BU.CountryRegion
      ,EntityNCOwnedIndustry
      ,BU.IsActive 
      ,BU.CreatedBy 
      ,CreateOn 
      ,BU.ModifiedBy
      ,BU.ModifiedOn 
      ,BU.Lang AS LangAbbr
  FROM Config.BusinessUnit BU
  LEFT JOIN Config.Organization CO on BU.OrganizationId = CO.OrganizationId
  LEFT JOIN Config.ParentBusinessUnit PBU on BU.ParentBusinessUnitId = PBU.ParentBusinessUnitId 
  WHERE (@hideInactiveCompany = 0 OR BU.IsActive = 1) -- Use the parameter!
  ORDER BY CASE WHEN @lang = 'cn' THEN BU.Lang END,
           CASE WHEN @lang = 'en' THEN BU.Lang END DESC, 
           EntityName