January 19, 2015 at 2:36 pm
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
January 19, 2015 at 2:49 pm
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...
January 20, 2015 at 6:31 pm
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
January 26, 2015 at 6:48 am
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
January 26, 2015 at 6:56 am
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
January 26, 2015 at 7:37 am
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
January 26, 2015 at 7:47 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy