Getting an error when trying to duplicate some code off of a website. What have I done wrong?

  • I found an article on the CodeProject website named T-SQL MapReduce. I'm trying to duplicate the SQL code there into a SQL Server 2914 Developer Edition database I've got, and also the C# code using Visual Studio. I've defined the user data types fine (e.g.: word_t, words_t and so on), but I got an error when I tried to create the UDF dbo.mapper from the page. The error I got was:

    Msg 102, Level 15, State 1, Procedure mapper, Line 5

    Incorrect syntax near 'CALLER'.

    The SQL code I used is straight off of the page; it looks like this:

    create function dbo.mapper(@documents DocumentsWithAutoIncrementIndexTable READONLY, @sep nvarchar)

    returns @t TABLE ( wordindex int, word word_t )

    WITH EXECUTE AS CALLER

    What have I done wrong? I'm guessing that the author has done something like left off a variable declaration or something like that. Anyway, what am I missing?

    Rod

  • The description in the original link is somewhat misleading:

    the part related to "create function..." and the following part "While EXISTS..." are both part of the same function.

    The function should look like

    create function dbo.mapper(@documents DocumentsWithAutoIncrementIndexTable READONLY, @sep nvarchar)

    returns @t TABLE ( wordindex int, word word_t )

    WITH EXECUTE AS CALLER

    While EXISTS(SELECT * From @documents WHERE @lastidprocessed < P_Id)

    Begin

    ...

    As a side note: you'll need to create the custom data type DocumentsWithAutoIncrementIndexTable, too...



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • I think I did create that custom data type, but will have to check it when I'm on my laptop again.

    Thank you for responding!

    Rod

  • LutzM (1/19/2015)


    The description in the original link is somewhat misleading:

    the part related to "create function..." and the following part "While EXISTS..." are both part of the same function.

    The function should look like

    create function dbo.mapper(@documents DocumentsWithAutoIncrementIndexTable READONLY, @sep nvarchar)

    returns @t TABLE ( wordindex int, word word_t )

    WITH EXECUTE AS CALLER

    While EXISTS(SELECT * From @documents WHERE @lastidprocessed < P_Id)

    Begin

    ...

    As a side note: you'll need to create the custom data type DocumentsWithAutoIncrementIndexTable, too...

    I see what you're saying now. Yes that makes sense.

    Now I know this is going to make me sound very ignorant, but I can't help that. In the article on Code Project the author made the comment that, "...@document which is a space-separated concatenation of words and @word which is a single word data type." I am not familiar with a data type that is "a space-separated concatenation of words". What sort of a data type is that? Is it just a NVARCHAR or is there some specify data type in SQL Server that is "a space-separated concatenation of words"? And how about the variable @word which is "a single word data type"? Again I'm not familiar with a data type in SQL Server that is "a single word data type". Is that also just a NVARCHAR?

    BTW, I have the DocumentsWithAutoIncrementIndexTable user defined type.

    Rod

  • Probably both VARCHAR(8000) or VARCHAR(Max). There's no 'array' data type in SQL, there's no data type which enforces a particular combination of string contents.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • In looking at the Mapper function more closely I believe that the @word variable is very likely a NVARCHAR. It's assigned a value from the SUBSTRING function call. I'm still not sure what sort of data type the @words variable is, though.

    And another question. I've written UDF's before, but they were all scalar return type UDFs. I am not sure what the syntax is, in this case, to return the table expression that Mapper should return.

    Rod

  • Doctor Who 2 (1/26/2015)


    I'm still not sure what sort of data type the @words variable is, though.

    It'll be varchar or nvarchar

    And another question. I've written UDF's before, but they were all scalar return type UDFs. I am not sure what the syntax is, in this case, to return the table expression that Mapper should return.

    Have you looked in Books Online? I seem to recall the documentation on functions is pretty good.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply