|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Execute Stored Procedure from User Defined FunctionIs it possible to execute a Stored Procedure from within a user defined
function. The purpose of the user defined function is to be able to use the results of the Stored Procedure in a select statement. The user defined function should return a table. JC,
Why do you need both? Can't you just use a stored procedure? HTH Jerry Show quoteHide quote "JC" <J*@discussions.microsoft.com> wrote in message news:009B25FA-06E1-481C-B563-6B4F3745717C@microsoft.com... > Is it possible to execute a Stored Procedure from within a user defined > function. > The purpose of the user defined function is to be able to use the results > of > the Stored Procedure in a select statement. The user defined function > should > return a table. I need to use the results of the stored procedure in an inner join.
Here is an Example: Select * From Table1 INNER JOIN <ResultReturnedBy_StoredProcedure> ON Table1.ID= <ResultReturnedBy_StoredProcedure>.ID You can use UDF functions in this manner but as far as I know I can only get results from a Stored Procedure using the Execute statement. If there is a another way to accomplish what I'm trying to do besides using UDF please let me know. Show quoteHide quote "Jerry Spivey" wrote: > JC, > > Why do you need both? Can't you just use a stored procedure? > > HTH > > Jerry > "JC" <J*@discussions.microsoft.com> wrote in message > news:009B25FA-06E1-481C-B563-6B4F3745717C@microsoft.com... > > Is it possible to execute a Stored Procedure from within a user defined > > function. > > The purpose of the user defined function is to be able to use the results > > of > > the Stored Procedure in a select statement. The user defined function > > should > > return a table. > > > JC,
Create a temp table, load the data into the temp table from the proc via INSERT...EXEC, join with the temp table. HTH Jerry Show quoteHide quote "JC" <J*@discussions.microsoft.com> wrote in message news:0DFBC2D5-87A4-4C88-A923-DA0C918749FF@microsoft.com... >I need to use the results of the stored procedure in an inner join. > Here is an Example: > Select * > From Table1 INNER JOIN <ResultReturnedBy_StoredProcedure> ON Table1.ID= > <ResultReturnedBy_StoredProcedure>.ID > > You can use UDF functions in this manner but as far as I know I can only > get > results from a Stored Procedure using the Execute statement. If there is a > another way to accomplish what I'm trying to do besides using UDF please > let > me know. > > > "Jerry Spivey" wrote: > >> JC, >> >> Why do you need both? Can't you just use a stored procedure? >> >> HTH >> >> Jerry >> "JC" <J*@discussions.microsoft.com> wrote in message >> news:009B25FA-06E1-481C-B563-6B4F3745717C@microsoft.com... >> > Is it possible to execute a Stored Procedure from within a user defined >> > function. >> > The purpose of the user defined function is to be able to use the >> > results >> > of >> > the Stored Procedure in a select statement. The user defined function >> > should >> > return a table. >> >> >> Jerry,
Thanks for your responses. I had considered the temp table idea but was hesitant about performance. Is performance really a concern with temp tables. Also just to know, Execute statements are not allowed inside a UDF, Y or N? Show quoteHide quote "Jerry Spivey" wrote: > JC, > > Create a temp table, load the data into the temp table from the proc via > INSERT...EXEC, join with the temp table. > > HTH > > Jerry > "JC" <J*@discussions.microsoft.com> wrote in message > news:0DFBC2D5-87A4-4C88-A923-DA0C918749FF@microsoft.com... > >I need to use the results of the stored procedure in an inner join. > > Here is an Example: > > Select * > > From Table1 INNER JOIN <ResultReturnedBy_StoredProcedure> ON Table1.ID= > > <ResultReturnedBy_StoredProcedure>.ID > > > > You can use UDF functions in this manner but as far as I know I can only > > get > > results from a Stored Procedure using the Execute statement. If there is a > > another way to accomplish what I'm trying to do besides using UDF please > > let > > me know. > > > > > > "Jerry Spivey" wrote: > > > >> JC, > >> > >> Why do you need both? Can't you just use a stored procedure? > >> > >> HTH > >> > >> Jerry > >> "JC" <J*@discussions.microsoft.com> wrote in message > >> news:009B25FA-06E1-481C-B563-6B4F3745717C@microsoft.com... > >> > Is it possible to execute a Stored Procedure from within a user defined > >> > function. > >> > The purpose of the user defined function is to be able to use the > >> > results > >> > of > >> > the Stored Procedure in a select statement. The user defined function > >> > should > >> > return a table. > >> > >> > >> > > > Depends on the number of records. I don't really use UDFs too much so I
don't know the answer to the second question. HTH Jerry Show quoteHide quote "JC" <J*@discussions.microsoft.com> wrote in message news:CB1F5B06-C152-4E0A-A565-3FDC1CA06FD8@microsoft.com... > Jerry, > Thanks for your responses. > I had considered the temp table idea but was hesitant about performance. > Is > performance really a concern with temp tables. Also just to know, Execute > statements are not allowed inside a UDF, Y or N? > > "Jerry Spivey" wrote: > >> JC, >> >> Create a temp table, load the data into the temp table from the proc via >> INSERT...EXEC, join with the temp table. >> >> HTH >> >> Jerry >> "JC" <J*@discussions.microsoft.com> wrote in message >> news:0DFBC2D5-87A4-4C88-A923-DA0C918749FF@microsoft.com... >> >I need to use the results of the stored procedure in an inner join. >> > Here is an Example: >> > Select * >> > From Table1 INNER JOIN <ResultReturnedBy_StoredProcedure> ON Table1.ID= >> > <ResultReturnedBy_StoredProcedure>.ID >> > >> > You can use UDF functions in this manner but as far as I know I can >> > only >> > get >> > results from a Stored Procedure using the Execute statement. If there >> > is a >> > another way to accomplish what I'm trying to do besides using UDF >> > please >> > let >> > me know. >> > >> > >> > "Jerry Spivey" wrote: >> > >> >> JC, >> >> >> >> Why do you need both? Can't you just use a stored procedure? >> >> >> >> HTH >> >> >> >> Jerry >> >> "JC" <J*@discussions.microsoft.com> wrote in message >> >> news:009B25FA-06E1-481C-B563-6B4F3745717C@microsoft.com... >> >> > Is it possible to execute a Stored Procedure from within a user >> >> > defined >> >> > function. >> >> > The purpose of the user defined function is to be able to use the >> >> > results >> >> > of >> >> > the Stored Procedure in a select statement. The user defined >> >> > function >> >> > should >> >> > return a table. >> >> >> >> >> >> >> >> >> The temp table will consist of one int primary key column and no other
columns. It will usually have between 5K to 20K rows but in some instances it can potentially have more. Show quoteHide quote "Jerry Spivey" wrote: > Depends on the number of records. I don't really use UDFs too much so I > don't know the answer to the second question. > > HTH > > Jerry > "JC" <J*@discussions.microsoft.com> wrote in message > news:CB1F5B06-C152-4E0A-A565-3FDC1CA06FD8@microsoft.com... > > Jerry, > > Thanks for your responses. > > I had considered the temp table idea but was hesitant about performance. > > Is > > performance really a concern with temp tables. Also just to know, Execute > > statements are not allowed inside a UDF, Y or N? > > > > "Jerry Spivey" wrote: > > > >> JC, > >> > >> Create a temp table, load the data into the temp table from the proc via > >> INSERT...EXEC, join with the temp table. > >> > >> HTH > >> > >> Jerry > >> "JC" <J*@discussions.microsoft.com> wrote in message > >> news:0DFBC2D5-87A4-4C88-A923-DA0C918749FF@microsoft.com... > >> >I need to use the results of the stored procedure in an inner join. > >> > Here is an Example: > >> > Select * > >> > From Table1 INNER JOIN <ResultReturnedBy_StoredProcedure> ON Table1.ID= > >> > <ResultReturnedBy_StoredProcedure>.ID > >> > > >> > You can use UDF functions in this manner but as far as I know I can > >> > only > >> > get > >> > results from a Stored Procedure using the Execute statement. If there > >> > is a > >> > another way to accomplish what I'm trying to do besides using UDF > >> > please > >> > let > >> > me know. > >> > > >> > > >> > "Jerry Spivey" wrote: > >> > > >> >> JC, > >> >> > >> >> Why do you need both? Can't you just use a stored procedure? > >> >> > >> >> HTH > >> >> > >> >> Jerry > >> >> "JC" <J*@discussions.microsoft.com> wrote in message > >> >> news:009B25FA-06E1-481C-B563-6B4F3745717C@microsoft.com... > >> >> > Is it possible to execute a Stored Procedure from within a user > >> >> > defined > >> >> > function. > >> >> > The purpose of the user defined function is to be able to use the > >> >> > results > >> >> > of > >> >> > the Stored Procedure in a select statement. The user defined > >> >> > function > >> >> > should > >> >> > return a table. > >> >> > >> >> > >> >> > >> > >> > >> > > > I'm having the same problem. I would like to use a UDF to call my stored
procedure so I could create a view using it. I am developing an application in C# that uses Crystal Reports. It is so much easier for the Reports to use Tables/Views/Functions (Stored Procs are not even listed) by the wizard. Any suggestions? Show quoteHide quote "JC" wrote: > The temp table will consist of one int primary key column and no other > columns. It will usually have between 5K to 20K rows but in some instances it > can potentially have more. > > "Jerry Spivey" wrote: > > > Depends on the number of records. I don't really use UDFs too much so I > > don't know the answer to the second question. > > > > HTH > > > > Jerry > > "JC" <J*@discussions.microsoft.com> wrote in message > > news:CB1F5B06-C152-4E0A-A565-3FDC1CA06FD8@microsoft.com... > > > Jerry, > > > Thanks for your responses. > > > I had considered the temp table idea but was hesitant about performance. > > > Is > > > performance really a concern with temp tables. Also just to know, Execute > > > statements are not allowed inside a UDF, Y or N? > > > > > > "Jerry Spivey" wrote: > > > > > >> JC, > > >> > > >> Create a temp table, load the data into the temp table from the proc via > > >> INSERT...EXEC, join with the temp table. > > >> > > >> HTH > > >> > > >> Jerry > > >> "JC" <J*@discussions.microsoft.com> wrote in message > > >> news:0DFBC2D5-87A4-4C88-A923-DA0C918749FF@microsoft.com... > > >> >I need to use the results of the stored procedure in an inner join. > > >> > Here is an Example: > > >> > Select * > > >> > From Table1 INNER JOIN <ResultReturnedBy_StoredProcedure> ON Table1.ID= > > >> > <ResultReturnedBy_StoredProcedure>.ID > > >> > > > >> > You can use UDF functions in this manner but as far as I know I can > > >> > only > > >> > get > > >> > results from a Stored Procedure using the Execute statement. If there > > >> > is a > > >> > another way to accomplish what I'm trying to do besides using UDF > > >> > please > > >> > let > > >> > me know. > > >> > > > >> > > > >> > "Jerry Spivey" wrote: > > >> > > > >> >> JC, > > >> >> > > >> >> Why do you need both? Can't you just use a stored procedure? > > >> >> > > >> >> HTH > > >> >> > > >> >> Jerry > > >> >> "JC" <J*@discussions.microsoft.com> wrote in message > > >> >> news:009B25FA-06E1-481C-B563-6B4F3745717C@microsoft.com... > > >> >> > Is it possible to execute a Stored Procedure from within a user > > >> >> > defined > > >> >> > function. > > >> >> > The purpose of the user defined function is to be able to use the > > >> >> > results > > >> >> > of > > >> >> > the Stored Procedure in a select statement. The user defined > > >> >> > function > > >> >> > should > > >> >> > return a table. > > >> >> > > >> >> > > >> >> > > >> > > >> > > >> > > > > > >  
Thread options
Other interesting topics
I have a dream - Enum datatype in SQL 2005?
Removing "time" from datetime. Between vs. >= and <= what the heck is happening here SQL help Creating SQL database "Users" pulled from Active directory OU Help on selecting on 3 keys, and finding both beginning and ending times... function in a constraint script to drop all 'user' objects Self relating tables |
|||||||||||||||||||||||
