Concatenate SQL Server stored procedure result with additional text

Question

I'd like to take the result of a stored procedure and concatenate it with additional text in as simple a way as possible.

Something along these lines:

Stored procedure:

CREATE PROCEDURE [dbo].[sp_UserFriendlyText] @Text varchar(100)
AS
BEGIN
SELECT CASE @Text
     WHEN 'Foo' THEN 'Beginning'
     WHEN 'bar' THEN 'End'
     ELSE 'Text Not Found'
END
END

It would be referenced like so:

DECLARE @Var1 varchar(100)
SET @Var1 = 'Foo'    
EXEC dbo.sp_UserFriendlyText @Var1 + ' is the best'

With the desired result being "Beginning is the best".

It complains my syntax is incorrect. I've tried adding parenthesis around the exec statement to create precedence, but it still doesn't work.

Because the sproc will be referenced in multiple queries I'd like to run it without the output declaration if at all possible (just to make things cleaner).

System is SQL Server 2008 R2.

Thanks!

Michael Fredrickson · Accepted Answer · 2011-11-10 21:22:33Z

6

I think a UDF would be a better choice in this situation for the cleanest syntax...

CREATE FUNCTION [dbo].[udf_UserFriendlyText](@Text VARCHAR(100))  
RETURNS VARCHAR(100)
AS  
BEGIN 
    DECLARE @ReturnvValue VARCHAR(100)
    SELECT @ReturnvValue = CASE @Text
        WHEN 'Foo' THEN 'Beginning'
        WHEN 'bar' THEN 'End'
        ELSE 'Text Not Found'
    END

    RETURN @ReturnvValue
END
GO

Then to call...

DECLARE @Var1 VARCHAR(100)
SET @Var1 = 'Foo'
SET @Var1 = dbo.udf_UserFriendlyText(@Var1) + ' is the best'
SELECT @Var1

answered Nov 10, 2011 at 21:22

Michael Fredrickson

37.5k6 gold badges95 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nth Over a year ago

Didn't even think of using a UDF, but it's clean and simple. Perfect!

Collectives™ on Stack Overflow

Concatenate SQL Server stored procedure result with additional text

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related