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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rajkumar GsSoftware 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.