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