2

In SQL Server 2008 (TSQL), I've created a stored procedure like this:

CREATE PROCEDURE SP_1_10_2
AS
declare @mostValuableBook nvarchar(255)
SELECT @mostValuableBook = Name
FROM books
WHERE price =
    ( SELECT MAX(price)
      FROM books
      WHERE izd LIKE '%BHV%' );
return @mostValuableBook
GO

But, when I'm trying to execute it:

declare @x nvarchar(255)
EXECUTE @x = SP_1_10_2;
SELECT 'The most expensive BHV book:', @x AS 'Name'
GO

I'm getting an error:

Conversion failed when converting the nvarchar value 'Internet Explorer 3 original' to data type int.

It seems like the problem is in the line

EXECUTE @x = SP_1_10_2;

Can you please tell me what's wrong? Why is it trying to convert to int?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Aremyst
  • 1,480
  • 2
  • 19
  • 33
  • 1
    The return value of a stored procedure can only be an `int`. You need to `SELECT` it or use an output parameter instead. – Martin Smith Nov 26 '12 at 18:59
  • possible duplicate of [How to call a stored procedure and return a value?](http://stackoverflow.com/questions/4558812/how-to-call-a-stored-procedure-and-return-a-value) – Martin Smith Nov 26 '12 at 19:01
  • Ok, thnaks. Can you please tell me, can I return a full record ? (SELECT * FROM books...) Through output parameter or else. – Aremyst Nov 26 '12 at 19:04
  • 1
    @user1433660 - The output parameter is one specific field have you, you basically assign the value in the sproc to a single field. See my sample below. – JonH Nov 26 '12 at 19:06

2 Answers2

6

RETURN cannot be used to return nvarchar / varchar such as you have. RETURN is used to return an integer, this can be expressed as some sort of status code 1=True / 0=False. Read more about return here: http://msdn.microsoft.com/en-us/library/ms174998.aspx

In your case, you simply need to use OUTPUT variables which is similiar to pass-by-ref in C# or C++. You pass the variable to the sproc, the sproc modifies it, and you get the expected results after a SELECT....

Change it so that your parameters becomes an output parameter:

CREATE PROCEDURE SP_1_10_2
@mostValueableBook nvarchar(255) output
AS
SELECT @mostValuableBook = Name
FROM books
WHERE price =
    ( SELECT MAX(price)
      FROM books
      WHERE izd LIKE '%BHV%' );
SELECT @mostValuableBook
GO

Call it like so:

DECLARE @theValBook nvarchar(255)
EXECUTE SP_1_10_2 @mostValuableBook = @theValBook output

Then you can say:

SELECT 'Most expensive book is', @theValBook

JonH
  • 32,732
  • 12
  • 87
  • 145
1

You can also create a function to return the value you desire instead of relying on numeric return codes. SQL Functions come in quite handy. See example below which returns the last name with the highest client id using the LIKE operator

Use MYDB
GO

CREATE Function fn_LastClientIdByName
(
@nameLike NVARCHAR(10)
)
RETURNS NVARCHAR(100)
AS 

BEGIN
DECLARE @result nvarchar(100)
DECLARE @clientName NVARCHAR(100)

SELECT top 1  @clientName = [clientLast] + ' ' + [clientFirst]   
FROM [dbo].[duiClientOnly]
WHERE clientLast like @nameLike + '%'
order by clid desc

select @result = @clientName
return @result
END
RickIsWright
  • 11
  • 3