Errors in Function code - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Errors in Function code

Author

Topic

SteveBermes
Starting Member

1 Post

Posted - 2007-02-02 : 10:31:17

Hi,

I'm pretty new to SQL and I'm having trouble with a function, probably just syntax. I'm trying to consolidate several rows of data into a single row text field.
The calling program can use views but not functions so I need to get a table into a view with the consolidated lines.

Also, I would think this could be consolidated to a single view that calls one function. Is that possible, how would I do it?

Thanks for any help,
Steve

VIEW WHERE DATA COMES FROM:
CUST_ORDER_ID | LINE_TEXT
number1 | first line item text
number1 | Second line item text
number1 | Third line item text
number2 | first line item text
number2 | Second line item text
...

VIEW I WANT:
CUST_ORDER_ID | CO_LINES_TEXT
number1 | Second line item text 'crlf' Third Line item text
number2 | Second line item text

FUNCTION CODE: This is the function code with errors

CREATE FUNCTION dbo.tvf_NC_CO_LINES_TEXT (@ORDER_ID VARCHAR(20))

RETURNS @NC_CO_LINES TABLE
(
CUST_ORDER_ID VARCHAR(20) PRIMARY KEY NOT NULL,
CO_LINES_TEXT VARCHAR(1000) NULL
)
AS
BEGIN
DECLARE @COLinesString VARCHAR(1000);
SET @ORDER_ID = CUST_ORDER_ID;
SELECT @COLinesString = COALESCE(@COLinesString + Char(13) + Char(10), '') + LINE_STRING
FROM NC_CO_LINES_STRING
WHERE NC_CO_LINES_STRING.CUST_ORDER_ID = @ORDER_ID
SET @COLinesString = CO_LINES_TEXT
RETURN
END;

GO

ERROR MESSAGES:
Msg 207, Level 16, State 1, Procedure tvf_NC_CO_LINES_TEXT, Line 16
Invalid column name 'CUST_ORDER_ID'.
Msg 207, Level 16, State 1, Procedure tvf_NC_CO_LINES_TEXT, Line 20
Invalid column name 'CO_LINES_TEXT'.

VIEW CODE:
SELECT dbo.CUST_ORDER_LINE.CUST_ORDER_ID, CAST(CAST(dbo.CUST_ORDER_LINE.ORDER_QTY AS INT) AS VARCHAR(2))
+ ' ' + dbo.CUST_ORDER_LINE.PART_ID + ' ' + dbo.PART.DESCRIPTION AS LINE_STRING
FROM dbo.CUST_ORDER_LINE INNER JOIN
dbo.PART ON dbo.CUST_ORDER_LINE.PART_ID = dbo.PART.ID
WHERE (dbo.CUST_ORDER_LINE.LINE_NO > 1)

Steve Bermes
Novae Corp.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-02-02 : 10:47:49

Looks like you want a cross tab report. Why not do this in your front end? (Crystal Reports perhaps).

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-02 : 10:48:57

You need to read more about writing table-valued function and using parameters in SP/function.

quote:
SET @ORDER_ID = CUST_ORDER_ID;

1. @ORDER_ID is an input parameter, so no need to assign value inside the body of function. Also the manner of setting value from table is wrong.

2. You are nowhere declaring a table variable inside the body of function and inserting record into it, even though the return type of your function is table.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources