How to use if condition with dynamic SQL

Question

I want to put if condition for following query but getting error

ALTER PROCEDURE [dbo].[GetExternalDocumentHistory]
    @reoid                  int, 
    @spType                 VARCHAR(50),
    @maximumRows            INT             = 0,
    @startRowIndex          INT             = 0,  
    @recordCount            INT  OUTPUT    
AS BEGIN

DECLARE @data TABLE(
        id                  INT IDENTITY(1,1),
        property_address    NVARCHAR(250),
        property_id         INT )

INSERT @data( property_address, property_id )

IF( @spType = 'ED' )
BEGIN
  SELECT property_address,  property_id 
  FROM external_documents_history as edu      
  WHERE edu.property_id = @reoid
END;

IF (@spType = 'GD'  ) 
BEGIN
   SELECT property_address, property_id 
   FROM external_documents_history as edu 
   WHERE edu.page_type = 'GD'
END;

SELECT @recordCount = COUNT(*) FROM @data
IF @maximumRows = 0     
BEGIN
   SET @startRowIndex = 0
   SET @maximumRows   = @recordCount
END

SELECT * FROM @data
WHERE id BETWEEN @startRowIndex + 1 AND @startRowIndex + @maximumRows
ORDER BY id  

END;

What is the issue with the above query ?

@user728630 - It is complaining about the IF due to the INSERT statement right above the if. — JonH
– JonH, Commented Oct 4, 2011 at 12:08
i created a temp table and inserting the data in the temp table by selecting, that's how i am doing in the rest of the stored proc and its working working fine without condition but in this one i have to add the condition — Rishi Bharadwaj
– Rishi Bharadwaj, Commented Oct 4, 2011 at 12:11

ypercubeᵀᴹ · Accepted Answer · 2011-10-04 12:27:45Z

3

Besides @Alex's two conditional INSERTs (the best way I think), here's a variation on @Sorpigals's answer, without the CASE::

  INSERT INTO @data( property_address, property_id )
    SELECT property_address
        ,  property_id 
    FROM external_documents_history as edu  
    WHERE ( @spType = 'ED' AND edu.property_id = @reoid )
       OR ( @spType = 'GD' AND edu.page_type = 'GD' )

answered Oct 4, 2011 at 12:27

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sorpigal Over a year ago

Yes, the case is a little superfluous. In general it can be used to simulate if within a select, which is why I wrote it that way. Your version is a little better (and easier to read, too).

Alex K. · Accepted Answer · 2011-10-04 12:10:28Z

2

You cannot use an IF after the INSERT statement , the simplest option is to restate the insert for all possible conditions;

IF (@spType = 'ED') BEGIN
  INSERT @data(property_address, property_id)
  SELECT property_address, property_id FROM external_documents_history as edu WHERE edu.property_id = @reoid
END

You can also skip SELECT @recordCount = COUNT(*) FROM @data and use @@ROWCOUNT

answered Oct 4, 2011 at 12:10

Alex K.

177k32 gold badges276 silver badges299 bronze badges

3 Comments

Rishi Bharadwaj Over a year ago

Like this i will have to use insert twice for both conditions, right?

Alex K. Over a year ago

You need a complete INSERT statement so yes you would need to state it in each IF condition

Alex K. Over a year ago

Becuse of the 2 selects I made the assumption that the source tables were different; they aren't so Sorpigal's solution is nicer.

sorpigal · Accepted Answer · 2011-10-04 12:17:15Z

Use a case statement in your where clause, e.g.

DECLARE @data TABLE(
        id                  INT IDENTITY(1,1),
        property_address    NVARCHAR(250),
        property_id         INT )

INSERT INTO @data( property_address, property_id )
select property_address,  property_id FROM external_documents_history as edu
    where
        case when @spType = 'ED' and edu.property_id = @reoid then 1
        case when @spType = 'GD' and edu.page_type = 'GD' then 1
        else 0
    end = 1
;

SELECT @recordCount = COUNT(*) FROM @data

     IF @maximumRows = 0 
         BEGIN
            SET @startRowIndex = 0
            SET @maximumRows   = @recordCount
         END

     SELECT * FROM @data
     WHERE id BETWEEN @startRowIndex + 1 AND @startRowIndex + @maximumRows
     ORDER BY id     
END;

JonH · Accepted Answer · 2011-10-04 12:04:57Z

1

For one thing this is not correct:

INSERT @data( property_address, property_id )

Should be:

INSERT INTO table_name VALUES (value1, value2, value3,...)

And being that this is before your if statement that is probably why your query analyzer / management studio is complaining about the if.

answered Oct 4, 2011 at 12:04

JonH

33.2k13 gold badges93 silver badges149 bronze badges

1 Comment

ypercubeᵀᴹ Over a year ago

In SQL-Server INTO is optional in INSERT: msdn.microsoft.com/en-us/library/aa933206%28v=sql.80%29.aspx

Jared Peless · Accepted Answer · 2011-10-04 12:09:33Z

1

If you are trying a dynamic INSERT INTO Table SELECT A, B, C FROM OtherTableDynamically you will need to prepare the entire statement and use sp_executesql.

answered Oct 4, 2011 at 12:09

Jared Peless

1,1209 silver badges11 bronze badges

Collectives™ on Stack Overflow

How to use if condition with dynamic SQL

5 Answers 5

1 Comment

3 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

3 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related