How do I call my function from a simple stored procedure

Question

How do I call my function from a simple stored procedure

ron.williams

Newbie

Points: 5
More actions
August 3, 2009 at 11:34 am

#95578

:unsure:I am trying to call my udf scalar function which returns a char value from a stored procedure. How can I do this with a simple stored procedure. I am new to sql server programming. Here is my function.
/****** Object: UserDefinedFunction [dbo].[Initcap] Script Date: 08/03/2009 11:17:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Initcap]
(
@inString VARCHAR(MAX)
)
/* INITCAP returns char, with the first letter of each word
in uppercase, all other letters in lowercase. Words are
delimited by white space or characters that are not
alphanumeric
*/
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE
@iINT,
@j-2INT,
@cCHAR(1),
@result VARCHAR(4000),
@StrLength int
SET @StrLength = LEN(@inString)
SET @result = LOWER(@inString)
SET @i = 2
SET @result = STUFF(@result,1,1,UPPER(SUBSTRING(@inString,1,1)))
WHILE @i < @StrLength
BEGIN
SET @c = SUBSTRING(@inString,@i,1)
IF (@c = ' ') OR
(@c = ';') OR
(@c = ':') OR
(@c = '!') OR
(@c = '?') OR
(@c=',' ) OR
(@c = '.') OR
(@c= '_')
BEGIN
SET @j-2 = @i
WHILE 1 = 1
BEGIN
if ASCII(UPPER(SUBSTRING(@inString,@j,1))) BETWEEN 65 AND 90
BEGIN
SET @result = STUFF(@result, @j-2 ,1,UPPER(SUBSTRING(@inString,@j,1)))
BREAK
END
SET @j-2 = @j-2+ 1
END
SET @i = @j-2
END
SET @i = @i + 1
END
RETURN @Result
END

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

You can invoke your function with a simple select statement. When you want to use a user defined function, you have to specify the function’s schema. Assuming your function is located on the dbo schema, you can invoke it this way:

Select dbo.InitCap ('This is just a demo string')

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323547 More actions · Answer 2

and here's how you'd use your function in an update statement:

UPDATE MyTable

Set MyColumn = dbo.InitCap(MyColumn)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!