VAAA VAAA - 6 months ago 8
SQL Question

SQL how to get only desired selects inside a SP

I have a SQL Server DB with a few catalogs.

Then I create a new SP that will get all the system catalogs:

-- company
SELECT [companyuid]
,[masterentity]
,[description]
,[externalid]
,[isactive]
FROM [dbo].[setup_company]
WHERE [masterentity] = @masterentity
AND isactive = 1

-- park
SELECT [parkuid]
,[companyuid]
,[description]
,[externalid]
,[isactive]
FROM [dbo].[setup_park]
WHERE isactive = 1

-- store
SELECT [storeuid]
,[parkuid]
,[description]
,[isactive]
FROM [dbo].[setup_store]
WHERE isactive = 1

-- family products
SELECT [familyuid]
,[description]
,[isactive]
FROM [dbo].[setup_product_family]
WHERE isactive = 1


Now, I need this SP to be flexible, so that I can return only
company
catalog or
company
and
users
.

I was thinking of creating a new table named
catalogs
, and there I set all the catalogs that I have and then attach some rule to the SP.

Answer

Another way of doing this,

enum Flg
{
 Company=1,
 Park=2,
 Store=4,
 Family=8
}

you can take only one variable in sp but pass the value in variable like this, Suppose only Company then @Flg=1,company and park both then @flg=3,company and family only then @Flg=9.

Understanding the concept here is more important

Then in your proc you can write like for Testing you can pass any damn value in @Flg.

Declare @Flg int=6

SELECT [companyuid]
      ,[masterentity]
      ,[description]
      ,[externalid]
      ,[isactive]
  FROM [dbo].[setup_company]
WHERE [masterentity] = @masterentity
AND  isactive = 1 and (@Flg & 1=1)
--union all
-- park
SELECT [parkuid]
      ,[companyuid]
      ,[description]
      ,[externalid]
      ,[isactive]
  FROM [dbo].[setup_park]
WHERE isactive = 1 and (@Flg & 2=2)
--union all
-- store
SELECT [storeuid]
      ,[parkuid]
      ,[description]
      ,[isactive]
  FROM [dbo].[setup_store]
WHERE isactive = 1 and (@Flg & 4=4)
--union all
-- family products
SELECT [familyuid]
      ,[description]
      ,[isactive]
  FROM [dbo].[setup_product_family]
WHERE isactive = 1 and (@Flg & 8=8)

You can also store multiple checkbox value here where type cannot be more than 10.