Question

Reuse Stored Procedure for Lookup Rules in Form

Forms Version 10

Updated January 8, 2020

asked on December 29, 2019

I want to prepare a reuseable stored procedure to select a list of users base on several criteria, such as department or grade. If these information is not provided, all users should be returned. The stored procedure definition should look like this

CREATE STORED PROCEDURE spGetUsers 
  @dept varchar(30),
  @grade varchar(30)
BEGIN
 ...
END

The stored procedure itself performs well, but when I would like to use it for Lookup rules in a case, where both @dept and @grade should be Null, I cannot do it as the _(DB.Null) option only appear once.

Furthermore, is it possible to assign a constant as a parameter when calling the stored procedure? Now, I need to create separate stored procedures for different scenarios?

Thanks

0 0

Replies

replied on December 30, 2019

What about passing a default value such as "None" from your form to the second parameter? I played around with this as well but had four input params and it was just too much trouble. Finally just created a couple views in the database that were filtered or not in the lookup rules.

0 0

replied on December 30, 2019

I planned to create some dummy single-line text field for the job, it works, but it is too messy for me. So wondering if there is any better alternative for the job.

0 0

replied on December 30, 2019

Are you trying to create multiple lookup rules using the same procedure? Based on your explanation it sounds like you need to accommodate multiple scenarios on the form (with/without the additional inputs), or will each scenario be on a different form?

0 0

replied on December 30, 2019

I just try to create a standard stored procedure for multiple forms, as I would like to centralize the retrieval of employees in my organization. In some scenarios, I just need all employees without any filter, sometimes, I need to filter it by their department, or grading, or both.

0 0

replied on December 30, 2019

Have you tried setting a default value within the stored procedure to essentially make it optional? That way you could just exclude them from the lookup on forms where they aren't needed/used.

For example

CREATE PROCEDURE [dbo].[sp_OptionalTest]
	@Required varchar(20),
	@Optional varchar(20) = null
AS
BEGIN
	SET NOCOUNT ON;

	SELECT @Required AS [Result]
	UNION ALL
	SELECT @Optional AS [Result] WHERE @Optional IS NOT NULL
END

GO

In this example, if you provide a parameter for Optional, you get both results, but if you only provide Required parameter you only get one result.

I don't know what your stored procedure looks like so I can't say exactly how you need to implement this, but that is the general idea.

Just note that I've never had a default null work correctly with IF statements, but it should be fine if you had something like

WHERE [column1] = @Required
AND (@Optional IS NULL OR [column2] = @Optional)

0 0

replied on January 8, 2020

Sorry for the late reply.

I tried to create a stored procedure as follow:

CREATE OR ALTER PROCEDURE [dbo].[spDEMOMultiInput]
	@UserID1 nvarchar(30),
	@UserID2 nvarchar(30) = NULL,
	@UserID3 nvarchar(30) = NULL
AS
BEGIN
	// details omitted
END
GO

When I setup Lookup Rules, it shows "The "Use" section of the lookup rule cannot be left blank." when I leave the parameters empty (as shown in screenshot 1 below). When I try to use _DB.Null, it only allows me to use once only.

Thanks.

SP Reuse 01.png (34.12 KB)

| Download

SP Reuse 02.png (28.95 KB)

| Download

0 0

replied on December 31, 2019

I assume the department and grades are build as dropdowns? If so, you could set the default value to 0 on the department and grade. If department is not selected, then 0 would be the value and so forth for grade. So your stored procedure would have 3 IF statements built.

Something like the below

CREATE STORED PROCEDURE spGetUsers
@dept varchar(30),
@grade varchar(30)

IF (@dept = 0 and @grade = 0)

BEGIN

Select u.User_FirstName + u.User_LastName as FullName
from tblUsers u
...
END

IF @dept != 0 and @grade !=0

BEGIN

Select u.User_FirstName + u.User_LastName as FullName
from tblUsers u
Where @dept=u.dept and @grade=u.grade
END

IF @dept !=0 OR @grade !=0

BEGIN

Select u.User_FirstName + u.User_LastName as FullName
from tblUsers u
Where @dept=u.dept or @grade=u.grade

END

0 0

You are not allowed to follow up in this post.

Question

Question

Reuse Stored Procedure for Lookup Rules in Form

Replies

Sign in to reply to this post.