Parameter data type problem

[jararaca]

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!

 

[DotNetGnat]

try this:

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

-DNG

 

[mrdenny]

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.

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.

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)

http://www.mrdenny.com

 

[jararaca]

Thanks! I'll try these things.

 

[mrdenny]

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)

http://www.mrdenny.com