CODEWITHSUNDEEP
sql-server
MrChudz
MrChudz

Reputation: 1205

Stored procedure doesn't return correct value

I have stored procedure:

ALTER PROCEDURE ReplaceHtmlEntities
    @companyName  NVARCHAR(200)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @newCompanyName NVARCHAR(200);

    SET @newCompanyName = @companyName;
    SET @newCompanyName = REPLACE(@newCompanyName,'&','&');
    SET @newCompanyName = REPLACE(@newCompanyName,'"','"');
    SET @newCompanyName = REPLACE(@newCompanyName,''','''');
    SELECT @newCompanyName;
END
GO

But it returns 0:

DECLARE @companyName nvarchar(200), @result nvarchar(200)

SET @companyName = N'Company name &'

EXEC    @result = [dbo].[ReplaceHtmlEntities] @companyName

SELECT  @result

@result should be 'Company name &' but it gives me '0' - why ?

Upvotes: 2

Views: 1219

Answers (3)

Charles Bretana
Charles Bretana

Reputation: 146603

Where you call it, eliminate the @result = and the line select @result.

The select inside the SP will generate the data for you.

DECLARE @companyName nvarchar(200), @result nvarchar(200)
SET @companyName = N'Company name &'
EXEC [dbo].[ReplaceHtmlEntities] @companyName

But if you made it a UDF it would be better,,,

 Create Function dbo.ReplaceHtmlEntities(@input varChar(1000) )
 returns table As
 Return (Select Replace(Replace(REPLACE(
                @input,'&','&'), 
                       '"','"'),
                       ''','''') OutVal)

Then all you need to do to call it is 

Select OutVal from dbo.ReplaceHtmlEntities('Put the companyname here')

Upvotes: 1

Martin Brown
Martin Brown

Reputation: 25329

Stored procedures in SQL Server can only return integers. The SELECT at the end of the stored procedure is going to create a result set not a return value.

To output the value form a stored procedure as a Output Parameter declare the procedure like this:

ALTER PROCEDURE ReplaceHtmlEntities
    @companyName  NVARCHAR(200),
    @newCompanyName NVARCHAR(200) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SET @newCompanyName = @companyName;
    SET @newCompanyName = REPLACE(@newCompanyName,'&','&');
    SET @newCompanyName = REPLACE(@newCompanyName,'"','"');
    SET @newCompanyName = REPLACE(@newCompanyName,''','''');
END
GO

DECLARE @companyName nvarchar(200) = 'A & B'

EXEC ReplaceHtmlEntities @companyName = @companyName, @newCompanyName = @companyName OUTPUT

SELECT  @companyName

GO

Upvotes: 0

Henrique
Henrique

Reputation: 415

Stored procedure can only return integers (ref).

You could return it from an output parameter, but I think you should create a function:

CREATE FUNCTION ReplaceHtmlEntities
    (@companyName  NVARCHAR(200))
RETURNS NVARCHAR(200)
AS
BEGIN
    DECLARE @newCompanyName NVARCHAR(200);

    SET @newCompanyName = @companyName;
    SET @newCompanyName = REPLACE(@newCompanyName,'&','&');
    SET @newCompanyName = REPLACE(@newCompanyName,'"','"');
    SET @newCompanyName = REPLACE(@newCompanyName,''','''');

    RETURN @newCompanyName;
END
GO

And this is how you can execute a function:

DECLARE @companyName nvarchar(200), @result nvarchar(200)
SET @companyName = N'Company name &'

SET @result = [dbo].[ReplaceHtmlEntities](@companyName)

SELECT  @result

Upvotes: 1

Related Questions