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

Hi,

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
     )
As
     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 ...
alaw005Asked:
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.

nigelrivettCommented:
Create Procedure "sp_LPG_Format"
    (
         @INPUT1 varchar(10),
         @INPUT2 varchar(10)
         @NewValue varchar(40) OUTPUT
    )
As
    SET @NewValue  = @INPUT1 + @INPUT2
    return
go

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

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
adatheladCommented:
There is a missing comma in the CREATE statement too.
Alos, take into account NULL values.
Try:

CREATE PROCEDURE sp_LPG_Format
   (
        @INPUT1 VARCHAR(10),
        @INPUT2 VARCHAR(10),
        @NewValue VARCHAR(40) OUTPUT
   )
As
   SET @NewValue = ISNULL(@INPUT1, '') + ISNULL(@INPUT2, '')
   RETURN
GO

Then, to call it:
DECLARE @pstrOutput VARCHAR(40)
EXEC sp_LPG_Format 'INPUT 1', 'INPUT 2', @pstrOutput OUTPUT
0
alaw005Author Commented:
thanks for that ... so simple in the end :)
0
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.