[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

parsing procedure fields in a ms sql table

I have a procdure that will parse senteneces into words for emample

exec split 'this is a test'',1

adds to a file called words (2 fields word and id)

so words end up with 4 records

this  1
is     1
a      1
test  1

If i run it with different parameters it works fine

Now i have a table and I need to run the procedure on every row that has 2 fields character and numeric

I tried

exex split select name,id from table where id=15 and i get an error

What I really need it is (or something similar)

exex split select name,id from table

to parse all the table (field name) into the words table

0
robrodp
Asked:
robrodp
  • 5
  • 3
2 Solutions
 
Anthony PerkinsCommented:
You need to convert that Stored Procedure to a Table Valued Function and then use CROSS APPLY to retrieve all the values.
0
 
robrodpAuthor Commented:
No idea of how to do that.. any ideas. I will look into it.
0
 
Anthony PerkinsCommented:
Post your Stored Procedure as it stands.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
robrodpAuthor Commented:
I have a table values function that parses fine

select * from dbo.fnParseMe('aqwee,ee,qw,nggrr',',') returns the words.

How do I get then in a table
0
 
robrodpAuthor Commented:
The table valued funcion is:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER FUNCTION [dbo].[fnParseMe] 
	(
	@sStr varchar(4000),
	@sDelim varchar(1)
	)
RETURNS @tbl table
	(
	value varchar(250)
	)
AS 
BEGIN 
	declare @nLen as int	
	declare @nStart as int
	declare @nEndIndex int
	select @nlen = Len(@sStr)
	select @nStart = 1
	while @nStart <= @nlen
	begin
		select @nEndIndex = charindex(@sDelim, @sStr, @nStart)
		if @nEndIndex = 0 
		begin
			select @nEndIndex = @nLen + 1
		end
		insert into @tbl (value)
		values (substring(@sStr, @nStart, @nEndIndex - @nStart))
		
		select @nStart = @nEndIndex + Len(@sDelim)
	end
	return 
END

Open in new window

0
 
gladfellowCommented:
As I understand from your question, you want to run the SP with each row value in the table as parameter. If this understanding is right ...

As far as I know, there is no way of invoking an SP with parameter as a  'SELECT ...' statement that returns many rows. I would have perhaps tried a 2-step method as follows:

STEP 1 - run the following suggested query that will generate as many 'exec ...' statements as the number of rows in the table

select 'exec split ''' + rtrim( name ) + ''', ' + convert( char(3), id ) from table

STEP 2 - run the 'exec statements' generated in STEP 1

Please let us know if this fulfills your need.

- Venkat
0
 
robrodpAuthor Commented:
There are 60,000 records how can I run the 60,000 generated statements ?

I have a parsing table valued function also it is in the code just above, would that help?
0
 
Anthony PerkinsCommented:
It is as simple as:
SELECT  *
FROM    YourTableName t
        CROSS APPLY dbo.fnParseMe(t.YourTableColumn, ',') f

Open in new window

0
 
robrodpAuthor Commented:
Thx...
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now