Post reply

Setting variables within a CTE

  • February 3, 2008 at 5:20 am

    #178869

    Hello,

    Is there any possible way to set or use variables within CTEs?

    Also, is it possible to call a stored procedure in a CTE using EXECUTE?

    Of course, I've attempted these cases with no luck, but want to know if it's factually not possible, or if the real problem is between the chair and the keyboard. 😛

    Thanks,

    - Ryan

  • February 3, 2008 at 6:58 am

    #774575

    CTEs are essentially temporary views, thus, no setting of vars or calling of procedures.

    You can set and use variables in the statement that uses the CTE. -

    ;WITH Testing (ID) AS (

    SELECT 1 AS ID

    )

    SELECT @Var = ID, @SomeVar FROM Testing

    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

  • February 3, 2008 at 11:14 am

    #774590

    You can also use variables in the CTE:

    DECLARE @myVAR int;

    SET @myVAR = 1;

    ;WITH myCTE (col1)

    AS (SELECT col1 FROM myTABLE WHERE col2 = @myVAR)

    SELECT * FROM myCTE;

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • February 5, 2008 at 9:25 am

    #775195

    Thank you both for your help! 😀

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

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