How to pass multiple values as 1 variable to function sql 2008 r2

Transact-SQL
1

Hello,
I am newbie to sql and i want your help.

I have a table valued function on my database with name CSPHPositionForDate_fn. The function needs 3 parameters..one for customer i search for , and 2 parameters for the date that i want to find data of this customer. The function working perfectly if i run the following code:
SELECT
ISNULL (SUM(PHD.Qty * PHD.PriceT),0)
FROM clroot.CSPHPositionForDate_fn('GΑLLΜΑRC', '2017-12-31 00:00:00', '2017-12-31 00:00:00', 0) PHD.

This Code returns 1 line and 1 column with the result of QTY* PriceT. for customer GΑLLΜΑRC ( example 53.4440000 )

My problem / question is that i have about 6.000 customers that i want to run this function. The customers names is like ( GΕΟRGΕΟC, GΕΟRΡΗΙS, LΙRΑGΕΟΙ, LΙRΑΜΟΝG, WΕΒSSΤΕΗ ......) and i want to run it for the same date for example '2017-12-31 00:00:00'.

So...how to run all this 6.000 names in the function and return 1 column with 6.000 lines with the results of all customers ?

FYI : I have the ability to get the customers names with filters on a select query, on one of my db tables.

I appreciate you for your time and your help.

best regards
Hercules Tsetsos

2

Something like this perhaps:

select a.customer_name
      ,isnull(sum(b.qty*b.pricet),0)
  from table_with_customers as a
       cross apply clroot.csphpositionfordate(a.customer_name
                                             ,'2017-12-31 00:00:00'
                                             ,'2017-12-31 00:00:00'
                                             ,0
                                             ) as b
;
1 Like
3

Pass in a table of the customers. In order to do that, you'd first need to create a user-defined table data type (you only have to do this one time). Then declare a local variable of that type. Finally, load that table and pass it into the function. From within the function, you can join to that table to get cust names just like you would join to any other table.

4

I use xml as parameter then parse in the stored procedure.


alter proc [dbo].mystoredprocedure
(
	@currentuser varchar(50),
	@payload as varchar(max)
)
as
begin
	DECLARE  @budget xml,
	select @budget = @payload;

	select distinct y.d.value('departmentcode[1]', 'varchar(50)') as departmentcode,
		   x.v.value('software[1]', 'money') as software,
		   x.v.value('hardware[1]', 'money') as hardware,
		   x.v.value('vendor[1]', 'money') as vendor,
		   x.v.value('description[1]', 'varchar(250)') as description
	  from @budget.nodes('/vrs') x(v)
	  cross apply x.v.nodes('selecteddepartment') as y(d)

....
5

Then, in order to process the names, the function will have to parse the xml back out to a table! Xml is just lots of extra processing for no reason.

6

:slight_smile: not really Scott. No function required to parse xml. just straight from xml to cte, temp table etc.

7

So what's the statement to go from xml to a table then, without additional processing? That is, that doesn't need some type of code to convert from xml to a table, even if it's a standard ms function or call.

8 
create table #customers(customer_id int not null, customer_name varchar(150))
create table #customerorders(customer_id int not null, product_id int, 
order_date datetime,qty int)
create table #products(product_id int not null, product_name varchar(150), 
pricet money)

insert into #customers
select 1 , 'Scott Fletcher' union
select 2, 'Beets Med' union
select 3, 'tsetsos' union
select 4, 'GALLMARC'


insert into #products
select 1, 'Chocolate', 22.5 union
select 2 , 'Avocado Smoothie', 2.5 union
select 3, 'Chicken tikka masala', 7.00 union
select 4, 'Toad-in-the-Hole' , 13.00

insert into #customerorders(customer_id, product_id, order_date, qty)
select 1, 3, getdate(),1 union
select 1, 1, getdate() - 3,2 union
select 1, 4, getdate() -4,1 union

select 2, 1, getdate(),1 union

select 4, 3, '2017-12-31',1  union
select 4, 2, '2017-12-29',1

declare @parm xml = 
'<payload>
	<customer_name>GALLMARC</customer_name>
	<from_date>2017-12-31 00:00:00</from_date>
	<to_date>2017-12-31 00:00:00</to_date>
</payload>'

;with cte
as
(
	select x.v.value('customer_name[1]', 'nvarchar(150)') as customer_name,
		   x.v.value('from_date[1]', 'datetime') as from_date,
		   x.v.value('to_date[1]', 'datetime') as to_date
	from @parm.nodes('/payload') x(v)
)
select c.customer_name
      ,isnull(sum(co.qty*p.pricet),0) as expense
  from #customers c 
  join #customerorders co on c.customer_id = co.customer_id
  join #products p on p.product_id = co.product_id
  where exists(select 1 
                 from cte 
				where cte.customer_name = c.customer_name
				  and co.order_date between cte.from_date and cte.to_date
				)
   group by c.customer_name

drop table #customers
drop table #customerorders
drop table #products
9

This code:

select x.v.value('customer_name[1]', 'nvarchar(150)') as customer_name,
x.v.value('from_date[1]', 'datetime') as from_date,
x.v.value('to_date[1]', 'datetime') as to_date
from @parm.nodes('/payload') x(v)

is parsing out the data again. How is it not?

10

the example I provided was in response to the statement above. no function parsing in this example.

11

I'm sorry, a function of some type will have to parse the xml back into a table.

It's just huge added overhead to:
convert non-xml data to xml,
then pass the xml data to a function,
which has to turn it back into non-xml data to use it!

Lol, that's so wasteful it sounds like something the govt would do!

12

:grin: hey we need revenue! so again, no function needed to parse the xml. xquery only, if you want to call xquery there you go :slight_smile:

sleep on it. since maybe you are way past yoru bedtime :wink:

13

Thanks a lot Bitsmed ,

your code solve my problem with the easy way!!!
I think its time to read deeper for the cross aply usage.
Τhank you for sharing your knowledge with me
Hercules

14

Don't need to sleep on it. You're using step-by-step programming logic instead of set-based database logic. That's an incorrect approach because you don't understand set-based processing.