Dynamic Dysfunction - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic Dysfunction

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-11-28 : 14:43:53

Given the following format in the SQA:

Create function CAPS (@string nvarchar(100))
returns nvarchar(100)
as
begin
declare
@OUTCAPS nvarchar (100)
select @OUTCAPS = UPPER(@string)
return @OUTCAPS
end
GO

DROP TABLE #Autos
CREATE TABLE #Autos
(_Vehicle nvarchar (25) NULL)
GO

INSERT #Autos VALUES ('1955 desoto fireflite ')
INSERT #Autos VALUES ('1967 chevrolet impala ')
INSERT #Autos VALUES ('1968 chrysler imperial ')
GO

UPDATE #Autos
SET _Vehicle = CAPS(_Vehicle)
GO

SELECT * FROM #Autos
GO

I keep getting: 'CAPS' is not a recognized function name.

Yet I've inserted it in the beginning. Is there any other way to do a dynamic function?

Thanks for any assistance you can render....

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2005-11-28 : 14:49:51

If you are running that under the same BATCH you may need to have a COMMIT Transaction after the create for the function.

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-11-28 : 14:54:39

I'm puzzled; since there is NO 'Begin Transaction', how would COMMIT Transaction resolve this?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-28 : 15:28:19

When calling UDFs, you must use the two-part naming convention, which is probably dbo.CAPS.

Tara Kizer
aka tduggan

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-11-28 : 15:40:05

Thanks again, Tara! You're right, I was missing a piece!

As usual it's always something small; never anything big.

And now it finally works!!!

Thanks!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources