Help spoc or UserDefinedFunction

[kiwiCoder]

My db has a table with two fields ClientKey(Pk) and ClientType(char). By providing the ClientType variable I need to return the ClientKey(integer), as this is a single value I see no reason to return a rs, a simple int value should be less resource intensive?. I'm developing in vb6, example of what I've tried below.

CREATE PROCEDURE spGetClientTypeKey
@ClientKey1 int OUTPUT,@ClientType varchar
AS
begin
set @ClientKey1 = dbo.ufcnGetaValue (@ClientType)
end
return @ClientKey1
GO
/*
CREATE FUNCTION ufcnGetaValue
(@ClientType varchar)
RETURNS INTEGER
begin
DECLARE @ClientKey INTEGER
select @ClientKey = ClientTypes.ClientTypekey
FROM ClientTypes
WHERE ClientTypes.ClientType= @ClientType
RETURN @ClientKey
end
*/

Called from vb like this
With cmd
.ActiveConnection = cnxnCroppak
.CommandType = adCmdStoredProc
.CommandText = "spGetClientTypeKey"
Set parReturnParameter = _
.CreateParameter("@ClientKey1", adInteger, adParamOutput)
' ' Return Parameter always the First Parameter
.Parameters.Append parReturnParameter
Set parInputParameter = _
.CreateParameter("@ClientType", adChar, adParamInput, 50, sClientType)
' ' Return Parameter always the First Parameter
.Parameters.Append parInputParameter
.Execute , parInputParameter, ADODB.adExecuteNoRecords
a = cmd.Parameters(0).Name
a = parReturnParameter
a = cmd.Parameters(0).Value
a = .ActiveConnection
End With
a = cmd.Parameters(0).Value

Any help greatly appreciated

 

[donutman]

CREATE PROCEDURE spGetClientTypeKey
@ClientType char(1)
AS
return isnull((select ClientTypes.ClientTypekey
FROM ClientTypes
WHERE ClientTypes.ClientType= @ClientType),0)

I'm assuming ClientType is Char(1) and that ClietnTypeKey=0 is ok for a NOT FOUND flag. But I'd also consider creating a collection that you build when the form is loaded that can do the translation of ClientType to ClientKey if there is a reasonable number of them. Why keep accessing the DB?
-Karl