How to create a SQL function using C#

Question

I want to define a function to get "order number" parameter and return "last name" of the customer.these two parameters are in different tables i am using inner join.

the errors i am getting are: Incorrect syntax near (.

Incorrect syntax near the keyword RETURN.

Must declare the scalar variable @FindingLastName.

my code:

com.CommandText = "Create function Sales.FindingLastName (@OrderNumber varchar(10)) Returns nvarchar(50) As Begin Declare @FindingLastName(Select LastName from Sales.OrderDetails INNER JOIN Sales.Customers RETURN @FindingLastName END GO)";
com.Connection = con;
MessageBox.Show(com.ExecuteNonQuery().ToString());

Why are you creating functions via code? Why don't you use simple script? — Marko Juvančič
– Marko Juvančič, Commented Jan 3, 2014 at 11:53
This has absolutely nothing to do with C#. The errors you get are entirely because of invalid SQL. — user743382
– user743382, Commented Jan 3, 2014 at 11:54
you can see it in my code , i am doing the same , how do you think i can fix it?thx @ Taosique — Parisan
– Parisan, Commented Jan 3, 2014 at 11:56
Compare your function statement with the examples given in this link at the end : msdn.microsoft.com/en-us/library/ms186755.aspx, I would also suggest to first run your script in SQL server than use it it as commanText, if you have to prefer this way. — NeverHopeless
– NeverHopeless, Commented Jan 3, 2014 at 12:22

Naveen · Accepted Answer · 2014-01-03 16:56:18Z

3

You have lot of things missed in your SQL Syntax. Have a look at below SQL Syntax Change the ColumnNameto actual columnnames in join statement. May be you want to modify the select condition accordingly by adding WHERE clause.

Create function Sales.FindingLastName 
(@OrderNumber varchar(10))
Returns nvarchar(50) As 
Begin 
Declare @FindingLastName varchar(50) = 
(Select LastName from Sales.OrderDetails O INNER JOIN Sales.Customers C ON C.ColumnName =O.ColumnName)
RETURN @FindingLastName 
END

One more question You can run this only once. Once the function is created, you can't create same function again. So i doubt are you sure you want to do it this way via c#?? !!

For more details on User Defined function in SQL Refer THIS LINK

edited Jan 3, 2014 at 16:56

answered Jan 3, 2014 at 12:03

Naveen

1,5021 gold badge15 silver badges24 bronze badges

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

2 Comments

Parisan Over a year ago

@ Naveen thanks man , i did every changes you said the only problem is that when i run it in C# i get -1 !! why is that?

Naveen Over a year ago

@ Parisan Run the select statement you wrote in your code directly in DB and check what it returns.

Marc Gravell · Accepted Answer · 2014-01-03 12:16:08Z

1

What stands out there is the GO. Firstly, GO is not SQL. It only exists to tools like SSMS (etc), which use it to split a long script into separate commands. Inside an actual command, it is simply a syntax error. Consequently, GO can never appear inside nesting such as parenthesis, or a BEGIN / END block.

Basically, remove the GO.

However, it is pretty rare to use DDL inside ADO.NET, unless part of an automated schema migration tool etc.

answered Jan 3, 2014 at 12:16

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Comments

Remco Brilstra · Accepted Answer · 2014-01-03 12:03:40Z

0

I'm no hero with SQL, but i think this should work

    Create function Sales.FindingLastName (@OrderNumber varchar(10)) 
    RETURNS nvarchar(50) 
    As 
    RETURN
    (
      Select LastName 
      from Sales.OrderDetails INNER JOIN Sales.Customers 
    );

answered Jan 3, 2014 at 12:03

Remco Brilstra

8386 silver badges13 bronze badges

1 Comment

Parisan Over a year ago

Dear Remco i am writing this code of yours in C# with this format com.CommandText = "Create function Sales.FindingLastName (@OrderNumber varchar(10) RETURNS nvarchar(50) As RETURN(Select LastName from Sales.OrderDetails INNER JOIN Sales.Customers )"; and i get the error of "incorrect syntax near RETURNS" how can i correct it?

Collectives™ on Stack Overflow

How to create a SQL function using C#

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related