Please HELP

I wrote a UDF in SQL server 2005 that looks like this:

CREATE FUNCTION InvoiceNum ()
RETURNS INT
AS
BEGIN
DECLARE @.inv int
SET @.inv = @.inv + 1
SET @.inv = (SELECT InvoiceNumber FROM Sales)

IF @.inv IS NULL
SET @.inv = '1'
ELSE if @.inv IS NOT NULL and @.inv > ''
INSERT INTO Sales(InvoiceNumber
RETURN @.inv
END

Which gives me this error:

Msg 156, Level 15, State 1, Procedure InvoiceNum, Line 16
Incorrect syntax near the keyword 'RETURN'.

I would like help on that error...and after that, I'm using Visual Studio 2005...I would like to know how to call the function from within a command in MSVS. Your help will be greatly appreciated!

Quote:

Originally Posted by gggram2000

I wrote a UDF in SQL server 2005 that looks like this:

CREATE FUNCTION InvoiceNum ()
RETURNS INT
AS
BEGIN
DECLARE @.inv int
SET @.inv = @.inv + 1
SET @.inv = (SELECT InvoiceNumber FROM Sales)

IF @.inv IS NULL
SET @.inv = '1'
ELSE if @.inv IS NOT NULL and @.inv > ''
INSERT INTO Sales(InvoiceNumber
RETURN @.inv
END

Which gives me this error:

Msg 156, Level 15, State 1, Procedure InvoiceNum, Line 16
Incorrect syntax near the keyword 'RETURN'.

I would like help on that error...and after that, I'm using Visual Studio 2005...I would like to know how to call the function from within a command in MSVS. Your help will be greatly appreciated!

You need to close the bracket.

INSERT INTO Sales(InvoiceNumber)|||

Quote:

Originally Posted by amitpatel66

You need to close the bracket.

INSERT INTO Sales(InvoiceNumber)

Yea that's a mistake i did when i pasted it here...but in the actual server it has the bracket.

A question would be how to insert a value (eg. Name) into a table row block. Like: Insert Into Table (ClolumnName) Value(@.ColumnName) But I want to enter it into and existing column/row to replace the value of that specific column with "Name". Let's say for that column it has "None"...I want to change it to "Name"...how do i do that?