T-SQL Foreach Loop

Question

Scenario

• I have a stored procedure written in T-Sql using SQL Server 2005.
• "SEL_ValuesByAssetName"
• It accepts a unique string "AssetName".
• It returns a table of values.

Question

• Instead of calling the stored procedure multiple times and having to make a database call everytime I do this, I want to create another stored procedure that accepts a list of all the "AssetNames", and calls the stored procedure "SEL_ValueByAssetName" for each assetname in the list, and then returns the ENTIRE TABLE OF VALUES.

Pseudo Code

foreach(value in @AllAssetsList)
{
@AssetName = value
SEL_ValueByAssetName(@AssetName)
UPDATE #TempTable
}

How would I go about doing this?

Answer 1

In MSSQL 2000 I would make @allAssetsList a Varchar comma separated values list. (and keep in mind that maximum length is 8000)

I would create a temporary table in the memory, parse this string and insert into that table, then do a simple query with the condition where assetName in (select assetName from #tempTable)

I wrote about MSSQL 2000 because I am not sure whether MSSQL 2005 has some new data type like an array that can be passed as a literal to the SP.

Answer 2

It will look quite crippled with using Stored Procedures. But can you use Table-Valued Functions instead?

In case of Table-Valued functions it would look something like:

SELECT al.Value AS AssetName, av.* FROM @AllAssetsList AS al
    CROSS APPLY SEL_ValuesByAssetName(al.Value) AS av

Sample implementation:

First of all, we need to create a Table-Valued Parameter type:

CREATE TYPE [dbo].[tvpStringTable] AS TABLE(Value varchar(max) NOT NULL)

Then, we need a function to get a value of a specific asset:

CREATE FUNCTION [dbo].[tvfGetAssetValue] 
(   
    @assetName varchar(max)
)
RETURNS TABLE 
AS
RETURN 
(
    -- Add the SELECT statement with parameter references here
    SELECT 0 AS AssetValue
    UNION
    SELECT 5 AS AssetValue
    UNION
    SELECT 7 AS AssetValue
)

Next, a function to return a list AssetName, AssetValue for assets list:

CREATE FUNCTION [dbo].[tvfGetAllAssets] 
(   
    @assetsList tvpStringTable READONLY
)
RETURNS TABLE 
AS
RETURN 
(
    -- Add the SELECT statement with parameter references here
    SELECT al.Value AS AssetName, av.AssetValue FROM @assetsList al
        CROSS APPLY tvfGetAssetValue(al.Value) AS av
)

Finally, we can test it:

DECLARE @names tvpStringTable
INSERT INTO @names VALUES ('name1'), ('name2'), ('name3')

SELECT * FROM [Test].[dbo].[tvfGetAllAssets] (@names)