?
Solved

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

Posted on 2003-03-07
3
Medium Priority
?
225 Views
Last Modified: 2008-02-01
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 ...
0
Comment
Question by:alaw005
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 18

Accepted Solution

by:
nigelrivett earned 400 total points
ID: 8088127
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
 
LVL 23

Expert Comment

by:adathelad
ID: 8088955
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
 

Author Comment

by:alaw005
ID: 8088979
thanks for that ... so simple in the end :)
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question