How can we call Function into Select qry ?

Hi ,
I need to call Function in Select Qry and some of the field of that Qry is
Input to
Function
like select Col1,col2, (select dbo.fn(Col1))Col3 from X
Any help Please ?
KrisKris wrote:
> Hi ,
> I need to call Function in Select Qry and some of the field of that
> Qry is Input to
> Function
> like select Col1,col2, (select dbo.fn(Col1))Col3 from X
> Any help Please ?
> Kris
Select
Col1, Col2, dbo.fn(Col1), Col3
From
X
Is that what you mean? If not, maybe what you're talking about is the
APPLY operator in SQL 2005 which allows you to add columns to a result
set based on the results of a function:
Select Col1, Col2
CROSS APPLY dbo.MyFunc(Col1)
David Gugick - SQL Server MVP
Quest Software|||David Thanks for your reply.
What I understand is, We can not use function into Select Qry, as function
suppose to return value for each records. I have to use Cursor.
Declare c1 -->select IdNum,(COl1+Col2)Col3 from X
read Value into @.Variable
select dbo.fn(@.Variable)
When I used Function in Select Qry. It is not returning value for all record
s,
select col1,col2, (select dbo.fn(col1+col2)) Col3 from X return Values
something like
Col1, Col2 ,Col3
A B AB
X Y AB
Is there any other way i can call fuction in select Qry becoz cursor is slow
.
Thx-Kris
"David Gugick" wrote:

> Kris wrote:
> Select
> Col1, Col2, dbo.fn(Col1), Col3
> From
> X
> Is that what you mean? If not, maybe what you're talking about is the
> APPLY operator in SQL 2005 which allows you to add columns to a result
> set based on the results of a function:
> Select Col1, Col2
> CROSS APPLY dbo.MyFunc(Col1)
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>|||Kris (Kris@.discussions.microsoft.com) writes:
> David Thanks for your reply.
> What I understand is, We can not use function into Select Qry, as function
> suppose to return value for each records. I have to use Cursor.
> Declare c1 -->select IdNum,(COl1+Col2)Col3 from X
> read Value into @.Variable
> select dbo.fn(@.Variable)
> When I used Function in Select Qry. It is not returning value for all
> records,
> select col1,col2, (select dbo.fn(col1+col2)) Col3 from X return Values
> something like
> Col1, Col2 ,Col3
> A B AB
> X Y AB
> Is there any other way i can call fuction in select Qry becoz cursor is
> slow.
It's not fully clear what sort of function you have, but it seems like
you have a scalar function. In such case you can do:
SELECT col1, col2, dbo.fn(col1 + col2) AS Col3 FROM X
If you don't get back all rows as expected, you may have some other
problem with your actual query.
If you have a table-valued function, it depends on which version of
SQL whether you can do this in query or not.
It is difficult to assist when you don't give accurate information of
what you are doing. Could you include the actual code you are using?
Also, please specify which version of SQL Server you are using.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How can i use a for loop in a query ?

hi all i have the following stored procedure.

declare @.AreaID int

select @.AreaID = 1

select DATEPART(hh, dbo.JobEvent.JobEventDateTime) AS hourbracket, count(ID) as NUMCOUNT

from
table1 INNER JOIN
table2 ON table1.JobId = table2.JobEvent_JobId
where

(table1.Job_AreaId = @.AREAID)

the there are about 300 AREAID's so my question is do i do a "LOOP" or is that no efficient and chews up resources on the server ?
what would be a nice clean way to do this ?

thanks
robby

Are you asking how to iterate over the results returned from your stored procedure?

depending on how you retrieve you results either from a data reader or you used a data adapter and filled it to a dataset

//if filled to a dataset you can do thisif (MyDataSet.Tables.Count != 0){foreach (DataRow rowin MyDataSet.Tables[0].Rows) {//now you can access each row returned }}//if using a data readerwhile (DataReader.Read()){//hear you can access each row }

|||

Hi,

if i lets say hardcode the areaid to lets say 1 i get a set of results. i want to next get the results for areaid 2 so as if i want to runt the stored procedure again. But can i lets say have some sort of loop which will pass the parameter (areaid ) into the stored procedure and get the results in c# ?

that would be i think a bit too long winded ... is that right ?

|||

Sorry, but I do not understand what you want.

|||

What I understood from the problem is that you want to execute the same query for around 300 AREAID.
If I am correct following query will solve your problem:

SELECT table1.Job_AreaId, DATEPART(hh, dbo.JobEvent.JobEventDateTime) AS hourbracket, count(ID) as NUMCOUNT
from table1 INNER JOIN table2 ON table1.JobId = table2.JobEvent_JobId
where table1.Job_AreaId in
(
SELECT the list of distinct AreaId for ex: select AreaId from MasterTable
)
Group by table1.Job_AreaId

Hope this helps you!!!

|||

Hi ,

Thanks for the response . It sure did help much appreciated

robby