Re: T-SQL how to deal with results from stored proc

Wolfgang Kreuzer (wolfgang.m.kreuzer@xxxxxx) writes:
> My attempt was to use INSERT EXEC to fetch te results of several
> sp_helprolemember calls in a temp table (in some stored proc;
> up_EnumSuperUsers; up_EnumApplAdmins ...) and return collected data as
> a record set. In a wrapping stored proc I intended to collect data
> from some of the proc's above and check if current user or specified
> user is in the list - but as far as I know evaluating data from a
> stored proc record set requires INSERT EXEC which does not allow
> nesting for whatever reason.
>
> On the other hand, UDF's do not allow usage of non-deterministic
> functions, call of stored proc, INSER, CREATE etc.

You can also use temp tables, as I discuss in the article I pointed you to.

> What I have done is, I took the SQL statement out of sp_helprolemember
> put it in several UDF's which just returns a BIT value indicating if
> someone is member of a role or some of them.

I don't know exactly what you are doing, but have you looked at the
built-in function is_member()?



--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
.

Relevant Pages

  • Table structure and data transfer from SQL2000 to Access (.mdb)
    ... SQL server table to a table within MS access. ... EXEC sp_addlinkedsrvlogin 'Freddie', false, 'sa', 'Admin', NULL ... -- This fails - Refer error ... from #temp ...
    (comp.databases.ms-sqlserver)
  • Re: Stored procedure never finishes
    ... I have found that if I don't use temp tables then the stored proc works. ... > that you may have a run away query (this can't be fixed by restarting the ... > SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Temp Tables, Transaction and Loops
    ... Is it performant and what is the reason for using EXEC sp_executesql in this ... > CREATE PROC spmyproc ... > ALTER TABLE #Temp ADD col INT ... >> procedure from another stored proc within a loop and a transaction I ...
    (microsoft.public.sqlserver.programming)
  • Re: Using results form a stored procedure in an exists clause?
    ... > Consider saving the results in a temp table and then running the EXISTS ... > Don't name your stored proc with an sp_ prefix. ... > Columnist, SQL Server Professional ... >> Use a UDF instead of Stored procedure ...
    (microsoft.public.sqlserver.programming)
  • DataTable empty when Stored Proc uses temp table
    ... I have a SQL Server 2000 Stored proc which creates a temp table and ... Prev by Date: ...
    (microsoft.public.dotnet.csharp.general)