向 SQL Server 中的存储过程添加参数 [英] Adding Parameters to a Stored Procedure in SQL Server

问题描述

我是 K2 和 SQL Server 的新手.

I am new to K2and SQL Server.

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

I want to add a parameter to a stored procedure, which will be later tied to a K2 smart object for the respective views and forms.

目前它接受 1 个参数，lang，它是来自 K2 Smartform 视图的标签的输入.

Currently it takes in 1 parameter, lang, which is an input from a label from the K2 Smartform View.

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

I added a label labelHideInactiveCompany on the same view, and I would like to pass that value into my Stored Procedure but I don't know how to do it.

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

I am told that the first thing I need to change is the stored procedure, and then updating the smart object.

我可以知道我应该采取哪些步骤吗?谢谢.

May I know what are the steps I should be take? Thank you.

以下是我的查询:

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

推荐答案

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

It sounds like you want this parameter and this WHERE clause:

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

这篇关于向 SQL Server 中的存储过程添加参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！