Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Parameter data type problem

Status
Not open for further replies.

jararaca

Programmer
Jun 1, 2005
159
US
Hi--

I've got the following sp (not sure how to demark code in this forum):

=======================================================
CREATE PROCEDURE dbo.UP_Get_Program

@PACEId INT

AS

DECLARE
@Program VARCHAR(7)

SET @Program = (SELECT Program FROM MedMgmt.dbo.UV_MMQ_Members_With_Programs WHERE ClientId = @PACEId)

RETURN @Program
=======================================================

If I run this sp as scaler in my .net app, it returns the value 'SMI' for program, as it should. But If I run it as you see it above with the return parameter, it gives me this error:

============================================
Syntax error converting the varchar value 'SMI' to a column of data type int. Warning: Null value is eliminated by an aggregate or other SET operation.
============================================

The parameter is being set up properly as varchar(7), which is the datatype and size of the program field. I would like to run it with the Return value, because I need to add other things to this query and possibly change the value of program along the way, so any help on this would be much appreciated.

Thanks!

 
try this:

Code:
SELECT @Program = Program FROM MedMgmt.dbo.UV_MMQ_Members_With_Programs WHERE ClientId = @PACEId

-DNG
 
SQL expects return paramaters to be an integer which can be captured via the T/SQL @@ERROR command.

Recreate your procedure like this and use an output variable.

Code:
CREATE PROCEDURE dbo.UP_Get_Program

@PACEId        INT
@Program    VARCHAR(7) OUTPUT

AS

        
SET @Program = (SELECT Program FROM MedMgmt.dbo.UV_MMQ_Members_With_Programs WHERE ClientId = @PACEId)

go

Then run your procedure and pull the output variable.
Code:
declare @Program varchar(7)
exec dbo.UP_Get_Program 7, @Program=@Program OUTPUT
select @Program

In your .NET code you'll need to setup an output variable and set a variable to equal the output variable.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)
[noevil]
 
no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)
[noevil]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top