# why to use dynamic sql???

• ### Question

• DECLARE @Quarter VARCHAR(50) = 'Q2'
SELECT * FROM PropertyTotal  WHERE Quarter in

('''' + REPLACE((CASE @Quarter WHEN 'Q1' THEN 'Q1' WHEN 'Q2' THEN 'Q1,Q2' END),',',''',''') + '''');

CAN SOME ONE EXPLAIN WHAT IS WRONG IN THIS query?

WHAT IS NEED FOR ME TO GO FOR DYNAMIC SQL INORDER TO GET EXECUTE THIS QUERY????

Monday, January 2, 2012 11:21 AM

• Looks like you just need the following:

```SELECT * from PropertyTotal Where Quarter <= @Quarter
```

For every expert, there is an equal and opposite expert. - Becker's Law

My blog
Monday, January 2, 2012 5:06 PM

### All replies

• Hi,

Try with below query:

```DECLARE @Quarter VARCHAR(50) = 'Q2'

SELECT * FROM PropertyTotal  WHERE Quarter in (SELECT 'Q1' UNION SELECT @Quarter)
```
This is specific to your question here, If you can give some more details of what you are trying to achieve then people here can help with more ideas.

Regards,

Asim Bagwan

Kindly mark the replies as Answers if they help!

Monday, January 2, 2012 11:33 AM
• if @Quarter value is 'Q3' then i need to get all the records in quarters Q1,Q2,Q3 .

this should be done without using dynamic sql.

i mean if @Quarter = 'Q1' then Quarter in q1, q2

when @Quarter in q4 then quarter in  'q1','q2','q3','q4'

Monday, January 2, 2012 11:41 AM
• Hi,

See if this works for you:

```DECLARE @Quarter VARCHAR(50) = 'Q4'
--SELECT * FROM PropertyTotal  WHERE Quarter in

;WITH CTE AS
(
SELECT 1 Id, 'Q1' Qtr
UNION
SELECT 2, 'Q2'
UNION
SELECT 3, 'Q3'
UNION
SELECT 4, 'Q4'
)

SELECT * FROM PropertyTotal  WHERE Quarter in (SELECT Qtr FROM CTE WHERE ID <= RIGHT(@Quarter, 1))
```

Regards,

Asim Bagwan

Kindly mark the replies as Answers if they help!

Monday, January 2, 2012 12:25 PM
• WHAT IS NEED FOR ME TO GO FOR DYNAMIC SQL INORDER TO GET EXECUTE THIS QUERY????

Hi Sudeep,

We need dynamic  queries when we have to entertain some search criteria from UI. In other words, If we need to fetch some data from DB based on some random parameters(that do not have a defined vale for e.g a name search where user can enter just 3 characters to search) that user can choose from UI.

In you case you need to fetch data based on the quarters. Which quarter that depends on user input. Here you can fetch data of single as well as multiple quarters.

Thanks
Every day its a new learning. Keep Learning!!
Monday, January 2, 2012 12:34 PM
• You may make use of cursor to achieve this.

Monday, January 2, 2012 4:58 PM
• Looks like you just need the following:

```SELECT * from PropertyTotal Where Quarter <= @Quarter
```

For every expert, there is an equal and opposite expert. - Becker's Law

My blog
Monday, January 2, 2012 5:06 PM
• Hi Mitchpe,

As per my knowledge, one should avoid using cursors. They are unnecesasarily heavy and there performance is also low.

Every day its a new learning. Keep Learning!!

• Edited by Tuesday, January 3, 2012 8:38 AM
Tuesday, January 3, 2012 4:10 AM
• thanks Naomi.

Tuesday, January 3, 2012 6:01 AM
• Refer this detailed document of why we need dynamic SQL

http://www.sommarskog.se/dynamic_sql.html#storedprocedures

Hope this helps!

Thanks

Every day its a new learning. Keep Learning!!