Adding function in SQL Server Management Studio
I asked a question some time ago (https://www.experts-exchange.com/questions/21878497/Combine-child-records-into-flat-table.html) regarding a function in an MSSQL database. The function got lost during a database transfer, and I need to recreate it. I am now using MS SQL Server Management Studio 10, and I am confused by all the choices for functions. Based on the original question (see link), where should I enter this function into the database?
Thank you.
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This worked almost perfectly. I am getting an error in the code when I execute it.
The actual function and the select statement appears in the attached snippet. When I execute the select statement, I get this error:
Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
Any suggestions?
The actual function and the select statement appears in the attached snippet. When I execute the select statement, I get this error:
Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
Any suggestions?
SELECT Book.*, dbo.udf_AuthorList(Book.BookID) AS Authors FROM Book
CREATE FUNCTION dbo.udf_AuthorList (@ProductID int)
RETURNS varchar(500)
BEGIN
DECLARE @List varchar(500)
SET @List = ''
SELECT @List = @List + FirstName + ' ' + Lastname + ', '
FROM isrbksh_admin.qprAuthors
WHERE ProductID = @ProductID
ORDER BY LastName
SET @List = SUBSTRING(@List, 1, LEN(@List) - 1)
RETURN @List
END
When you execute this SELECT statement, chance that error may occur depending on the value of @List variable in that function.
Can you spot, which record is causing this error in that select statement. ?
SELECT Book.*, dbo.udf_AuthorList(Book.Bo
Post the result of this query.
Raj
Can you spot, which record is causing this error in that select statement. ?
SELECT Book.*, dbo.udf_AuthorList(Book.Bo
Post the result of this query.
Raj
SELECT TOP 5 FirstName, Lastname
FROM isrbksh_admin.qprAuthors
ORDER BY LEN(FirstName + ' ' + Lastname)
ASKER
Using your latest code, I was able to determine that some child records were missing, and that caused the data error. I added the child records, and everything works properly now. Thank you for your help in getting this to work.
To test your function with the SELECT query copy & paste the select query from that link in another 'Query Window' and press F5
Raj