Hello:
I am trying to define a cursor as follows:
DECLARE EmployeeList
CURSOR FOR dbo.GetRecord(@.EmployeeID,@.CurrentDate)
Can't I use a UDF in the CURSOR FOR ?
Help please.
thank you.
I think it is the other way your UDF can use local Cursors, try the links below for more info; the second link is a UDF expert. I am assuming you know Cursors can be avoided because the query processor is not created to perform such tasks. Hope this helps.
http://www.databasejournal.com/features/mssql/article.php/1442221
http://www.novicksoftware.com/
|||SomeNewTricks2, what does your GetRecord UDF return?
|||What I am trying to do is that, I have an input parameter for my stored procedure, if it is set to 1, I want to get all records else one specific record, I want to build the select statement dynamically, then put it in the CURSOR FOR statement.
Is that possible Terri?
Thank you|||
Also, can I use something like:
CURSOR FOR
SELECT * FROM dbo.GetRecord(@.EmployeeID,@.CurrentDate)
?
thanks a lot.
Hello tmorton: I was able to do so:
CREATE FUNCTION [dbo].[GetEmployeeRecord]
(
@.EmployeeID INT
)
RETURNS @.EmployeeRecord TABLE
(
EmployeeID INT,
UserName VARCHAR(50),
FirstName VARCHAR(50),
LastName VARCHAR(50),
)
AS
BEGIN
DECLARE @.strQuery NVARCHAR(1000)
DECLARE @.parameterList NVARCHAR(1000)
SELECT @.strQuery =
N'
-- Create Global Table (Temporarily)
Create Table ##TempEmployeeData
(
EmployeeID INT,
UserName VARCHAR(50),
FirstName VARCHAR(50),
LastName VARCHAR(50),
)
-- Insert the needed records to the above table
INSERT INTO ##TempEmployeeData SELECT * FROM Employees WHERE (1=1)'
-- If employee id = -1, means get all records else, get specifi record
IF (@.EmployeeID != -1)
SELECT @.strQuery = @.strQuery + N' AND (EmployeeID = @.EmployeeID)'
-- Add parameter list, only one parameter present
SET @.parameterList =N'@.EmployeeID INT'
-- Execute dynamic query
EXECUTE SP_EXECUTESQL @.strQuery, @.parameterList, @.EmployeeID
-- Fill our returned table
INSERT INTO
@.EmployeeRecord
SELECT * FROM ##TempEmployeeData
-- Drop the Temp table
DROP TABLE ##TempEmployeeData
RETURN
END
I get this error:
Server: Msg 2772, Level 16, State 1, Procedure GetEmployeeRecord, Line 72
Cannot access temporary tables from within a function.
Server: Msg 2772, Level 16, State 1, Procedure GetEmployeeRecord, Line 74
Cannot access temporary tables from within a function.
Once I finish this udf, I will use
CURSOR FOR
SELECT * FROM dbo.GetEmployeeRecord.
Can you help please. thanks you.
SomeNewTricks2 wrote:
Also, can I use something like:
CURSOR FOR
SELECT * FROM dbo.GetRecord(@.EmployeeID,@.CurrentDate)
Yes, that would be the correct way to access data from a tabel-valuedUDF. Your function returns a table, so use need to SELECT fromit. You can't just put the name of the UDF into a command byitself and expect SQL to know what to do.
You are trying to use global temp tables (##) which is usually not agood idea for a web application. When user #2 hits the site andtries to run the page you are going to start running into problems.
Maybe I am not really following what you are trying to do exactly, but why aren't you just doing something like this (untested)?
CREATE FUNCTION [dbo].[GetEmployeeRecord]
(
@.EmployeeID INT
)
RETURNS @.EmployeeRecord TABLE
(
EmployeeID INT,
UserName VARCHAR(50),
FirstName VARCHAR(50),
LastName VARCHAR(50),
)
AS
BEGIN
INSERT INTO
@.EmployeeRecord
(
EmployeeID,
UserName,
FirstName,
LastName
)
SELECTEND
EmployeeID,
UserName,
FirstName,
LastName
FROM
Employees
WHERE
@.EmployeeID = -1 OR @.EmployeeID = EmployeeID
RETURN
|||thank you Terri, that solved the problem even without temp tables.
The reason I used ## is that to be able to access it outside the context of the dynamically executed query. I mean when I run a dynamic query, in which I create a temp table, I need to access it outside the context of its execution, that is why i used ##.
One more thing, I am trying to get all records when EmlpoyeeId = -1 and get specific record when there is a valid employee id
my question, why did u write EmployeeID = -1 OR EmployeeID = @.EmployeeID ?
if EmployeeID = -1, then I will go into the table search for EmployeeID = -1, cannot find any record, however, it is returning all records, what is the logic behind it?
thank you
|||
why did u write EmployeeID = -1 OR EmployeeID = @.EmployeeID ?
Actually, I wrote@.EmployeeID= -1 OREmployeeID = @.EmployeeID. That's a huge difference and is the keyto why the statement works. You have 2 conditions in your WHEREclause, with an OR between them, so only one of the conditions mustbe true in order for the WHERE criteria to be met and for a row to bereturned.
Here's a chart representing representing the above explanation:
@.EmployeeID = 5
UserName EmployeeID Condition1 (@.EmployeeID=-1) Condition 2(@.EmplyeeID=EmployeeID) Row Returned?
jones 123 False False No
smith 5 False True Yes
brown 99 False False No
@.EmployeeID = -1
UserName EmployeeID Condition1 (@.EmployeeID=-1) Condition 2(@.EmplyeeID=EmployeeID) Row Returned?
jones 123 True False Yes
smith 5 True False Yes
brown 99 True False Yes
|||
Fantastic Terri, you are really fantastic.
I once read that Books Online are good, but are there are resources for SQL Server that you usally trust?
Thanks a lot.
|||Terri, what I am doing is kind of a complicated thing.It is a stored procedure, where I am using like 7 UDFs inside it, each has its own responsibility.
I have one more question, in a stored procedure, can I return a table?
I mean, inside the SP I will have several SELECT statements, I have like 8 fields, I want to have data for them for each employee, sometimes I might return 1 row, other times many rows, so what I am doing is, go through each row in the employees table, using the CURSOR, and then process eac employee alone, either I process one employee or many.
during procesing I will get a record of 8 fields for the employee,I want to insert that into a table, because I might have several employees, how to do that in SP ?
thanks a lot really, you are saving me.|||I'm glad to help :-)
I once read that Books Online are good,but are there are resources for SQL Server that you usallytrust?
I recently changed jobs, and currently Books Online is the onlyresource for SQL Server that I have. I use it *continually*.
For websites, I most often use Google. I've also gotten a lot of help fromhttp://www.sql-server-performance.com.
On the shelf at my last job we had Ken Henderson'sThe Guru's Guide to Transact-SQL, which is excellent. Also, I have not used this book myself, but it comes highly recommended: Rob Veiera'sProfessional SQL Server 2000 Programming. I still have lots more to learn. But I have learned what I know so farwith both practical on-the-job experience, and reading and replying toposts here and on the lists athttp://www.sqladvice.com. I've certainly made mistakes in some of the advice I've given, and whenothers have come along and made a different/better suggestion this iswhere I've learned the most, I think.
|||
Terri, what I am doing is kind of a complicated thing.
It is a stored procedure, where I am using like 7 UDFs inside it, each has its own responsibility.
I have one more question, in a stored procedure, can I return a table?
Imean, inside the SP I will have several SELECT statements, I have like8 fields, I want to have data for them for each employee, sometimes Imight return 1 row, other times many rows, so what I am doing is, gothrough each row in the employees table, using the CURSOR, and thenprocess eac employee alone, either I process one employee or many.
duringprocesing I will get a record of 8 fields for the employee,I want toinsert that into a table, because I might have several employees, howto do that in SP ?
thanks a lot really, you are saving me.
Are you certain you need to use cursors? You need to break freeof the procedural mindset and start thinking about sets of data. When you say you need to process each employee, what is it that you aredoing?
Explaining it further, rather than creating a loop with a cursor toinsert one record at a time into a table, you can do it in one fellswoop with a statement like this:
INSERT INTO|||
someTable
(
column1,
column2
)
SELECT
column1,
column2
FROM
someOtherTable
WHERE
@.someID = -1 OR @.someID = someID
Hi Terri:
What do you mean by "sets of data", what is the difference between that and Cursors?
What I am doing is the following:
1- I am getting a list of all employees
2- for each employee I do the following:
2.1 Get attendance sheet (timein, timeout, absence, remarks)
3- if employee is scehduled
3.1 Get the schedule of the employee
3.2 get new values (required time in, required time out, required work time)
3.3 get difference in time in
3.4 get difference in time out
4- If not scheduled
4.1 I get normal schedule for all employees
4.2 I set the values for (required time in, ... as above)
I am doing that in one sp, it is in a project I have employees and I want to keep track of their attendance.
so, I am lopping through each employee, generated one record as output:
EmployeeName, timeIn, Requiredtimein, timeout, requiredtimeout, worktime, requried work time, break. diffin, diffout, absence, remarks
That is the record that should be returned for each employee.
So what are your ideas?
Thanks a lot and good luck in your work.
SELECT
E.EmployeeName,
A.TimeIn,
ISNULL(S.RequiredTimeIn, NS.RequiredTimeIn) AS RequiredTimeIn,
A.TimeOut,
ISNULL(S.RequiredTimeOut, NS.RequiredTimeOut) ASRequiredTimeOut,
A.TimeOut - A.TimeIn AS WorkTime,
ISNULL(S.RequiredWorkTime, NS.RequiredWorkTime) ASRequiredWorkTime,
A.Break,
A.TimeIn - ISNULL(S.RequiredTimeIn, NS.RequiredTimeIn) AS DiffIn,
A.TimeOut - ISNULL(S.RequiredTimeOut, NS.RequiredTimeOut) AS Diffout,
A.Absence,
A.Remarks
FROM
Employee E
LEFT OUTER JOIN
Attendance A ON E.EmployeeID = A.EmployeeID
LEFT OUTER JOIN
EmployeeSchedule S ON S.employeeID = E.employeeID
LEFT OUTER JOIN
NormalSchedule NS ON NS.employeeID = E.employeeID
I looked up a few resources to help you with the concept of set-based logic (instead of procedural logic):
SQL Cheat Sheet: Query By Example
Thinking SQL: Set-based logic can improve query performance
Procedural Versus Declarative Languages
