July 29, 2015 at 5:48 pm
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?
July 29, 2015 at 6:31 pm
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
July 31, 2015 at 2:06 pm
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply