In SQL Server 2000 can I...

Results 1 to 6 of 6

Thread: In SQL Server 2000 can I...

  1. #1
    Join Date
    Dec 1969
    Posts
    201

    Default In SQL Server 2000 can I...

    Create a user defined function in which I pass in a table, add to the table then return the table? If so what is the syntax? I&#039;m thinking you cannot do this because the following syntax seems like it should be correct:<BR><BR>CREATE FUNCTION [dbo].fn_GetChildContent <BR>(<BR> @iContentTable table<BR> (<BR> ContentID int,<BR> ContentTypeID int,<BR> Content varchar(25)<BR> )<BR><BR>) <BR>RETURNS @Table Table<BR> (<BR> ContentID int,<BR> ContentTypeID int,<BR> Content varchar(25)<BR> )<BR>AS <BR>BEGIN <BR><BR>...<BR><BR>RETURN<BR>END

  2. #2
    Join Date
    Dec 1969
    Posts
    497

    Default RE: In SQL Server 2000 can I...

    I&#039;ve never tried passing in a table... did you get an error??<BR><BR>Sponge

  3. #3
    Join Date
    Dec 1969
    Posts
    18,177

    Default I doubt that you can..

    .. just because of the way that you call functions:<BR><BR>select function_name(field, &#039;value&#039;, getdate())<BR>from table

  4. #4
    Join Date
    Dec 1969
    Posts
    201

    Default RE: In SQL Server 2000 can I...

    Yeah, I get "Error 156: Incorrect syntax near the keyword &#039;table&#039;."<BR><BR>What I&#039;m trying to do is come up with some way of pulling from a table and recursively getting all the child entries in that table.<BR><BR>In this case I have a Content table with ContentID and some other fields, and a Content_Content table with ParentContentID, and ChildContentID fields. I want to be able to grab all the appropriate rows from Content, then all child Content Rows of those, and all Child Content Rows of those, etc. in one Database access if possible. Any suggestions?<BR><BR>Also, I&#039;ve heard that SQL recursion is pretty intensive on the server, but in this case it will not usually go more than 1 level deep. Recursion really won&#039;t be used too often, but I&#039;d like to have it there in the rare case where it is needed.


  5. #5
    Join Date
    Dec 1969
    Posts
    18,177

    Default See the articles on..

    .. SQLTeam.com about "Hierachies" and trees of data.<BR><BR>And, look on Books Online for "Displaying Hiearchies" or something like that.<BR><BR>A function (and recursion) is not the way to go.

  6. #6
    Join Date
    Dec 1969
    Posts
    201

    Default Will do...

    Thanks for the advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •