question

fairyliquid avatar image
fairyliquid asked

Set OUTPUT params on Stored Proc to return char(5)

I'm struggling to work out where to put the OUTPUT parameters in a stored proc that should call another stored proc. This is whhere I got to but I keep getting the error - *Conversion failed when converting the varchar value 'BLAH' to data type int.* ALTER PROCEDURE [dbo].[TestOutPut] @contactName varchar(30) AS BEGIN DECLARE @output char(5); SET NOCOUNT ON; EXEC GetCustomerId @contactName, @output OUTPUT SELECT @output END Above stored proc calls this stored proc: ALTER PROCEDURE [dbo].[GetCustomerId] @contactName varchar(30), --OUTPUT @custid char(5) OUTPUT AS BEGIN --DECLARE @custid char(5); SET NOCOUNT ON; SELECT @custid = CUSTID FROM [dbo].[CUSTOMER] WHERE CONTACT = @contactName RETURN @custid; END Any help would be greatly appreciated.
output
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
There's nothing really wrong with your stored procedures, the output clauses are in the right place. The reason you are getting the error is that the CUSTID field is not an integer, and the RETURN can only be used with an integer. For what you are doing you don't need to use RETURN, just remove this line and the procedures will work fine.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Brilliant! Thanks for your speedy response. :D
0 Likes 0 ·
No worries :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.