| Author |
stored procedure issue
|
|
|
| Hi,
The following does not work, it doesn't seem to like a stored procedure
combined with a sql request. Why and how can I overcome this ?
SELECT table_name from INFORMATION_SCHEMA.tables where table_name IN
(
exec pr_Admin_TablesFromR
elations 1
)
Thx
| |
|
|
| David Portas 2005-06-10, 7:23 am |
| Put the contents of " pr_Admin_TablesFromR
elations" into the subquery is
probably the simplest and most efficient solution.
--
David Portas
SQL Server MVP
--
| |
|
| ok, i was about to move the content of it into a function, is that ok ?
| |
|
| Thx for your replies.
This works fine :
CREATE PROCEDURE pr_Admin_GetUserFilt
ersTables
@WebId as int
AS
SELECT table_name from INFORMATION_SCHEMA.tables where table_name IN
(
select * from fn_Admin_GetRelation
sTables (@WebId)
)
| |
| David Portas 2005-06-10, 7:23 am |
| An inline table-valued function (TVF) is pretty much equivalent to a
view or subquery or derived table - it's really just a different
syntax. I usually prefer the standard view/subquery/derived table
approach because I think it makes code clearer and it's more flexible
too (TVFs can only be used in certain limited ways).
A multi-statement TVF is a different matter. That's really a way of
exposing procedural code in a query. Although this may look like an
attractive feature do bear in mind that for reasons of performance,
maintainability and integrity, procedural code should be kept to a
minimum in the database. Reliance on lots of multi-statement
table-valued functions in a business process application is probably
indicative of some underlying design and architecure problems or just
bad choices in coding practice. That's the general rule for data
manipluation operations anyway.
Metadata manipulation is a slightly special case because you often need
to do procedural things with metadata. Metadata manipulation tends to
represent a relatively small part of the server-side code in most
business process type applications. Mostly it's confined to admin and
utility -type applications where best-practices are necessarily
different if you want to write generic code against data structures
that are undefined at design time.
In conclusion, a TVF may well be a reasonable solution here but without
knowing what's in the function and what your goal is I can't say for
sure whether there might be better alternatives.
--
David Portas
SQL Server MVP
--
|
|
|
|