How to get return from Stored Procedure

• Kanwar A Singh

  Old Hand

  Points: 336

  More actions

  June 4, 2004 at 3:39 pm

  #152174

  I have Created a small sp which returns 16 char long unique values

  Look at this:

  CREATE PROCEDURE __sp_UTL_KeyConstructTemp

           @mTableName VARCHAR(64),

                  @mColumnKey VARCHAR(64),

    @mKey NVARCHAR(16) OUTPUT

  AS

   DECLARE @strSQL VARCHAR(2000)

   CREATE TABLE #tmpKey(rKey varchar(16))

          SET @strSQL =  'SELECT CASE WHEN Max(' + @mTableName + '.' + @mColumnKey + ') IS NULL THEN ''0000000000000001'' ' + 

           ' ELSE RIGHT(''0000000000000000'' + CONVERT(VARCHAR(16),MAX(CONVERT(DECIMAL(16,0),' + @mTableName + '.' + @mColumnKey + '))+1),16)'  +

           ' END FROM ' + @mTableName + ' where ' + @mTableName + '.' + @mColumnKey + ' like ''0000%'''

   INSERT INTO #tmpKey EXECUTE (@strSQL)

   Select @mkey = rKey from #tmpKey

  But I don't want to create Temp table #tmpKey

  but i want to use OUTPUT param for this sp, can any body solve my problem

  thankx

   

   

• noeld

  SSC Guru

  Points: 96590

  More actions

  June 4, 2004 at 6:53 pm

  #509115

  It's late Friday and as always I am in a hurry but you should try this:

   

  SET @strSQL =  'SELECT @mkey = CASE WHEN Max(' + @mTableName + '.' + @mColumnKey + ') IS NULL THEN ''0000000000000001'' ' + 

           ' ELSE RIGHT(''0000000000000000'' + CONVERT(VARCHAR(16),MAX(CONVERT(DECIMAL(16,0),' + @mTableName + '.' + @mColumnKey + '))+1),16)'  +

           ' END FROM ' + @mTableName + ' where ' + @mTableName + '.' + @mColumnKey + ' like ''0000%'''

    exec sp_executesql @strSQL , @mkey varchar(16) OUTPUT', @mkey OUTPUT

   

  HTH

  
  * Noel