passing a variable to CTE in a view

  • Hi ,

    I have a view which contains a CTE and I need to pass the parameter inside the CTE.

    our application is not accepting sps or functions. Please help me

    WITH PartyHierarchy( party_id,reporting_to_party_id,level)AS

    (

    select party_id,reporting_to_party_id,0 from table_a

    where party_id=@party_id ------- parameter to be passed

    union all

    select a.party_id,a.reporting_to_party_id,level+1

    from table_a a

    inner join PartyHierarchy b ON b.party_id=a.reporting_to_party_id

    )

    select H.party_id

    ,H.reporting_to_party_id

    ,pp.last_name as 'SourceParty'

    ,ppb.last_name as businessEntity

    ,prt.code as RelationshipType

    ,ps.name as state

    ,ppt.ID as toPartyId

    ,ppt.last_name as destinationParty

    ,pstat.display_name as status

    ,ppr.effective_date as effectiveDate

    --,level

    --,party_id_from

    --,party_id_to

    from PartyHierarchy H

    inner join table_a W ON H.party_id=W.party_id

    INNER JOIN table_b ppr ON ppr.party_id_from=H.party_id

    INNER JOIN table_c pp ON ppr.party_id_from=pp.id

    INNER JOIN table_c ppb ON ppr.party_id_from=ppb.id

    WHERE table_b.is_deleted=0

    and W.party_id =100007

  • you cannot pass parameters to View

    For parameterized views please check below link

    http://msdn.microsoft.com/en-us/library/c11kd98s(v=vs.80).aspx

  • You have been around here long enough to know that what you posted is not enough information. What do you mean by pass a variable to a cte? A cte is nothing but an inline view.

    And why oh why does your application "not accept stored procedures or functions"??? :w00t:

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • sean,

    The requirement is for hierarchy records. (child and parent records, for that I am passing the child id as parameter). I could create table valued function for the same requirement, but just came to know that our application/framework does not support procedures of functions

    I need to pass the parameter like below

    WITH PartyHierarchy( party_id,reporting_to_party_id,level)AS

    (

    select party_id,reporting_to_party_id,0 from table_a

    where party_id=@party_id ------ parameter to be passed

    union all

    select a.party_id,a.reporting_to_party_id,level+1

    from table_a a

    inner join PartyHierarchy b ON b.party_id=a.reporting_to_party_id

    )

  • pmadhavapeddi22 (1/21/2014)


    sean,

    The requirement is for hierarchy records. (child and parent records, for that I am passing the child id as parameter). I could create table valued function for the same requirement, but just came to know that our application/framework does not support procedures of functions

    I need to pass the parameter like below

    WITH PartyHierarchy( party_id,reporting_to_party_id,level)AS

    (

    select party_id,reporting_to_party_id,0 from table_a

    where party_id=@party_id ------ parameter to be passed

    union all

    select a.party_id,a.reporting_to_party_id,level+1

    from table_a a

    inner join PartyHierarchy b ON b.party_id=a.reporting_to_party_id

    )

    That would make sense for a recursive cte but you have this inside of a view? You are out of luck there. You can't pass a parameter to a view.

    I have to say that disallowing procedures and/or functions is a completely ridiculous rule. I can understand how some shops would outlaw functions because they are so frequently misused/abused but stored procs? That is sort of like saying you want to an object oriented programming language but you are not going to allow any custom methods or classes. It sounds like I am preaching to the choir here but that is seriously crazy.

    Not sure what you can do with such limitations.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • thanks prasad, i tried with parameterized view but again my value is dynamic, so still trying to get some solution 🙂

  • pmadhavapeddi22 (1/21/2014)


    thanks prasad, i tried with parameterized view but again my value is dynamic, so still trying to get some solution 🙂

    Given the ridiculous limitations of no procs or functions maybe you can do this with sql injection rich pass through sql. I of course would not condone that but not sure how else you are going to be able to do this. Dynamic sql is out, cursors are out (another approach that will stink), doing it correctly is out...

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • From my perspective, the answer is quite simple: Don't use a VIEW!

    Just keep your code in-line and use the parameter passed in through whatever means you've designed (presumably from the front end).

    "Your application doesn't support FUNCTIONs or STORED PROCEDUREs" sounds like an inaccurate representation of your situation. It sounds like your DBA (or whoever less qualified person is in charge) has simply made up a silly rule.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Prassad Dabbada V R (1/21/2014)


    you cannot pass parameters to View

    For parameterized views please check below link

    http://msdn.microsoft.com/en-us/library/c11kd98s(v=vs.80).aspx

    You can also think of an inline Table Valued Function (iTVF) as a parameterized view.

    I know this does not help the OP...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • A view cannot accept parameters. [font="Arial Black"]A view CAN, however, read from a table. Put your parameter in a table [/font]and then read from the view which reads from that table to get the parameter. It'll need to be done in a transaction.,

    I agree with the others and feel for you. Not being able to use stored procedures or iTVFs is a pretty nasty requirement. It's amazing that they'll allow a view which is frequently much worse than any stored procedure or iTVF that you could write.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ...but just came to know that our application/framework does not support procedures of functions

    Just a thought...

    This might not help with the hierarchy situation but may help you get around your application's function limitation (which is terrible) in the future. You said the application limits you to views but can those views contain functions? You can use functions in a view. Using Jeff's suggestion: If you had a parameter table you could use a function like this:

    CREATE VIEW dbo.sneakyWayToUseAFunction

    AS

    SELECT iTVF.*

    FROM dbo.parameter

    CROSS APPLY dbo.<usefulFunction>(parameter.val) AS iTVF

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • pmadhavapeddi22 (1/21/2014)


    Hi ,

    I have a view which contains a CTE and I need to pass the parameter inside the CTE.

    our application is not accepting sps or functions. Please help me

    You don't actually need to do anything special other than make sure your party_id is passed through untouched in the cte/view...

    All you need to do is include party_id in the anchor and the union, you can alias it, but make sure it is present in both parts of the rCTE.

    Here is your code again , with that change:

    create view yourView

    as

    WITH PartyHierarchy( party_id,reporting_to_party_id,level,[highlight="#ffff11"]base_id[/highlight])AS

    (

    select party_id,reporting_to_party_id, 0[highlight="#ffff11"], party_id as base_id[/highlight]

    from table_a

    union all

    select a.party_id,a.reporting_to_party_id,level+1[highlight="#ffff11"], b.base_id[/highlight]

    from table_a a

    inner join PartyHierarchy b ON b.party_id=a.reporting_to_party_id

    )

    select H.party_id

    ,H.reporting_to_party_id

    ,pp.last_name as 'SourceParty'

    ,ppb.last_name as businessEntity

    ,prt.code as RelationshipType

    ,ps.name as state

    ,ppt.ID as toPartyId

    ,ppt.last_name as destinationParty

    ,pstat.display_name as status

    ,ppr.effective_date as effectiveDate

    --,level

    --,party_id_from

    --,party_id_to

    [highlight="#ffff11"], base_id[/highlight]

    from PartyHierarchy H

    inner join table_a W ON H.party_id=W.party_id

    INNER JOIN table_b ppr ON ppr.party_id_from=H.party_id

    INNER JOIN table_c pp ON ppr.party_id_from=pp.id

    INNER JOIN table_c ppb ON ppr.party_id_from=ppb.id

    WHERE table_b.is_deleted=0

    Now you can select from the View in your application like this:

    SELECT * -- I don't want to write out the columns, but you should...

    FROM yourView

    WHERE base_id = @party_id

    You should get the appropriate filtering on the base table now - the same as having the WHERE clause in the anchor.

    edit: I had left the variable in the anchor which was very wrong and misleading!!!

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • hi magoo,

    This is really a good solution, base_id is getting eliminated.

    Thanks

  • magoo,

    small correction to my previous post,

    This is a good solution. In the query output, base_id is getting eliminated,but I need to get the the base_id record also

    regards

  • Well. if you need help with that, could you please post some table creation scripts and sample data, along with the new view definition.

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

    Viewing 15 posts - 1 through 15 (of 15 total)

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