Help streamlining a function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help streamlining a function

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-05-10 : 09:29:18

Hi All

I've written a function that is supposed to accept a nvarchar(max) string of comma-separated values, and return these in a table:


CREATE FUNCTION myfn_createIdTable
	(
	@Ids nvarchar(max),
	@separator char(1)
	)
RETURNS @tbl_Ids TABLE
	(
	indexCol int Identity(1,1),
	IdVal int NOT NULL
	)
AS
	BEGIN
		-- check @Ids string ends with separator character
		IF NOT RIGHT(@Ids, 1) = @separator
			BEGIN
				SET @Ids = @Ids + @separator
			END
		DECLARE @separator_position INT
		DECLARE @array_value nvarchar(10)
		WHILE PATINDEX('%' + @separator + '%' , @Ids) <> 0
			BEGIN -- feed array of integers into tempTable
				SELECT @separator_position =  PATINDEX('%' + @separator + '%' , @Ids)
				SELECT @array_value = LEFT(@Ids, @separator_position - 1)
				INSERT INTO @tbl_Ids (IdVal) VALUES (@array_value)
				SELECT @Ids = STUFF(@Ids, 1, @separator_position, '')
			END
		RETURN
	END
GO

Then, within my stored procedures I am executing the function as follows:


DECLARE @tmp TABLE (ID INT, IDs INT)
INSERT INTO @tmp  (ID, IDs) SELECT indexCol, IdVal FROM myfn_createIdTable('45,33,775,34,24,44,85,37,26,1,267', ',')
SELECT * FROM @tmp

This works okay, but I wondered if it is possible to reduce the code in the stored procedure. Is it possible to not have to declare the schema of the @tmp table variable (e.g. SELECT straight into an 'empty' variable)??

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-10 : 09:37:29

Not very sure what do you mean by "SELECT straight into an 'empty' variable" ?

But you don't required the @tmp table variable. You can treat the "myfn_createIdTable('45,33,775,34,24,44,85,37,26,1,267', ',')" as a table and join it to other table


SELECT *
FROM   myfn_createIdTable('45,33,775,34,24,44,85,37,26,1,267', ',') d
       INNER JOIN some_other_table t    ON d.IdVal = t.col

KH
[spoiler]Time is always against us[/spoiler]

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-10 : 09:38:44

Or, did you mean reduce the code in the function?

If so, you may want to see if the function described in Jeff Moden's post http://www.sqlservercentral.com/articles/Tally+Table/72993/ would be of use to you. His post itself is very long, but scroll down to Figure 21 where he has the final code that you can copy and use.

That function is only for varchar(8000). If you really need a varchar(max) version, he has promised to post one, but it is not too hard to modify the varchar8K version to varchar(max).

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-05-10 : 09:56:36

Hi

Thanks for your replies.

My stored proc's accept the csv string as a parameter. Then, usually I turn this csv string into a table variable using the code which I've now moved into a function (again to reduce repeated code in my proc's).

The table variable containing the ID values is then used a number of times within the stored procedure. Therefore I was hoping for a real quick (reducing code) way to swap the csv string into a table variable that could be reused.

I know that I can do...


SELECT * FROM myfn_createIdTable(@userIDs, @separator) fn INNER JOIN ...

...but if I'm re-using the table variable lots, then the same function is getting called more than once which surely isn't economical...?!?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources