View with Parameter??

Question

View with Parameter??

Ken at work

Hall of Fame

Points: 3426
More actions
June 4, 2012 at 2:38 pm

#256944

Can you use a variable with a view?
I have a view that joins 5 tables, one of which is an OpenQuery. Is there anyway to choose a value/variable for a Where Clause?
Reason: We use Access for a few of our front ends. I know the world is going to PHP or ASP but this has not been upgraded yet. Within Access: If you have an Access query that is joined with a SQL View I can update the Access table. However if I have an Access query that is joined to a Query using a Stored Procedure it will neither allow updates or inserts.
So, what is the best way to have a SQL View that joins 5 tables that will still allow me to change a value in a (or multiple) where clause(s).
Thank you for your help,
P.S. I know that a View is basically the Table and as such cannot have a parameter. So I'm really asking is what do you use for a work around?

Viewing 9 posts - 1 through 8 (of 8 total)

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

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

Ken at work (6/4/2012)
Can you use a variable with a view?
I have a view that joins 5 tables, one of which is an OpenQuery. Is there anyway to choose a value/variable for a Where Clause?
Reason: We use Access for a few of our front ends. I know the world is going to PHP or ASP but this has not been upgraded yet. Within Access: If you have an Access query that is joined with a SQL View I can update the Access table. However if I have an Access query that is joined to a Query using a Stored Procedure it will neither allow updates or inserts.
So, what is the best way to have a SQL View that joins 5 tables that will still allow me to change a value in a (or multiple) where clause(s).
Thank you for your help,

No a view may not use a variable. You could maybe create a table valued function instead. It is hard to know exactly what you want without some more details.

_______________________________________________________________

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/

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

What you're asking for is an in-line table-valued function.

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

Ken at work Hall of Fame Points: 3426 More actions · Answer 3

Is there anyway to declare a variable inside a ITVF or MTVF?

i.e. If the input Parameter is @Co_No nvarchar(3)

Then inside the ITVF you would find:

Declare @MySql_01 nvarchar(4000)

Declare @MySql_02 nvarchar(4000)

Set MySql_01 =

'Select *

From OpenQuery(Linked_Server, '' '

Set MySql_02 =

'Select

Field_A, Field_B, Field_C, Field_D

From Table_E

Where (Field_A = ' + Co_No + ')

For Read Only '') As My_Table '

Execute ( MySql_01 + MySql_02);

That way I could put a parameter/varaible in and OpenQuery. I'm pretty sure I can't enter it without stringing it together that way, sigh...

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

You can't use dynamic SQL in a function.

Going to have to go procedure with this.

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

Ken at work Hall of Fame Points: 3426 More actions · Answer 5

That's what I thought, I was just trying to jump ahead.

Thanks for the ITVF that, hopefully, will solve my problems

Ken at work Hall of Fame Points: 3426 More actions · Answer 6

Is there any way to have temp tables in an ITVF?

What would accomplish the same thing, which is cutting down on the data coming from the linked server?

My example didn't show it but really the view is 5 joined tables, one of which is from a linked server. If I can put a dynamic Where Clause in the OpenQuery if not through an Execute of multiple nvarchars then perhaps I could build a temp table first?

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

No. Table variables only, but I strongly recommend you don't use a multi-statement table-valued function as they can be performance nightmares

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

Ken at work Hall of Fame Points: 3426 More actions · Answer 8

hehe, and it's not like I haven't already had those issues.

Well, here I am thanking you again for your tremendous help.

I do appreciate all you've done for me.