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.
Advertisement
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>;