SQL Table Valued Function – Return certain data from ‘WITH’ based on parameter value

I am trying to do a “with” to loop through some data (which its doing fine). But after that with, I want to return data dependent on a bit parameter. Its important that this is inside a function. Below is basically what my code is doing.

WITH StuffChain 
AS (
   //initial
    union all
   //more
)

After this, I am trying to do something like

CASE WHEN @MyParamVal = 1 THEN
      SELECT TOP (1) * FROM StuffChain
ELSE
      SELECT * FROM StuffChain
END
RETURN

SQL is not my strength and I am still learning sorry. I am also unsure whether or not to use inline or multi statement function

EDIT: When I am giving the case, I am using it to explain what I am after to return, not necessarily what I will use. I use it to just describe what I need using what little I know if that makes sense.

Answer

You can do as follows. This is just one of the solution. you can do with many other ways also.

WITH StuffChain 
AS (
   //initial
    union all
   //more
)

After creation of CTE, try with following

SELECT TOP (CASE WHEN @MyParamVal = 1 THEN 1 ELSE 
    (SELECT COUNT(1) FROM StuffChain) END  *
FROM StuffChain
order by <column> <ASC/DESC>;

Advertisement

Answer