question

zoeOhara-0533 avatar image
0 Votes"
zoeOhara-0533 asked zoeOhara-0533 commented

How to return the results of a stored procedure

Hi,

I am a stored procedure that generates a random character:

 create PROCEDURE  usp_randchar
  @some_length int = 0,
  @out varchar(MAX) output
 as
    
 WITH random_char(c) AS (
    SELECT char(convert(int, rand(abs(convert(int, convert(varbinary, newid())))) * 60.0) + 32)
 ),
 t1 (char_count, s) AS (
    SELECT 0, convert(nvarchar(MAX), (SELECT c FROM random_char))
    UNION ALL
    SELECT char_count + 1, s + (SELECT c FROM random_char) AS s 
    FROM t1 WHERE char_count < 100
 )
 SELECT s FROM t1 WHERE char_count = @some_length
 return

But I try to assign the output to a variable I just get a Null value:

 DECLARE @DFCLNT VARCHAR(1) 
 EXECUTE usp_randchar 0, @out = @DFCLNT output 
 SELECT @DFCLNT

Any idea what I am doing wrong?

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered zoeOhara-0533 commented

Try adjusting the last SELECT of the procedure:

 SELECT @out = s FROM t1 WHERE char_count = @some_length

It is probably possible to implement the function differently.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RusselLoski-0634 avatar image
0 Votes"
RusselLoski-0634 answered
  create PROCEDURE  usp_randchar
   @some_length int = 0,
   @out varchar(MAX) output
  as
        
  WITH random_char(c) AS (
     SELECT char(convert(int, rand(abs(convert(int, convert(varbinary, newid())))) * 60.0) + 32)
  ),
  t1 (char_count, s) AS (
     SELECT 0, convert(nvarchar(MAX), (SELECT c FROM random_char))
     UNION ALL
     SELECT char_count + 1, s + (SELECT c FROM random_char) AS s 
     FROM t1 WHERE char_count < 100
  )
  SELECT top 1 @out = s FROM t1 WHERE char_count = @some_length
  return

You need to assign the variable a value.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.