Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2003-03-07
3
Medium Priority
?
232 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
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

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

580 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