How can you assign the results of stored procedure to a variable


I am trying to create a stored procedure which must return a recordset containing a single varchar output value that can be assigned to a variable in another sp I am calling it from. I have tried the following:

Create Procedure "sp_LPG_Format"
          @INPUT1 varchar(10),
          @INPUT2 varchar(10)
          @NewValue varchar(40) = null OUTPUT
     SET @NewValue  = @INPUT1 + @INPUT2
     return @NewValue

I need to be able to call this from within another stored procedure and assign its output value to a variable, I have tried using

EXEC @a = sp_LPG_Format 'INPUT 1', 'INPUT 2'

but I get the following error:

Syntax error converting the varchar value 'XXXX' to a column of data type int.

I noticed that SQL Server 2000 has User-Defined Functions that may do what I want but I only have SQL Server 7.0

please help, its driving me up the wall ...
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Create Procedure "sp_LPG_Format"
         @INPUT1 varchar(10),
         @INPUT2 varchar(10)
         @NewValue varchar(40) OUTPUT
    SET @NewValue  = @INPUT1 + @INPUT2

declare @a varchar(40)
EXEC sp_LPG_Format 'INPUT 1', 'INPUT 2', @a output

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
There is a missing comma in the CREATE statement too.
Alos, take into account NULL values.

        @INPUT1 VARCHAR(10),
        @INPUT2 VARCHAR(10),
        @NewValue VARCHAR(40) OUTPUT
   SET @NewValue = ISNULL(@INPUT1, '') + ISNULL(@INPUT2, '')

Then, to call it:
DECLARE @pstrOutput VARCHAR(40)
EXEC sp_LPG_Format 'INPUT 1', 'INPUT 2', @pstrOutput OUTPUT
alaw005Author Commented:
thanks for that ... so simple in the end :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.