CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresparametersoptional-parameters
Costas
Costas

Reputation: 27

Stored Procedure If statement

I'm wanting to have a sp use a conditional parameter such as 

CREATE PROCEDURE spTest
(
    @param1 DATE,
    @param2 DATE,
    @param3 NVARCHAR(8) = null
)

AS

IF (@param3 = 'test1')
BEGIN
    SELECT a, SUM(b) AS SumOfB, SUM(c) AS SumOfC FROM db1 GROUP BY a
END

WITH innerQuery1 AS
(
    SELECT a, b, c FROM db2 WHERE a >=@param1 AND a <= @param2
)
    SELECT a, SUM(b) AS SumOfB, SUM(c) AS SumOfC FROM innerQuery1 GROUP BY a

I know this is very generic but this process works. What I'm attempting to do is put the conditional "in the middle" such as.

CREATE PROCEDURE spTest
(
    @param1 DATE,
    @param2 DATE,
    @param3 NVARCHAR(8) = null
)

AS

WITH innerQuery1 AS
(
    SELECT a, b, c FROM db2 WHERE a >=@param1 AND a <= @param2
)

IF (@param3 = 'test1')
BEGIN
    SELECT a, SUM(b) AS SumOfB, SUM(c) AS SumOfC FROM db1 GROUP BY a
END

    SELECT a, SUM(b) AS SumOfB, SUM(c) AS SumOfC FROM innerQuery1 GROUP BY a

What happens is now it can't see innerQuery1 anymore. Can you not have a conditional in a WITH?

Any ideas as to why it doesn't work? My actual query is much longer and more nested queries. Basically trying to only have the one sp in this case and using the results from queries based on the parameter.

Thanks,

Upvotes: 0

Views: 626

Answers (2)

IVNSTN
IVNSTN

Reputation: 9299

If I understand your intent right, you might want to think of some different approach like splitting test cases into separate SPs and composing test-suites with them:

CREATE PROC TestSuite_CheckAggregations
    @arg,
    @param1, @param2
as
  ...
  SET @testCase = CASE @arg 
     WHEN '1' THEN 'TestCase_1_innerQuery'
     WHEN '2' THEN `TestCase_2_DirectQuery`
  END
  ...
  EXEC @testCase
    @param1, @param2
  ...
END



CREATE PROC TestCase_1_innerQuery
...

CREATE PROC TestCase_2_DirectQuery
...

It might be more useful to build testing system on language/framework that supports such things naturally.

Upvotes: 1

Ilyes
Ilyes

Reputation: 14928

Try to create your procedure as:

CREATE PROCEDURE spTest
(
    @param1 DATE,
    @param2 DATE,
    @param3 NVARCHAR(8) = null
)

AS

IF (@param3 = 'test1')
BEGIN
    SELECT a, SUM(b) AS SumOfB, SUM(c) AS SumOfC FROM db1 GROUP BY a
END
    ELSE
        BEGIN
            WITH innerQuery1 AS
            (
                SELECT a, b, c FROM db2 WHERE a >=@param1 AND a <= @param2
            )
                SELECT a, SUM(b) AS SumOfB, SUM(c) AS SumOfC FROM innerQuery1 GROUP BY a;
        END
GO

Upvotes: 1

Related Questions