t-sql 2012 pass paramter value to cte

Question

t-sql 2012 pass paramter value to cte

wendy elizabeth

SSCoach

Points: 16915
More actions
July 29, 2015 at 5:48 pm

#299276

In some t-sql 2012 that I am using, I am using the following on 5 separate merge statements.
USING
(select LKC.comboID,LKC.lockID,LKC.seq,A.lockCombo2,A.schoolnumber,LKR.lockerId
from
[LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber =
LKR.number
JOIN TST.dbo.Lock LK ON LKR.lockID = LK.lockID
JOIN TST.dbo.LockCombination LKC ON LK.lockID = LKC.lockID
and LKC.seq = 1
) AS LKC2 (comboID,lockID,seq,combo)
What is different, is the value of LKC.seq = 1 as listed below. I am using 5 separate ctes
and the only value that changes is the LKC.seq number being a value between 1 and 5. Thus
can you pass a parameter value to the CTE that I just listed above? If so, would you show me
the t-sql to accomplish this goal?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63559 More actions · Answer 1

CTEs are no different than any other query in that respect. You can pass parameters to them exactly the way you would in any other stored procedure.

SELECT ...

FROM ...

WHERE field = @paramValue

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

That's not a CTE, that's part of a merge statement.

Do you have 5 merge statements, or are there some CTEs that you haven't shown us?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

t-sql 2012 pass paramter value to cte

Cookies on SQLServerCentral