Adding function in SQL Server Management Studio

I asked a question some time ago (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21878497.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.
Lev SeltzerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
Open 'SQL Server Management Studio. Open a new Query Window.

Copy and Paste that function there. On the top part, near the toolbox, you can see the combobox containing the list of databases available in your server.

Select the database from the list to which you want to create the function.

Now press F5 or click 'Execute'

This will create that function into the selected database

Hope this is what you are asking about
Raj
0
 
Rajkumar GsSoftware EngineerCommented:
Now if there is no error message on bottom message window, it's created.

To test your function with the SELECT query copy & paste the select query from that link in another 'Query Window' and press F5

Raj
0
 
Lev SeltzerAuthor Commented:
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?

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

Open in new window

0
 
Rajkumar GsSoftware EngineerCommented:
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.BookID) AS Authors FROM Book

Post the result of this query.

Raj
SELECT TOP 5 FirstName, Lastname
FROM isrbksh_admin.qprAuthors 
ORDER BY LEN(FirstName + ' ' +  Lastname)

Open in new window

0
 
Lev SeltzerAuthor Commented:
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.
0
All Courses

From novice to tech pro — start learning today.