December 13, 2002 at 1:29 pm
Hi everyone, this is my first post to these boards, I hope its not something that hasn't been covered before.
I've written some basic stored procedures before, returning @@identity and so forth, and am marginally famaliar with basic T-SQL, but haven't really delved full on into the more complex stuff. However, for a current project I am looking at writing a stored proc that compares values from a palm database record, with an extant record in an SQL Server database. What I want to do is pass in the ID for the record, a date, and a boolean variable. Using the variable, i want to get the record, and compare the passed in boolean value with the current boolean in the database. Based on that, i'll either update the record or compare the dates, then update the record or just return 0 with no update.
I believe I have most of this worked out, but I can't figure out how to access the results I get back from the query. I think I need to declare a cursor, but I'm unsure how exactly to go about doing that. Any help would be much appreciated.
Thanks,
Chris Martin
---
"So why are you a history major?"
"Well . . . I got kicked out of the CS department."
"Why?"
"Lets just say that calling the Head of the Department a 'pompous assclown' was not the best academic decision I ever made."
---
"So why are you a history major?"
"Well . . . I got kicked out of the CS department."
"Why?"
"Lets just say that calling the Head of the Department a 'pompous assclown' was not the best academic decision I ever made."
December 13, 2002 at 1:49 pm
If your query is only going to return 1 record then you can simply select the field value into a variable as follows:
DECLARE @bFoo bit
SELECT @bFoo = Foo
FROM TBL
WHERE ID = @ID
Otherwise you will need to create a cursor and handle each record in the cursor.
Hope this helps!
Gary Johnson
DBA
Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
December 13, 2002 at 2:20 pm
Thanks a bunch. I actually need two columns from the DB, but i only need one on a conditional basis, so I just used two variables. Here is the stored procedure, in all its glory, if anyone is interested.
Again, thanks a lot for your quick response.
--Stored procedure begins here
CREATE PROCEDURE CompareAssignmentRecord_Update
@PalmStatus bit,
@PalmCompleted DateTime,
@AssignmentID int,
@dbStatus bit,
@dbDate DateTime
AS
SELECT @dbStatus = IsCompleted From tblAssignments WHERE AssignmentID = @AssignmentID
IF @dbStatus = 1 --if the record in teh DB is already flagged as comlete, compare the datecompleted values. the lowest date wins.
Begin
SELECT @dbDate = CompletedDate FROM tblAssignments WHERE AssignmentID = @AssignmentID
If @dbDate > @PalmCompleted
Begin
UPDATE tblAssignments SET CompletedDate = @PalmCompleted WHERE AssignmentID = @AssignmentID
End
Else
--if the database record already has the lower date, dont do anything, and return 1
Begin
Return 1
End
End
Else --if the db record is not flagged as completed, set it to completed
Begin
Update tblAssignmnets SET CompletedDate = @PalmCompleted, IsCompleted = 1
Return 0
End
GO
---
"So why are you a history major?"
"Well . . . I got kicked out of the CS department."
"Why?"
"Lets just say that calling the Head of the Department a 'pompous assclown' was not the best academic decision I ever made."
---
"So why are you a history major?"
"Well . . . I got kicked out of the CS department."
"Why?"
"Lets just say that calling the Head of the Department a 'pompous assclown' was not the best academic decision I ever made."
December 16, 2002 at 4:18 am
If you want the return value
in tsql you can use @reply = EXEC procname, in ADO the return is in the parameter RETURN_VALUE.
If you want other variables then add OUTPUT to any parameter you require to be output from the procedure. I use this to return the id of the created record back to the app.
Far away is close at hand in the images of elsewhere.
Anon.
December 16, 2002 at 1:02 pm
Good Proc.
Documented and NO CURSORS unless absolutely neccessary.
Simple concepts, but you'd never believe how often they're thrown out the window.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply