passing variable to CTE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

passing variable to CTE

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-02-04 : 15:31:14

hi,

Below is my query


WITH Months AS(
	SELECT  UPPER(convert(varchar(3),datename(month, DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0)))) month_name,
		DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0) startdate,
		DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N + 1, 0) enddate
	FROM (VALUES (6),(5),(4),(3),(2),(1)) x(N)
)
select month_name from months

i want to pass the value to this query and based on the value the values should be constructed.

for example :

declare @val int

if @val = 2 then below should be constructed


WITH Months AS(
	SELECT  UPPER(convert(varchar(3),datename(month, DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0)))) month_name,
		DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0) startdate,
		DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N + 1, 0) enddate
	FROM (VALUES (2),(1)) x(N)
)
select month_name from months

if @val = 3 then below should be constructed


WITH Months AS(
	SELECT  UPPER(convert(varchar(3),datename(month, DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0)))) month_name,
		DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0) startdate,
		DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N + 1, 0) enddate
	FROM (VALUES (3),(2),(1)) x(N)
)
select month_name from months

if @val = 4 then below should be constructed


WITH Months AS(
	SELECT  UPPER(convert(varchar(3),datename(month, DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0)))) month_name,
		DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0) startdate,
		DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N + 1, 0) enddate
	FROM (VALUES (4),(3),(2),(1)) x(N)
)
select month_name from months

if @val = 5 then below should be constructed


WITH Months AS(
	SELECT  UPPER(convert(varchar(3),datename(month, DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0)))) month_name,
		DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0) startdate,
		DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N + 1, 0) enddate
	FROM (VALUES (5),(4),(3),(2),(1)) x(N)
)
select month_name from months

how to achieve this dynamic construction. please help me

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-04 : 16:43:24

won't this work?


declare @val int
set @val = 2
;WITH Months AS(
	SELECT  UPPER(convert(varchar(3),datename(month, DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0)))) month_name,
		DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0) startdate,
		DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N + 1, 0) enddate
	FROM (VALUES (6),(5),(4),(3),(2),(1)) x(N)
       where N <= @val
)
select month_name from months

Be One with the Optimizer
TG

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-02-04 : 21:58:19

Yes it worked great. Thank you so much.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-05 : 10:06:24

cool - you're welcome.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources