? about how to get value from sp

  • Thread starter Thread starter EManning
  • Start date Start date
E

EManning

Using A2K and SQL2000

I have an sp that formats a doctor's name and their specialty like so:

John Doe (Radiology)

I need to be able to abbreviate the specialty. Radiology would be '(Rad)',
Anesthesiology would be '(A)', and so on. Since I have other sp's that
format the name this way and the specialty list is very long, I'd like to be
able to pass the specialty to another sp or function and return the
abbreviation.

Since I'm using an adp, I take it I can't use a user-defined function.
Could I accomplish this by passing the specialty another sp and returning
the abbreviation? What would the syntax be for doing this?

Here's a snippet of one of the stored procedures that formats the name.
Thanks for any help or advice.

SELECT tblDemographics.LastName + ', ' + tblDemographics.FirstName +

case

when tblDemographics.ResidencyTrack = 'Anesthesiology' then ' (A)'

when tblDemographics.ResidencyTrack = 'Radiology' then ' (R)'

else ' ' + '(' + tblDemographics.ResidencyTrack + ')'

end as ResidentName
 
Your code snippet would typically go into one stored procedure, and as I understand you would like to call it from another stored procedure

IN SQL Query Analyzer you would typically create one stored procedure

Create PROCEDURE TranslateProfession @Profession varchar(100), @Abbreviation varchar(10) outpu
A

BEGI
--Your existing code goes here
--You take the long description from @Profession input parameter and return the result in @Abbreviation, an output paramete

EN


Whenever you want to call the above stored procedure you can write a line like this

exec dbo.TranslateProfession @ParameterWithLongName, @ParameterShortName outpu

Before calling put your long description into @ParameterWithLongName. The stored procedure will run and put the resul
into @ParameterShortName
@ParameterWithLongName and @ParameterShortName must be declared in the calling stored procedur
They should be declared with the same type and size as in the called procedure, varchar(100) and varchar(10)

Tor





----- EManning wrote: ----

Using A2K and SQL200

I have an sp that formats a doctor's name and their specialty like so

John Doe (Radiology

I need to be able to abbreviate the specialty. Radiology would be '(Rad)'
Anesthesiology would be '(A)', and so on. Since I have other sp's tha
format the name this way and the specialty list is very long, I'd like to b
able to pass the specialty to another sp or function and return th
abbreviation

Since I'm using an adp, I take it I can't use a user-defined function
Could I accomplish this by passing the specialty another sp and returnin
the abbreviation? What would the syntax be for doing this

Here's a snippet of one of the stored procedures that formats the name
Thanks for any help or advice

SELECT tblDemographics.LastName + ', ' + tblDemographics.FirstName

cas

when tblDemographics.ResidencyTrack = 'Anesthesiology' then ' (A)

when tblDemographics.ResidencyTrack = 'Radiology' then ' (R)

else ' ' + '(' + tblDemographics.ResidencyTrack + ')

end as ResidentNam
 
Back
Top