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