INITCAP Function

Do you mean you want to rename all your stored procedures, the text of the stored procedures, functions and text of the function at once?

Frank
http://www.insidesql.de
http://www.familienzirkus.de

If you want to display these data in some kind of report I suggest you do this at the client, not at the server. Should be an easy exercise for front-end tools.

Frank
http://www.insidesql.de
http://www.familienzirkus.de

FOund this code from Developers Dex site, contributed by
Author: Nagabhushanam


-------
CREATE function INITCAP (@inString varchar(4000) )
/* 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(4000)
as
BEGIN
DECLARE @i int, @c char(1),@result varchar(255)
SET @result=LOWER(@inString)
SET @i=2
SET @result=STUFF(@result,1,1,UPPER(SUBSTRING(@inString,1,1)))
WHILE @i<=LEN(@inString)
BEGIN
SET @c=SUBSTRING(@inString,@i,1)
IF (@c=' ') OR (@c=';') OR (@c=':') OR (@c='!') OR (@c='?') OR (@c=',')OR (@c='.')OR (@c='_')
IF @i<LEN(@inString)
BEGIN
SET @i=@i+1
SET @result=STUFF(@result,@i,1,UPPER(SUBSTRING(@inString,@i,1)))
END
SET @i=@i+1
END
RETURN @result
END
----------

_________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Not everything that can be done in Oracle is good [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br />However, since you are on SQL2k you can use a table variable.<br /><br />What I meant, was that you are required to loop through your char column and that's one thing SQL Server is not really good at, I mean this looping is more easily done within your programming language<br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>

quote:Originally posted by satya

FOund this code from Developers Dex site, contributed by
Author: Nagabhushanam


-------
CREATE function INITCAP (@inString varchar(4000) )
/* 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(4000)
as
BEGIN
DECLARE @i int, @c char(1),@result varchar(255)
SET @result=LOWER(@inString)
SET @i=2
SET @result=STUFF(@result,1,1,UPPER(SUBSTRING(@inString,1,1)))
WHILE @i<=LEN(@inString)
BEGIN
SET @c=SUBSTRING(@inString,@i,1)
IF (@c=' ') OR (@c=';') OR (@c=':') OR (@c='!') OR (@c='?') OR (@c=',')OR (@c='.')OR (@c='_')
IF @i<LEN(@inString)
BEGIN
SET @i=@i+1
SET @result=STUFF(@result,@i,1,UPPER(SUBSTRING(@inString,@i,1)))
END
SET @i=@i+1
END
RETURN @result
END
----------

hm....that's what I meant. Will surely work, but turns it into row by row processing.
And it's really more of a presentational issue and as such job of the front end.


Frank
http://www.insidesql.de
http://www.familienzirkus.de

You must find out the ways to do it and lets hope Originator feels this code will help.

_________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Hey, consider this just my $0.02 cents [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>

quote:Originally posted by Twan


client side would be the simplest option, but

If you needed extra speed you could also create a tiny dll in VB using its StrConv( str, VbStrConv.ProperCase ), then register that dll as an external stored procedure

Cheers
Twan

external stored procedure or extended stored procedure?

You can't write extended stored procedures in VB, because VB doesn't create true Win32 executables and it doesn't allow you to export functions like C++ or C.
However, you can write the Dll in VB and instantiate it via the sp_OA* stored procedures.

Frank
http://www.insidesql.de
http://www.familienzirkus.de

Sorry, should have mentioned that I don't have experience with .Net.
I'll take that road next year.

Can you do this with VB.Net?
Hm...if so I'm a little bit scared what happens when you can programm Yukon with it.

Frank
http://www.insidesql.de
http://www.familienzirkus.de

Try something like


__declspec(dllexport) ULONG __GetXpVersion()
{
return ODS_VERSION;
}
taken from BOL

I'm pretty sure when trying to call a VB Dll function, you'll receive something like

"Cannot find the function blabla in the library blabla.dll. Reason: 127(The specified procedure could not be found.)."

because even if there is an entry point found like DllMain, the function itself will not be found.

Don't get me wrong, I would like to be mistaken, because it would potentially make things easier on the one side if VB would work, on the other side I think it's ok that only C or C++ could be used.

Why? Because poorly programmed extended stored procedures (no matter what language) are likely to crash SQL Server, as is mentioned here
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odssql/ods_6_con_01_9rxv.asp

Here are some more useful links on this

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odssql/ods_6_con_01_3fhq.asp
http://search.microsoft.com/search/results.aspx?View=msdn&st=a&s=2&c=4&qu=extended stored procedures

I hope you're right on Yukon, because I'll bet you'll come across many SQL Server CLR stored procedures containing


Do While Not recordset.EOF
..
Loop
I wonder how this is consistent with set-based theory???

Just my $0.02 cents.

Frank
http://www.insidesql.de
http://www.familienzirkus.de