Free Jobs
Reference




Dot Net Forums: ASPNET (77) C#.NET (39) VB.NET (8) AJAX (21) Silver Light (1) WCF (10) WPF (1) WWF (1) .NET 3.5 (3) SQL SERVER (38) Other (3)

Topic: How return string array from one stored procedure to another and split in calling sp

To get the Posted Questions details daily in your mail Click Here

Share this Post on Facebook/Twitter/Orkut Bookmark and Share





User: manishav3388 Post Date: 23 May 2012 12:58

Introduction:
How return string array from one stored procedure to another and split in calling sp



Main Content:



CREATE procedure [Cuser].[Test_Test_sp1]

     (

        @param1        char(15)   = Null,

        @param2        varchar(119)  = Null,

        @param3        varchar(49)  = Null

     )

AS

     BEGIN

    

     declare @var1    char(15)    

     declare @var2    varchar(119)

     declare @var3    varchar(49)

     declare @return_value   varchar(200)

    

     SET @var1 = (SELECT column1 from Table1  br  join Table2 brd on br.id = brd.ID

     WHERE column3 =@param1)

    

     SET @var2 = (SELECT column1 from Table1  br  join Table2 brd on br.id = brd.ID

     WHERE column3 =@param1)

    

     if(@var1 is null)

     BEGIN

     PRINT ''A''

     END

     ELSE

     BEGIN

     PRINT ''B''

     END

    

     if(@var1 is null or @var1 = '''')

     begin

     SET @var1 =  @param1

     end

    

     if(@var2 is null or @var2 = '''')

     begin

     SET @var2 =  @param2 

     end 

    

     set @return_value = @var1 +'',''+@var2

    

     select @return_value


     END

 

---------------

In the calling sp write below code to break the return string in two values

calling sp

declare @return_value varchar(200)

CREATE TABLE #tmpTable

(

    OutputValue VARCHAR(200)

)

INSERT INTO #tmpTable (OutputValue) EXEC Test_Test_sp1  ''param1'', ''param2'',''param3''

SELECT     @return_value = OutputValue FROM #tmpTable

DROP TABLE #tmpTable


declare @delimiter varchar(1)

set @delimiter ='',''

declare @index int

set @index = CHARINDEX(@delimiter , @return_value)

print @index

print LEFT(@return_value, @index-1)

print RIGHT(@return_value,((LEN(@return_value)- (@index))))

 


Conclusion:
this way we can return delimited string from one sp to calling sp and use in another



About Writer:
hello,I am JAVA Developer from Noida.


Writer's Link: http://

Writer's Email:







Comments:
Post Comment





Similar Articles Top ArticlesLatest Articles Other Articles by this Author







www.freejobsreference.com Copyright © 2009-2011 | All Rights Reserved
All content of this website is protected by copyright laws and should not be reproduced in any form or by any means without written permission
(Best viewed in IE 8.0+ or Firefox 3.0+ at resolution of 1024 * 768 or higher)