passing a variable to CTE in a view

Question

Post reply

passing a variable to CTE in a view

pmadhavapeddi22

SSCertifiable

Points: 5306
More actions
January 21, 2014 at 7:52 am

#281441

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
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]

Prassad Dabbada V R Ten Centuries Points: 1251 More actions · Answer 1

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

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/

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 3

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

)

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

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/

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 5

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

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/

Dwain Camps SSC Guru Points: 86978 More actions · Answer 7

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 8

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

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 9

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)

Alan Burstein SSC Guru Points: 61141 More actions · Answer 10

...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

mister.magoo SSC-Forever Points: 47174 More actions · Answer 11

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);

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 12

hi magoo,

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

Thanks

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 13

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

mister.magoo SSC-Forever Points: 47174 More actions · Answer 14

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);