How can I query properties dynamically in SQL Server?-CodePudding

I am currently working on a project, that includes in an automatical flairing part.

Basicly what this does:

I have a table called Fox, with various columns. And some other tables refering to Fox (i.e. CaughtChickens).
I want to have another table, that I can expand anytime, with 3 columns (other than ID ofc.) in my mind FlairName, FlairColor, and FlairStoredProcedure.
I want to have a stored procedure that returns all FlairName where the FlairStoredProcedure returns 1, for a certain FoxID.

This way I can write a stored procedure that checks if a certain Fox caught a chicken and returns 1 if it did, and add a flair Hunter on the User UI.

There are some cons with this:

Every time I want a new flair I have to write a new stored procedure it (yeah I kinda can't short this one out).
The stored procedures needs to have the same amount of in parameters (ie. @FoxID), and needs to return 1 or 0 (or select nothing when false, select the name if true (?))
I need to use dynamicSQL in the stored procedure that collect these flairs, and I kinda don't want to use any dynamicSQL at all.

Isn't there a lot easier way to do this that I am missing?

EDIT:

Example:

I have a table Fox:

FoxID FoxName FoxColor FoxSize Valid
  1   Swiper    red      12      1

I would have a table Flairs

FlairID FlairName FlairStoredProcedure Valid
   1       Big       pFlairs_IsFoxBig    1
   2      Green    pFlairs_IsFoxGreen    1

I would have 3 stored procedures:

pFox_Flairs

@FoxID int
    DECLARE @CurrentFlairSP as varchar(100)
    DECLARE @CurrentIDIndex as varchar(100) = 1
    DECLARE @ResultFlairs as table(FlairName as varchar(50), FlairColor as integer)
WHILE @CurrentIDIndex <= (SELECT MAX(ID) FROM Flairs WHERE Valid <> 0)
BEGIN
 IF EXISTS(SELECT * FROM Flairs WHERE ID = @CurrentIDIndex AND VALID <> 0)
BEGIN
  SET @CurrentFlairSP = CONCAT((SELECT TOP 1 FlairStoredProcedure FROM Flairs WHERE ID = @CurrentIDIndex AND VALID <> 0), ' @FoxID=@FoxID')
  INSERT INTO @ResultFlairs
  EXEC (@CurrentFlairSP)
 END
 @CurrentIDIndex  = 1
END
SELECT * FROM @ResultFlairs

pFlairs_IsFoxBig

@FoxID int
SELECT 'Big' WHERE EXISTS( SELECT TOP 1 * FROM Fox WHERE ID = @Fox AND FoxSize > 10)

pFlairs_IsFoxGreen

@FoxID int
SELECT 'Green' WHERE EXISTS( SELECT TOP 1 * FROM Fox WHERE ID = @Fox AND FoxColor = 'green')

CodePudding user response：

You could create a single table valued function that checks all the conditions:

CREATE OR ALTER FUNCTION dbo.GetFlairs ( @FoxID int )
RETURNS TABLE
AS RETURN

SELECT v.FlairName
FROM Fox f
CROSS APPLY (
    SELECT 'Big'
    WHERE f.FoxSize > 10

    UNION ALL

    SELECT 'Green'
    WHERE f.FoxColor = 'green'
) v(FlairName)
WHERE f.FoxID = @FoxID;

go

Then you can use it like this:

SELECT *
FROM dbo.GetFlairs(123);

If or when you add more attributes or conditions, simply add them into the function as another UNION ALL