CODEWITHSUNDEEP
sql-server
Autumn
Autumn

Reputation: 585

Pass procedure output to variable

I am new to SQL server. I am passing procedure output to a variable but always get "NULL". Is there anything wrong with the procedure? I tested to declare the initial value of etext as 'a', it returns a.

  create procedure encrypt 
    @ptext as varchar(500)
    , @etext as varchar(500) OUTPUT
  as 
  begin
set nocount on
declare @key as tinyint = 3
declare @pc as varchar(1)
declare @i as smallint = 1
declare @n as smallint

set @n = len(@ptext)

while @i <= @n
begin 
set @pc = substring (@ptext, @i, 1)

if ascii(@pc) between 48 and 57 
begin
    if ascii(@pc) + @key < 58
        set @pc = char((ascii(@pc) + @key))
    else 
        set @pc = char((ascii(@pc) + @key)-10)
end

else if ascii(@pc) between 65 and 90 
begin
    if ascii(@pc) + @key < 91
        set @pc = char((ascii(@pc) + @key))
    else 
        set @pc = char((ascii(@pc) + @key)-26)
end

if ascii(@pc) between 97 and 122 
begin
    if ascii(@pc) + @key < 123
        set @pc = char((ascii(@pc) + @key))
    else 
        set @pc = char((ascii(@pc) + @key)-26)
end

set @etext = @etext + @pc
set @i = @i + 1
end 
end


DECLARE @etext char(500);
exec encrypt 'time', @etext OUTPUT
select @etext

Upvotes: 0

Views: 55

Answers (1)

Szymon
Szymon

Reputation: 43023

As you're concatenating the strings in set @etext = @etext + @pc, you need to make sure that @etext is not null or otherwise the result will be null.

Change that line to

et @etext = isnull(@etext, '') + @pc

This way you'll be able to pass null into your procedure and it will still work.

Upvotes: 2

Related Questions