Professional Web Applications Themes

Can't call UDF as sub-query - Microsoft SQL / MS SQL Server

You cannot pass a column name as a parameter to a UDF in a join/subquery. If you can post your table DDLs & sample data along with expected results someone can suggest an alternative. Taking a guess, you can re-write your UDF as : CREATE FUNCTION udf (eigenschaft TINYINT) RETURNS table TABLE (id SMALLINT, Wert SMALLINT) AS ... GO Now you can write your query as : SELECT * FROM Variants t1 WHERE EXISTS (SELECT * FROM dbo.udf(2) f1 WHERE f1.id = t1.id) ; -- - Anith ( Please respond to newsgroups only )...

  1. #1

    Default Re: Can't call UDF as sub-query

    You cannot pass a column name as a parameter to a UDF in a join/subquery. If
    you can post your table DDLs & sample data along with expected results
    someone can suggest an alternative. Taking a guess, you can re-write your
    UDF as :

    CREATE FUNCTION udf (eigenschaft TINYINT)
    RETURNS table TABLE (id SMALLINT, Wert SMALLINT)
    AS ...
    GO

    Now you can write your query as :

    SELECT *
    FROM Variants t1
    WHERE EXISTS (SELECT *
    FROM dbo.udf(2) f1
    WHERE f1.id = t1.id) ;

    --
    - Anith
    ( Please respond to newsgroups only )


    Anith Sen Guest

  2. #2

    Default Re: Can't call UDF as sub-query

    Well, I guess this is what I will have to do then... But the new column is an INT. If I can't filter the resultset of my UDF as originally designed, it is likely to yield BIG temporary tables.. *sigh*

    Sorry, I can't publish the DDL...

    Axel

    ----------------------------
    "Anith Sen" <anithbizdatasolutions.com> schrieb im Newsbeitrag news:uh3Oyc$QDHA.2636TK2MSFTNGP10.phx.gbl...
    > You cannot pass a column name as a parameter to a UDF in a join/subquery. If
    > you can post your table DDLs & sample data along with expected results
    > someone can suggest an alternative. Taking a guess, you can re-write your
    > UDF as :
    >
    > CREATE FUNCTION udf (eigenschaft TINYINT)
    > RETURNS table TABLE (id SMALLINT, Wert SMALLINT)
    > AS ...
    > GO
    >
    > Now you can write your query as :
    >
    > SELECT *
    > FROM Variants t1
    > WHERE EXISTS (SELECT *
    > FROM dbo.udf(2) f1
    > WHERE f1.id = t1.id) ;
    >
    > --
    > - Anith
    > ( Please respond to newsgroups only )
    >
    >
    Axel Dahmen Guest

Similar Threads

  1. Replies: 26
    Last Post: September 12th, 11:02 PM
  2. Replies: 0
    Last Post: September 19th, 06:55 AM
  3. Replies: 0
    Last Post: September 19th, 04:59 AM
  4. Replies: 0
    Last Post: September 18th, 11:40 AM
  5. call without query like http://web.com/storename
    By R. Rajesh Jeba Anbiah in forum PHP Development
    Replies: 1
    Last Post: August 31st, 08:50 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139