Tuesday, 28 August 2018

The stored procedure does not return the 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 ?

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

0 comments:

Post a Comment