Array Processing - CLR or TSQL?

Question

Post reply

Array Processing - CLR or TSQL?

David-155102

SSCarpal Tunnel

Points: 4361
More actions
May 27, 2008 at 9:10 am

#191065

I was considering using CLR to write a table-valued function that can accept an array argument and return the elements as a table. I cannot work out a way of achieving what I need using TSQL but maybe you do?!
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

The big question in my mind would be - what is it you see using this for? do you have a specific example in mind?

Understanding the background could be really helpful.

For what it's worth - Jeff Moden has an article on emulating multi-dimension arrays being passed in. It posted last week as I recall. Might be worth starting there.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Thanks for the "plug", Matt! 🙂

The article Matt speaks of is at the following URL...

http://www.sqlservercentral.com/articles/T-SQL/63003/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David-155102 SSCarpal Tunnel Points: 4361 More actions · Answer 3

Jeff/Matt,

Thanks for your help, I've decided to go down the Tally table route as it suits what I need with very little effort.

BTW Jeff, good article on Tally tables!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Thanks for the feedback, David!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BuilderBits SSCertifiable Points: 6071 More actions · Answer 5

I personal like xml for these situations.

And here is my why of implementing this

Options---> //

//

ProuductID --> //= 7

ALTER FUNCTION [dbo].[SplitList]

(

@list AS XML

)

RETURNS TABLE

AS

RETURN

(

SELECT tempTable.item.value('.', 'VARCHAR(MAX)') AS ProductID

FROM @list.nodes('list/item') tempTable(item)

);

ALTER PROC [dbo].[uspUtbProduct_Options_Update_Ext]

@List XML,

@ProductID INT,

@UpdateFalse0InsertTrue1 BIT

AS

SET NOCOUNT ON;

DECLARE @ProductOptionIDs INT, @Cnt INT, @ProductOptionID INT;

DECLARE @TableVar table

(ID int identity(1,1) PRIMARY KEY,

ProductID INT NOT NULL

)

INSERT INTO @TableVar(ProductID)

SELECT ProductID FROM [SplitList](@List);

SELECT @Cnt = LEN(ProductID) FROM [SplitList](@List)

WHILE(@Cnt > 0)

BEGIN

SELECT @ProductOptionID = ProductID FROM @TableVar WHERE ID = (@Cnt);

IF(@UpdateFalse0InsertTrue1 = 0)

BEGIN

UPDATE Production.utbProductUtbProductOption

SET ProductOptionID = @ProductOptionID

WHERE Production.utbProductUtbProductOption.ProductID = @ProductID

END

IF(@UpdateFalse0InsertTrue1 = 1)

BEGIN

INSERT INTO [Production].[utbProductUtbProductOption]

([ProductID]

,[ProductOptionID])

VALUES

(@ProductID

,@ProductOptionID)

END

SET @Cnt = @Cnt-1

END

SET NOCOUNT OFF;

protected void btnAddOpts_Click(object sender, EventArgs e)

{

HiddenField hdn = null;

CheckBox ckBx = null;

ArrayList prodIDs = null;

ProductsDataContext pc = null;

try

{

prodIDs = new ArrayList();

for (short i = 0; i < this.lvProdOpts.Items.Count; i++)

{

ckBx = this.lvProdOpts.Items.FindControl("ckBx") as CheckBox;

if (ckBx.Checked)

{

hdn = this.lvProdOpts.Items.FindControl("hdn") as HiddenField;

prodIDs.Add(int.Parse(hdn.Value));

}

if (prodIDs.Count > 0)

{

pc = new ProductsDataContext();

int res = pc.uspUtbProduct_Options_Update_Ext

(GetXml(prodIDs), int.Parse(this.ViewState["ProductID"].ToString()), true);

this.LoadListViewProductOptions();

}

//

} //7

catch (Exception)

{ }

hdn = null;

ckBx = null;

prodIDs = null;

pc = null;

}

public static XElement GetXml(ArrayList list)

{

XElement x = new XElement("list");

foreach (int prodID in list)

{

x.Add(new XElement("item", prodID));

}

return x;

}

Dam again!