[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

use function output as procedure input parameters

my udf outputs like this, for the given @string:
1,ABC,50
2,DEF,2.52
3,GHI,20
4,JKL,20

i just need to use the comma and separate the string into three separate values,
which will be written to a table variable, and then used in a data retrieval.

basically, user will input a @string.  i get that, and parse it out into there separate values, write to a table variable within the proc, and produce data based on the inserted result.  user may return one record via the @string, or multiple.

seriously pressing.

please advise

0
dbaSQL
Asked:
dbaSQL
  • 27
  • 6
  • 5
  • +1
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
please paste your function here,
0
 
chapmandewCommented:
will there always be only 3 variables in the string?  will they always be in the same order?
0
 
dbaSQLAuthor Commented:
sorry.  should have done that.  see within, aneesh

SELECT * FROM dbo.udf_parsestring ('1,ABC,50|2,DEF,2.52|3,GHI,20|4,JKLM,20','|')


that would give back this:
1,ABC,50
2,DEF,2.52
3,GHI,20
4,JKLM,20


i just need to dump that into a table variable, as three distinct values per record, and then use within a subsequent retrieval
ALTER FUNCTION [dbo].[udf_ParseString]
(
@String 	VARCHAR(8000),
@Delimiter	CHAR(1)
)
RETURNS @tbl TABLE (s VARCHAR(1000))
AS
/*
Takes a single string of multiple values, separated by a common delimiter, and returns a table with a record for each value.
Used in stored procs to allow the passing of multiple values in a single parameter.
*/
BEGIN
DECLARE @i INT ,
	@j INT
	SELECT 	@i = 1
	WHILE @i <= LEN(@String)
	BEGIN
		SELECT	@j = CHARINDEX(@Delimiter, @String, @i)
		IF @j = 0
		BEGIN
			SELECT	@j = LEN(@String) + 1
		END
		INSERT	@tbl SELECT SUBSTRING(@String, @i, @j - @i)
		SELECT	@i = @j + LEN(@Delimiter)
	END
	RETURN
END

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
dbaSQLAuthor Commented:
yes, they will be, chap.  always three, always same order, and always delimited by a comma
numeric,alpha,numeric
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
ALTER FUNCTION [dbo].[udf_ParseString]
(
@String       VARCHAR(8000),
@Delimiter      CHAR(1)
)
RETURNS @tbl TABLE (a VARCHAR(100),b VARCHAR(100),c VARCHAR(100))
AS
/*
Takes a single string of multiple values, separated by a common delimiter, and returns a table with a record for each value.
Used in stored procs to allow the passing of multiple values in a single parameter.
*/
BEGIN
DECLARE @i INT ,
      @j INT , @a VARCHAR(100),@b VARCHAR(100),@c VARCHAR(100)
      SELECT       @i = 1
      WHILE @i <= LEN(@String)
      BEGIN
            SELECT      @j = CHARINDEX(@Delimiter, @String, @i)
            IF @j = 0
            BEGIN
                  SELECT      @j = LEN(@String) + 1
            END
                  IF @a IS null
               SET @a =   SUBSTRING(@String, @i, @j - @i)
            ELSE IF @b IS NULL
               SET @b =   SUBSTRING(@String, @i, @j - @i)
            ELSE IF @c is null
               SET @c =  SUBSTRING(@String, @i, @j - @i)
            ELSE
                  BEGIN
                        INSERT INTO @tbl select @a,@b,@c
                        SELECT @a = NULL , @b = null, @c = null
                  END
            SELECT      @i = @j + LEN(@Delimiter)
      END
      RETURN
END



GO
SELECT * FROM dbo.udf_parsestring ('1,ABC,50|2,DEF,2.52|3,GHI,20|4,JKLM,20','|')


0
 
dbaSQLAuthor Commented:
that gives me:  
   a                        b                 c
1,ABC,50      2,DEF,2.52      3,GHI,20

I need this:  
a      b          c
1     ABC      50
2     DEF       2.52
3    GHI          20


i would then insert a, b, c into my table variable

'1' = a
'ABC' = b
'50'  = c
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
ALTER FUNCTION [dbo].[udf_ParseString]
(
@String       VARCHAR(8000),
@Delimiter      CHAR(1)
)
RETURNS @tbl TABLE (a VARCHAR(100),b VARCHAR(100),c VARCHAR(100))
AS
/*
Takes a single string of multiple values, separated by a common delimiter, and returns a table with a record for each value.
Used in stored procs to allow the passing of multiple values in a single parameter.
*/
BEGIN
DECLARE @i INT ,
      @j INT
      SELECT       @i = 1
      WHILE @i <= LEN(@String)
      BEGIN
            SELECT      @j = CHARINDEX(@Delimiter, @String, @i)
            IF @j = 0
            BEGIN
                  SELECT      @j = LEN(@String) + 1
            END
            INSERT      @tbl SELECT * from dbo.udf_parsestring1 ( SUBSTRING(@String, @i, @j - @i), ',' )
            SELECT      @i = @j + LEN(@Delimiter)
      END
      RETURN
END


GO


ALTER  FUNCTION [dbo].[udf_ParseString1]
(
@String       VARCHAR(8000),
@Delimiter      CHAR(1)
)
RETURNS @tbl TABLE (a VARCHAR(100),b VARCHAR(100),c VARCHAR(100))
AS
/*
Takes a single string of multiple values, separated by a common delimiter, and returns a table with a record for each value.
Used in stored procs to allow the passing of multiple values in a single parameter.
*/
BEGIN
DECLARE @i INT ,
      @j INT , @a VARCHAR(100),@b VARCHAR(100),@c VARCHAR(100)
      SELECT       @i = 1
      WHILE @i <= LEN(@String)
      BEGIN
            SELECT      @j = CHARINDEX(@Delimiter, @String, @i)
            IF @j = 0
            BEGIN
                  SELECT      @j = LEN(@String) + 1
            END
                  IF @a IS null
               SET @a =   SUBSTRING(@String, @i, @j - @i)
            ELSE IF @b IS NULL
               SET @b =   SUBSTRING(@String, @i, @j - @i)
            ELSE IF @c is null
               SET @c =  SUBSTRING(@String, @i, @j - @i)
            SELECT      @i = @j + LEN(@Delimiter)
      END
            INSERT INTO @tbl select @a,@b,@c
            
      RETURN
END



GO
SELECT * FROM dbo.udf_parsestring ('1,ABC,50|2,DEF,2.52|3,GHI,20|4,JKLM,20','|')

0
 
dbaSQLAuthor Commented:
maybe not following, aneesh.  it gives me back the same result-set:

  a                       b                  c
1,ABC,50      2,DEF,2.52      3,GHI,20
0
 
dbaSQLAuthor Commented:
oh.  i got it.

i need two functions to do this?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
with two functionsit look simple
0
 
dbaSQLAuthor Commented:
ok.  but, to proceduralize it ?

create proc (
 @string varchar(500)
)
as
set nocount on

begin try
   begin tran

     declare @table table (a int, b char(10), c decimal(18,8)
     insert @table
     select * from udf_parsestring @string

    then do the select based on the values in @table

.............
.....


how do i pass this:  SELECT * FROM dbo.udf_parsestring ('1,ABC,50|2,DEF,2.52|3,GHI,20|4,JKLM,20','|')


into my proc?

where this:   ('1,ABC,50|2,DEF,2.52|3,GHI,20|4,JKLM,20','|')
= @string

do you know what i mean?
0
 
dbaSQLAuthor Commented:
yeah.  i just can't get that back into the proc as input parameters.
ugh
0
 
dbaSQLAuthor Commented:

declare @string varchar(200)
set @string =('1,ABC,50|2,DEF,2.52|3,GHI,20|4,JKLM,20','|')
SELECT * FROM dbo.udf_parsestring (@string)

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.


it doesn't like that ',' in my @string
0
 
dbaSQLAuthor Commented:
aneesh?  does it need to be dynamic?  your suggestion is great.  but how do i pass that @string into my proc?
0
 
dbaSQLAuthor Commented:
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>ok.  but, to proceduralize it ?
I dont understand wht exactly you look for  
0
 
dbaSQLAuthor Commented:
i want joe-user to be able to do this:

exec proc @string = ( '1,ABC,50|2,DEF,2.52|3,GHI,20|4,JKLM,20','|')

within the proc, will write the output values into my table variable, and then perform a select based on the given values.

i get @string, parse it into the three values, load to my table variable, and return based on this:


select distinct FIELD
from tableA JOIN tableB ON a.blah = b.blah
 JOIN @tablevariable c ON a.blah = c.blah
0
 
dbaSQLAuthor Commented:
exec proc @string = .............
the @string is passed to the udf_parsestring function
the output goes into my table variable, i then return some data based on a join between a couple other tables and that @tablevariable, and it's all done
0
 
dbaSQLAuthor Commented:
every damned thing i try fails with this:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ','.


i don't know if i need to be focusing on my new proc, or another revision to my function(s)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
exec procName @string =  '1,ABC,50|2,DEF,2.52|3,GHI,20|4,JKLM,20', '|'
0
 
dbaSQLAuthor Commented:
not following.  does my function need changes?
0
 
dbaSQLAuthor Commented:
create procname (
 @string varchar(500)
)
as
set nocount on

begin try
   begin tran

     declare @table table (a int, b char(10), c decimal(18,8)
     insert @table
     select * from udf_parsestring @string

    then do the select based on the values in @table

.............
.....






how does that get me to this:   exec procName @string =  '1,ABC,50|2,DEF,2.52|3,GHI,20|4,JKLM,20', '|'
0
 
dbaSQLAuthor Commented:
i'm sorry, aneesh.  this is truly upsetting me.  surely you're saying it.  i just am not following
0
 
chapmandewCommented:
Can you just call it twice, then gather your records together after?

select
*
from
(
select ranking = row_number() over (order by left(s, 1))), dbo.udf_ParseString(s, ',')
from dbo.udf_ParseString('1,ABC,50|2,DEF,2.52|3,GHI,20|4,JKLM,20','|')
) a
0
 
dbaSQLAuthor Commented:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.

i am so pulling my hair out at this point.  


function, procedure, i don't care how many times I call it.  user passes this, i need to parse the string and use as input in procname

exec proc @string = ( '1,ABC,50|2,DEF,2.52|3,GHI,20|4,JKLM,20','|')
0
 
chapmandewCommented:
whoops..try it now

select
*
from
(
select dbo.udf_ParseString(s, ','), ranking = row_number()  over(order by left(s,1))
from dbo.udf_ParseString('1,ABC,50|2,DEF,2.52|3,GHI,20|4,JKLM,20','|')
) a
0
 
dbaSQLAuthor Commented:
Msg 4121, Level 16, State 1, Line 2
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.udf_ParseString", or the name is ambiguous.
0
 
chapmandewCommented:
are you running it in the correct db?  try this:

select
*
from
(
select udf_ParseString(s, ','), ranking = row_number()  over(order by left(s,1))
from udf_ParseString('1,ABC,50|2,DEF,2.52|3,GHI,20|4,JKLM,20','|')
) a
0
 
dbaSQLAuthor Commented:
yes i am, chap.  no difference
0
 
dbaSQLAuthor Commented:
i got it.  very likely not the most ideal.  but i got it.  unfreeking believable
0
 
dbaSQLAuthor Commented:
posting in a sec
0
 
chapmandewCommented:
;)
0
 
dbaSQLAuthor Commented:
well... i thought i had it.  i have just been told that it returns a bit more results than expected.


IF OBJECT_ID('procname','p')>0
DROP PROC dbo.procname
GO
CREATE PROC dbo.procname (
 @string VARCHAR(2000),
 @delimiter char(1)
)
as
set nocount on
/*
exec dbo.procname
@string = '1,IACI,50|2,USD,2.52|3,HSNI,20|4,IILG,20|5,TKTM,20|6,TREE,3',@delimiter='|'
*/
DECLARE @tableC TABLE (a int,b char(10),c decimal(18,8))
DECLARE @i INT,
            @j INT

      SELECT @i = 1
      WHILE @i <= LEN(@String)
      BEGIN
            SELECT @j = CHARINDEX(@Delimiter, @String, @i)
            IF @j = 0
            BEGIN
                  SELECT @j = LEN(@String) + 1
            END
            INSERT @tableC SELECT * from dbo.udf_parsestring1 ( SUBSTRING(@String, @i, @j - @i), ',' )
            SELECT  @i = @j + LEN(@Delimiter)
      END

         SELECT DISTINCT ug.fieldname
        FROM dbo.tableA ug JOIN dbo.tableB s ON s.id = ug.id
        JOIN @tableC d ON ug.no = d.no
             AND ug.id = s.id
            AND ug.c = d.c


SET NOCOUNT OFF
GO
0
 
dbaSQLAuthor Commented:
unbelievable.  i had no idea it was so difficult to pass a parsed string into a proc as input
0
 
hegotITCommented:
Here you go:

IF OBJECT_ID('procname','p')>0
DROP PROC dbo.procname
GO
CREATE PROC dbo.procname (
 @String VARCHAR(2000),
 @Delimiter char(1)
)
AS
/*
exec dbo.procname
@string = '1,IACI,50|2,USD,2.52|3,HSNI,20|4,IILG,20|5,TKTM,20|6,TREE,3',@delimiter='|'
*/

SET NOCOUNT ON;

SET QUOTED_IDENTIFIER OFF;

--DECLARE @String nvarchar(2000);
--DECLARE @Delimiter nchar(1);

DECLARE @tableS TABLE (s nvarchar(2000));
DECLARE @tableC TABLE (a int,b char(10),c decimal(18,8));
DECLARE @i INT, @j INT;
DECLARE @s nvarchar(50);

--SELECT @String = '1,ABC,50|2,DEF,2.52|3,GHI,20|4,JKLM,20|5,ADD1,13.8'
--SELECT @String = '1,IACI,50|2,USD,2.52|3,HSNI,20|4,IILG,20|5,TKTM,20|6,TREE,3';
--SELECT @Delimiter = '|'

INSERT INTO @tableS SELECT * FROM dbo.udf_parsestring( @String, @Delimiter );

--SELECT * FROM @tableS;
SELECT @s = (SELECT TOP 1 * FROM @tableS);
--SELECT @s;

WHILE EXISTS(SELECT * FROM @tableS)
BEGIN
      INSERT INTO @tableC SELECT * FROM dbo.udf_parsestring1(@s, ',');
      DELETE TOP (1) FROM @tableS;
      SELECT @s = (SELECT TOP 1 * FROM @tableS);
END

SELECT * FROM @tableC;

SET NOCOUNT OFF;

SET QUOTED_IDENTIFIER ON
GO
0
 
dbaSQLAuthor Commented:
Column name or number of supplied values does not match table definition.

It's the insert into @tableS
0
 
hegotITCommented:
The output from your udf_ParseString or udf_ParseString1 functions might be different than mine... here are the versions I'm using:

--udf_ParseString:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[udf_ParseString]
(
@String       VARCHAR(8000),
@Delimiter      CHAR(1)
)
RETURNS @tbl TABLE (s VARCHAR(1000))
AS
/*
Takes a single string of multiple values, separated by a common delimiter, and returns a table with a record for each value.
Used in stored procs to allow the passing of multiple values in a single parameter.
*/
BEGIN
DECLARE @i INT ,
      @j INT
      SELECT       @i = 1
      WHILE @i <= LEN(@String)
      BEGIN
            SELECT      @j = CHARINDEX(@Delimiter, @String, @i)
            IF @j = 0
            BEGIN
                  SELECT      @j = LEN(@String) + 1
            END
            INSERT      @tbl SELECT SUBSTRING(@String, @i, @j - @i)
            SELECT      @i = @j + LEN(@Delimiter)
      END
      RETURN
END

-----------------------------------------------------------------------------------------------
--udf_ParseString1:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[udf_ParseString1]
(
@String       VARCHAR(8000),
@Delimiter      CHAR(1)
)
RETURNS @tbl TABLE (a VARCHAR(100),b VARCHAR(100),c VARCHAR(100))
AS
/*
Takes a single string of multiple values, separated by a common delimiter, and returns a table with a record for each value.
Used in stored procs to allow the passing of multiple values in a single parameter.
*/
BEGIN
DECLARE @i INT ,
      @j INT , @a VARCHAR(100),@b VARCHAR(100),@c VARCHAR(100)
      SELECT       @i = 1
      WHILE @i <= LEN(@String)
      BEGIN
            SELECT      @j = CHARINDEX(@Delimiter, @String, @i)
            IF @j = 0
            BEGIN
                  SELECT      @j = LEN(@String) + 1
            END
                  IF @a IS null
               SET @a =   SUBSTRING(@String, @i, @j - @i)
            ELSE IF @b IS NULL
               SET @b =   SUBSTRING(@String, @i, @j - @i)
            ELSE IF @c is null
               SET @c =  SUBSTRING(@String, @i, @j - @i)
            SELECT      @i = @j + LEN(@Delimiter)
      END
            INSERT INTO @tbl select @a,@b,@c
           
      RETURN
END
-------------------------------------------------------------------------------------------------

-- I'm using SQL Server 2005 Standard Edition
-- if I run this:
exec dbo.procname @string = '1,IACI,50|2,USD,2.52|3,HSNI,20|4,IILG,20|5,TKTM,20|6,TREE,3',@delimiter='|'

-- I get this result: (see attached image)




result.jpg
0
 
dbaSQLAuthor Commented:
yes, i get all of that, too.  except for when i'm trying to use the procedure.  still looking at it, but it is the @tableS insert.  still digging
0
 
hegotITCommented:
Okay... paste this into a new query window:

SET NOCOUNT ON;

SET QUOTED_IDENTIFIER OFF;

DECLARE @String nvarchar(2000);
DECLARE @Delimiter nchar(1);

DECLARE @tableS TABLE (s nvarchar(2000));
DECLARE @tableC TABLE (a int,b char(10),c decimal(18,8));
DECLARE @i INT, @j INT;
DECLARE @s nvarchar(50);

--SELECT @String = '1,ABC,50|2,DEF,2.52|3,GHI,20|4,JKLM,20|5,ADD1,13.8'
SELECT @String = '1,IACI,50|2,USD,2.52|3,HSNI,20|4,IILG,20|5,TKTM,20|6,TREE,3';
SELECT @Delimiter = '|'

INSERT INTO @tableS SELECT * FROM dbo.udf_parsestring( @String, @Delimiter );

SELECT * FROM @tableS;

-- you should get the following: (see attached image)


tableS-insert.jpg
0
 
dbaSQLAuthor Commented:
no need.  i got the first one together correctly... AND they've just hit me with yet another crazed string manipulation.

thank you very much, hegoIT
and chap, and aneesh

i more than appreciate all of the input
0
 
dbaSQLAuthor Commented:
definitely a crazy one... thank you each for all of the input
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 27
  • 6
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now