June 4, 2012 at 2:38 pm
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?
June 4, 2012 at 2:44 pm
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/
June 4, 2012 at 2:49 pm
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
June 5, 2012 at 6:49 am
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...
June 5, 2012 at 6:54 am
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
June 5, 2012 at 7:07 am
That's what I thought, I was just trying to jump ahead.
Thanks for the ITVF that, hopefully, will solve my problems
June 5, 2012 at 7:43 am
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?
June 5, 2012 at 7:57 am
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
June 5, 2012 at 8:11 am
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply