User Defined Functions in SQL 7 1

[Steve101]

I know that these do not exist in SQL 7 and do exist in SQL 2000.

I am writing an application using Microsoft Access ADP frontend, and need to know if the equivalent functionality can be easily done in SQL 7 using stored procedures.

For example, how would I write a generic stored procedure to concatenate a SurName and FirstName where say if the firstname were Null, the comma separater would be suppressed; eg.

FirstName: John
Surname: Smith
would yield "Smith, John"

but
FirstName: Null
Surname: Smith
would simply yield: "Smith" not "Smith,"

I'd like to be able to do this as if I was using a custom developed function (in SQL 7); eg:

Select FullName(Surname, FirstName) ....

Where FullName is my 'joining procedure'. Whats the best way to do this against SQL 7?

Thanks in anticipation,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[kutty007]

This should work


DECLARE @FNAME CHAR(20), @LNAME CHAR(20)
SELECT @FNAME = '', @LNAME = 'KARTHIK'

SELECT CASE IsNull(@FNAME,'') WHEN '' THEN '' ELSE RTrim(@FNAME) + ', ' END + RTrim(@LName)


SELECT @FNAME = 'SUNDAR ', @LNAME = 'KARTHIK'

SELECT CASE IsNull(@FNAME,'') WHEN '' THEN '' ELSE RTrim(@FNAME) + ', ' END + RTrim(@LName)

 

[Steve101]

Thats great; many thanks. Is there a way that I can encapsulate that in something equivalent to a user defined function (which I know doesnt exist in sql7) to allow me to use it elswhere eg. as makename(firstname, surname)?

Thanks again,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[NoCoolHandle]

For a single name or multiple? Do you need a where clause? Do you want the output in a recordset or via an output parameter?

Rob

 

[Steve101]

What I'd like to be able to do functionally is something like this:

SELECT MakeName(Surname, FirstName), DOB
FROM tblSomeTable
WHERE ...

Then somewhere else:

SELECT MakeName (SName, FName), Phone
FROM tblSomeOtherTable
WHERE ...

In other words, it would be nice to be able to code the logic once, then re-use it elsewhere, as one would a user defined function. Just wondering whether its possible to emulate this capability in SLQ 7.

What is the significance to the above of the output being via a recordset, or via an output parameter?

Cheers,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[NoCoolHandle]

Ahh the answer then is NO, can't be done. That specific need was one of the great reasons to change to 2000.

MSDE 2000 supports UDF's and is free.. But you have a limit of 5 worker threads and 2 gig database size etc,etc,etc..

How is the winter?

Rob
EX Kiwi

 

[Steve101]

Thanks Rob; as I suspected, but was hoping there was an elegant general workaround. A couple of other simple questions if I might:

Does 5 worker threads translate to 5 simultanious online users into the backend?

MSDE 2000 has no Enterprise Manager capability. Is this correct?

What I'm trying to assess is whether its worth developing against 2000 in the first place. The client has said that he will go 2000, but not immediately as he has other priorities, technical and financial. I could meanwhile develop against an on site MSDE 2000 installation, which would probably be adequate for development?

Finally, does MSDE give reasonable representative performance, or is it deliberately crippled by MS?

Cheers again,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)