Adding Parameters to a Stored Procedure in SQL Server

Question

I am new to K2and SQL Server.

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.

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

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.

Below is my query:

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

score 2 · Answer 1 · answered Oct 12 '18 at 05:34

2

Adding the parameter is simple, I've used place holders as we don't know what you want to call the param or what datatype it is.

All you need to do is add the parameter in the ALTER statement.

ALTER procedure [Config].[usp_ListBusinessUnit] 
@lang varchar(2) = null,
@newParamName newParamType -- Your new stuff
as
-- the rest of your stored proc here

Now don't forget to do something with the new parameter in the stored proc.

answered Oct 12 '18 at 05:34

Jon P

19,442
8
49
72

I received the error `Incorrect syntax near '@hideInactiveCompany'.`. Do I have to set/create the new parameter somewhere else before declaring it in my stored prod? – gymcode Oct 12 '18 at 05:42
2

Note the comma after the first parameter. Parameters are effectively a comma separated list. – Jon P Oct 12 '18 at 06:03

Max Szczurek · Accepted Answer · 2018-10-12T05:57:26.993

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

I met the same error with Jon P's approach, `Incorrect syntax near '@hideInactiveCompany'.`. Do I have to set/create the new parameter somewhere else before declaring it in my stored prod? — gymcode, Oct 12 '18 at 05:44
No - the syntax in the post is correct. Make sure you added a comma after @lang varchar(2) = null (the 1st parameter declaration) — Max Szczurek, Oct 12 '18 at 05:50
@gymcode I edited my post so you should just be able to copy and paste it into SQL management studio and execute it. Can you try this? It should work... — Max Szczurek, Oct 12 '18 at 05:58

Adding Parameters to a Stored Procedure in SQL Server

2 Answers2

Linked