Problem creating function for .net assembly in SQL Server

Question

[SqlProcedure()]
public static void GetCustInfo(SqlString PhoneNo, out SqlString CustInfo)
{
    // code...
}

This is how I try to create the function:

CREATE FUNCTION [dbo].[GetCustInfo] (@PhoneNo nvarchar(50))
RETURNS nvarchar(max) WITH EXECUTE AS CALLER
AS EXTERNAL NAME  CustFromPhone.[ManagedCodeAndSQLServer.BaseFunctionClass].GetCustInfo

and this is the error:

CREATE FUNCTION failed because T-SQL and CLR types for return value do not match.

your right... look the comment below what my mistake was.

Ezi
– Ezi

2011-05-31 20:33:55 +00:00
Commented May 31, 2011 at 20:33 — Ezi
– Ezi, Commented May 31, 2011 at 20:33

DarkSquirrel42 · Accepted Answer · 2011-05-31 20:23:55Z

3

even if you ask nicely ... a void function won't return something that can be converted to a nvarchar

answered May 31, 2011 at 20:23

DarkSquirrel42

10.3k3 gold badges22 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ezi Over a year ago

I've read that sql procedure's can't have a return value. (maybe that's only true for vb.net?)

DarkSquirrel42 Over a year ago

you are mixing up some concepts. to keep things simple: functions return something ... procedures don't. that's when you talk about the theoretic concept of functions or procedures. when it comes to everyday language, many people (including me) tend to call everything a function, or use both terms interchangable. to be precisely, there is no such thing as a void function ... if you have a void foo() that's always a procedure, but usually you don't have to worry about it. in this case it's simple ... your SQL function says it returns a nvarchar(max) ... your GetCustInfo(...) returns nothing

Decker97 · Accepted Answer · 2011-05-31 20:27:41Z

1

Try this maybe:

[SqlProcedure()]
public static SqlString GetCustInfo(SqlString PhoneNo)
{
    // code...
}

answered May 31, 2011 at 20:27

Decker97

1,65310 silver badges11 bronze badges

Collectives™ on Stack Overflow

Problem creating function for .net assembly in SQL Server

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related