CODEWITHSUNDEEP
sqlsql-servert-sqlstored-proceduresprocedure
alcoss
alcoss

Reputation: 3

Stored procedures, return different number of columns

I wrote a stored procedure but it doesn't work as I need

CREATE OR ALTER PROCEDURE get_info 
@id int = NULL,
@lang nvarchar(15) = NULL
AS
BEGIN
 SET NOCOUNT ON;
 SELECT id,
   CASE @lang
    WHEN 'ru' THEN text_ru
    WHEN 'en' THEN text_en
    ELSE (text_en, text_ru)
   END
 FROM task_text
 WHERE (@id IS NULL OR id = @id);
END

It doesn't work because case can't return 2 values.

My perfect stored procedure should work like this:

exec get_info --> full table id, text_ru, text_en
exec get_info @id = 1 --> specific id, text_ru, text_en
exec get_info @lang = 'ru' --> full id, text_ru
exec get_info @id = 1, @lang = 'ru'--> specific id, text_ru, @lang

Upvotes: 0

Views: 277

Answers (1)

Zafor
Zafor

Reputation: 353

You can try by writing the procedure like this way

CREATE OR ALTER PROCEDURE get_info 
    @id int = NULL,
    @lang nvarchar(15) = NULL
AS
BEGIN
SET NOCOUNT ON;

IF @lang = 'ru'
BEGIN
   SELECT id,text_ru
   FROM task_text
   WHERE (@id IS NULL OR id = @id);
END
IF @lang = 'en'
BEGIN
   SELECT id,text_en
   FROM task_text
   WHERE (@id IS NULL OR id = @id);
END
IF @lang IS NULL
BEGIN
   SELECT id,text_ru,text_ru
   FROM task_text
   WHERE (@id IS NULL OR id = @id);
END
END

Upvotes: 1

Related Questions