How to pass column value as a function parameter ? SQL Server 2005

Question

I'm new here so go easy on me. How do I pass a column value as a function parameter?

I have this query to select months and years:

 SELECT 
     DATEPART(MONTH, a.RequestDate), 
     DATEPART(YEAR, a.RequestDate) 
 FROM 
     TABLE A

Sample data:

Months  Years 
------  -----
  5     2013
  3     2013
  1     2013
  9     2013

The values are not in order and could be duplicates.

I want to pass these value one by one to a function parameter automatically:

 SELECT WeekNo 
 FROM   _GLOBALDB.dbo.fn_WeekNoListInMonth(a.Months, a.Years)

The result would show what weeks would be in that particular month. For example:

 SELECT MIN(WeekNo) 
 FROM   fn_WeekNoListInMonth(5, 2013)

Expected result:

 WeekNO
 ------
   18
   19
   20
   21

I've tried this:

 SELECT MIN(WeekNo) 
 FROM fn_WeekNoListInMonth(A.Months, A.Years)

But it shows an error:

The multi-part identifier "A.Months" could not be bound.
The multi-part identifier "t.Months" could not be bound.

So, you want to get all the output of fn_WeekNoListInMonth() for each row in A? So, if there were 10 rows in A, you'd get between 40 and 50 rows back? Or do you need to get the lowest value from the output of the function, for each row in 'A'? — Ann L.
– Ann L., Commented Dec 28, 2018 at 2:44
@Ann L. All the output . if i want to get the lowest or the highest , i'll just add 'Min' for the lowest and 'max' for the highest , am i right ? — jonathan
– jonathan, Commented Dec 28, 2018 at 2:52
Pity you're on SQL 2005 -- this became a LOT easier with SQL 2008. I'm trying to remember what we used to do before that. — Ann L.
– Ann L., Commented Dec 28, 2018 at 2:55
(Why was MIN in the question, if you weren't trying to get the top or bottom value?) — Ann L.
– Ann L., Commented Dec 28, 2018 at 2:56
I get the impression that the result of this isn't going to be the final result. What is the information you're hoping to wind up with? Perhaps we can avoid doing this (as I remember it) unreasonably difficult thing. — Ann L.
– Ann L., Commented Dec 28, 2018 at 2:59

Gordon Linoff · Accepted Answer · 2018-12-28 03:06:56Z

3

You should be able to use cross apply in SQL Server 2005:

SELECT . . .
FROM TABLE A CROSS APPLY
     dbo.fn_WeekNoListInMonth(month(a.RequestDate), year(a.RequestDate))

answered Dec 28, 2018 at 3:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ann L. Over a year ago

Yep, you're right. I remembered CROSS APPLY as coming in 2008 but I think that's because the employer I was working for skipped from SQL Server 2000 to SQL Server 2008.

jonathan Over a year ago

the output is not what i want , it output random numbers and it output 2 column where as the fn_weekListInMonth function only output 1 column

Gordon Linoff Over a year ago

@jonathan . . . This addresses what your question seems to be: "I want to pass these value one by one to a function parameter automatically:". If the rest of the query doesn't do what you want, then ask another question.

Rachel_Wang · Accepted Answer · 2018-12-31 06:04:38Z

0

;with cte as ( 
SELECT DATEPART(MONTH, RequestDate) as Months, DATEPART(YEAR, RequestDate) as Years
 FROM TABLE 
 )
SELECT WeekNO  FROM cte A 
CROSS APPLY dbo.fn_WeekNoListInMonth(month(A.Months, A.Years)

answered Dec 31, 2018 at 6:04

Rachel_Wang

573 bronze badges

1 Comment

Roy Scheffers Over a year ago

While this might answer the authors' question, it lacks some explaining words and/or links to documentation. Raw code snippets are not very helpful without some phrases around them. You may also find how to write a good answer very helpful. Please edit your answer.

Collectives™ on Stack Overflow

How to pass column value as a function parameter ? SQL Server 2005

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related