using a concatenated string variable in another query

Question

I have a SPROC like this in SQL server which will split a concatenated string ([var1][var2]) and return 2 result set, how do I pass each individual item from the result sets into another @var in my SProc so that I can do this, thanks:

SET @var3 = (select [var1]+[var2]) --Join the result sets values and assign it to another variable
                 from ...where...

Result sets:

e.g

resultset
----
tbl1
----
[var1]

resultset
----
tbl1
----
[var2]

Query that splits the concatenated string into it's parts:

declare @Str as varchar(100) 

set @Str = '[Var1][Var2]' 
while (@Str <> '') begin 
        select LEFT(@Str, CHARINDEX(']', @Str))  as resulttbl 
        set @Str = SUBSTRING(@Str, CHARINDEX(']', @Str) + 1, LEN(@Str))  

end

So, you need to split the variables and then concatenated again and assign them to another variable? — Lamak
– Lamak, Commented Dec 9, 2011 at 17:53

MatBailie · Accepted Answer · 2011-12-09 17:55:48Z

You could use OUTPUT parameters...

CREATE PROCEUDRE yourSP (@str AS VARCHAR(max), @output1 AS VARCHAR(max) OUTPUT, @output2 AS VARCHAR(max) OUTPUT)
AS
BEGIN
  while (@Str <> '') begin 
    set @output1 = LEFT(@Str, CHARINDEX(']', @Str))
    set @Str = SUBSTRING(@Str, CHARINDEX(']', @Str) + 1, LEN(@Str))  
  end
  set @puput2 = @str
END

Then call that SP with bout input and output variables.

DECLARE
  @str     VARCHAR(max),
  @result1 VARCHAR(max),
  @result2 VARCHAR(max)
SELECT
  @str     = '[Var1][Var2]'

EXEC yourSP @str, @result1 OUTPUT, @result2 OUTPUT

SELECT @str, @output1, @output2

Or, you could package it in a table valued function instead of a stored procedure...

SELECT
  @output1 = split.value1,
  @output2 = split.value2
FROM
  dbo.yourFn(@str) AS split

And if you have atable of data to process, this then applows you to use APPLY...

SELECT
  source.str,
  split.value1,
  split.value2
FROM
  source
CROSS APPLY
  dbo.yourFn(source.str) AS split

Collectives™ on Stack Overflow

using a concatenated string variable in another query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related