July 14, 2008 at 9:19 am
Hey Guys,
I need help in writing a stored procedure.
The requirement is that the table has a large number of records (approx 3000) and I need to update one or two columns of the table. Before that I might also need to extract an id that I need to pass to execute the query above which in turn I will have to extract by passing another parameter which I have.
I am a novice at stored procedure/SQL and will appreciate any help
Thanks in Advance
July 14, 2008 at 9:24 am
3000 records is quite small actually. Have you taken a stab at this already, and do you have any code that you can show that you are having a specific issue with? Creating an update procedure is not to different from any other procedure:
CREATE TABLE Processor
(rowid int identity primary key,
jobid int,
ProcessTime datetime default(getdate()))
INSERT INTO Processor (jobid)
SELECT 1001
UNION SELECT 1002
GO
CREATE PROCEDURE uspUpdateProcessorProcessTime (@ID int)
AS
BEGIN
UPDATE Processor
SET processTime = getdate()
WHERE rowid = @id
END
GO
SELECT * FROM Processor
GO
EXEC uspUpdateProcessorProcessTime 1001
GO
SELECT * FROM Processor
this is overly simplistic, but demonstrates the concept.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
July 14, 2008 at 9:24 am
Harsha Shettigar (7/14/2008)
Hey Guys,I need help in writing a stored procedure.
The requirement is that the table has a large number of records (approx 3000) and I need to update one or two columns of the table. Before that I might also need to extract an id that I need to pass to execute the query above which in turn I will have to extract by passing another parameter which I have.
I am a novice at stored procedure/SQL and will appreciate any help
Thanks in Advance
I'm thinking you're going to need some more specifics than that. Start by describing specifically what you're trying to do, and include whatever you've already started trying. A sample of the data as it stands, your table structure, and what you'd want to end up with at the end would also be important.
As a matter of fact - read through this - it will help you give us some info we can use to help you...
http://qa.sqlservercentral.com/articles/Best+Practices/61537/[/url]
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 14, 2008 at 9:28 am
You'll have to make an attempt and give us a little more direction.
CREATE PROCEDURE myProc
@param1 int = 1
as
is the starting point to create a procedure with a parameter. You can separate other parameters with commas, the "=1" is optional and sets a default.
A SELECT query extracts a value, so you might try writing one and seeing what you get. Report issues and we'll help you, but we want to see some work done.
UPDATE mytable
set MyCol = Myval
where someCol = Someval
Is the structure for an update. You can again, starting writing this and once you figure out what you need to do, enclose that inside the stored procedure.
If you check Books Online for UPDATE, SELECT, CREATE PROCEDURE, you'll see more examples.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 15, 2008 at 12:37 am
@ Jonathan,Steve - Thanks! I did check out some stored procedures for updates from the net, but then figured out that my problem was a little more complex.
@ Matt- Thanks for being a good guide 🙂 I figured out that I must provide much more information to have a realistic chance of getting my query solved.
I need to make use of four tables in my database to make the necessary database updates. I'll provide the create table statement below for each of the tables and also the sample data that I've extracted using Dbvisualiser from each of the tables below. I am unable to provide all the data for all the columns because of formatting issues and will provide only the value for those columns whose data will be useful in executing the query/procedure, hope that is not a problem.
Table 1
CREATE TABLE org_unit (
org_unit_id SERIAL8 NOT NULL PRIMARY KEY,
org_unit_name NVARCHAR(100,30) NOT NULL,
parent_id INT8,
has_queue SMALLINT NOT NULL,
auto_allocate_on SMALLINT NOT NULL,
available_flag SMALLINT NOT NULL,
auto_allocation_threshold INT8,
manual_allocation_threshold INT8,
queue_size INT8,
workload FLOAT(4),
auto_allocation_expression LVARCHAR(4096),
org_unit_type_code NVARCHAR(30,10),
workload_last_calculated DATETIME YEAR TO FRACTION DEFAULT CURRENT,
round_robin_flag SMALLINT DEFAULT 0 NOT NULL,
round_robin_timestamp DATETIME YEAR TO FRACTION,
handle_proactive_flag SMALLINT DEFAULT 0 NOT NULL,
FOREIGN KEY (parent_id) REFERENCES org_unit(org_unit_id) CONSTRAINT org_unit_parent_id,
FOREIGN KEY (org_unit_type_code) REFERENCES org_unit_type(org_unit_type_code) CONSTRAINT org_unit_type_code
) with crcols
--db.dbspace.gema--
--db.extent.org_unit--
lock mode row;
Sample data
org_unit_id org_unit_name parent_id
100318 MumTeamB 20005
Right now, I have the value of org_unit_name from Table 1 with me, using which I need to extract the value of org_unit_id from the same table.
Table 2
CREATE TABLE task (
task_id SERIAL8 NOT NULL,
new_info_flag SMALLINT DEFAULT 0 NOT NULL,
reallocated_flag SMALLINT DEFAULT 0 NOT NULL,
task_type_code NVARCHAR(30,10) NOT NULL,
creator_user_name NVARCHAR(50,10),
creation_datetime DATETIME YEAR TO FRACTION NOT NULL,
assignee_org_unit_id INT8,
assignee_email_address NVARCHAR(80),
ntp_user_service_area CHAR(3),
ntp_user_facility_code CHAR(3),
ntp_user_email_address NVARCHAR(80),
last_assignee_org_unit_id INT8,
FOREIGN KEY (task_type_code) REFERENCES task_type CONSTRAINT task_type_code,
FOREIGN KEY (creator_user_name) REFERENCES session_user(username) CONSTRAINT task_creator,
FOREIGN KEY (assignee_org_unit_id) REFERENCES org_unit(org_unit_id) CONSTRAINT task_assignee
) with crcols
--db.dbspace.task--
--db.extent.task--
lock mode row;
Sample data from Table 2
task_id task_type_code assignee_org_unit_id
1 Request 40015
2 Activity 40015
The org_unit_id extracted from Table 1 is the value of assignee_org_unit_id to be used in Table2. Corresponding to this assignee_org_unit_id the task_type_code can be of two types- Request or Activity. The task_id can either be request_id in Table 3 or it can be activity_id in Table 4.
More details about Request/Activity can be extracted from Table 3 and Table 4 below.
Table 3
CREATE TABLE request_task (
request_id SERIAL8 NOT NULL PRIMARY KEY,
shipment_id INT8, -- TODO: NOT NULL
request_type_code NVARCHAR(100,30) NOT NULL,
request_priority_code NVARCHAR(30,10) NOT NULL,
request_status_code NVARCHAR(30,10) NOT NULL,
externaldata_contact_id INT8,
inquirer_name NVARCHAR(80) NOT NULL,
inquirer_phone NVARCHAR(30,10),
inquirer_fax NVARCHAR(30,10),
inquirer_email NVARCHAR(80),
inquirer_timezone_code INT8,
inquirer_type_code NVARCHAR(30,10) NOT NULL,
closure_datetime DATETIME YEAR TO FRACTION,
comments LVARCHAR,
awb_number NVARCHAR(35,10), -- TODO remove!
row_version INT DEFAULT 0 NOT NULL,
shipment_override_flag SMALLINT NOT NULL,
reopened_flag SMALLINT NOT NULL,
last_update_datetime DATETIME YEAR TO FRACTION,
reopened_request_id INT8,
original_request_id INT8,
service_recovery_type NVARCHAR(100,10), -- May contain values from the service_recovery_type table or free form text, so no FK is required.
service_recovery_cost NVARCHAR(30,10),
service_recovery_currency_code INT8,
closing_username NVARCHAR(50,10),
closure_type_code NVARCHAR(50,10),
trace_origin_service_area_code CHAR(3),
trace_origin_facility_code CHAR(3),
trace_origin_creator NVARCHAR(50,10),
creator_ntp_user_name NVARCHAR(80),
source_of_trace_problem NVARCHAR(50,10),
source_media_code CHAR(3) default 'pho',
vi_flag SMALLINT DEFAULT 0 NOT NULL,
FOREIGN KEY (shipment_id) REFERENCES shipment CONSTRAINT request_to_shipment,
FOREIGN KEY (request_type_code) REFERENCES request_type CONSTRAINT request_to_request_type,
FOREIGN KEY (request_priority_code) REFERENCES request_priority CONSTRAINT request_to_request_priority,
FOREIGN KEY (request_status_code) REFERENCES request_status CONSTRAINT request_to_request_status,
FOREIGN KEY (request_id) REFERENCES task(task_id) CONSTRAINT request_to_task,
FOREIGN KEY (inquirer_type_code) REFERENCES inquirer_type CONSTRAINT request_to_inquirer_type,
FOREIGN KEY (closure_type_code) REFERENCES closure_type CONSTRAINT request_to_closure_type,
FOREIGN KEY (source_media_code) REFERENCES source_media(pk_source_media_code) CONSTRAINT request_to_source_media_code
) with crcols
--db.dbspace.request_task--
--db.extent.request_task--
lock mode row;
Sample data from Table 3
request_id request_status_code
1 req.stat.assigned
I need to update the value of request_status_code to req.stat.closed.
Table 4
CREATE TABLE activity_task (
activity_id SERIAL8 NOT NULL PRIMARY KEY,
parent_task_id INT8 NOT NULL,
due_datetime DATETIME YEAR TO FRACTION,
activity_priority_code NVARCHAR(30,10) NOT NULL,
activity_status_code NVARCHAR(30,10) NOT NULL,
activity_type_code NVARCHAR(100,30) NOT NULL,
comments LVARCHAR,
creator_ntp_user_name NVARCHAR(80),
ntp_trace_id NVARCHAR(50),
request_id INT8 NOT NULL,
target_type NVARCHAR(16),
target_service_area_code CHAR(3),
target_facility_code CHAR(3),
target_ops_flag SMALLINT,
closure_type_code NVARCHAR(50,10),
source_of_trace_problem NVARCHAR(50,10),
service_recovery_currency_code INT8,
service_recovery_cost NVARCHAR(30,10),
service_recovery_type NVARCHAR(100,10),
service_area_code CHAR(7),
complete_status NVARCHAR(30,10),
closed_datetime DATETIME YEAR TO FRACTION,
vi_flag SMALLINT DEFAULT 0 NOT NULL,
FOREIGN KEY (parent_task_id) REFERENCES task(task_id) CONSTRAINT activity_to_parent_task,
FOREIGN KEY (activity_type_code) REFERENCES activity_type CONSTRAINT activity_to_activity_type,
FOREIGN KEY (activity_status_code) REFERENCES activity_status CONSTRAINT activity_to_activity_status,
FOREIGN KEY (activity_id) REFERENCES task(task_id) CONSTRAINT activity_to_task,
FOREIGN KEY (activity_priority_code) REFERENCES activity_priority CONSTRAINT activity_to_activity_priority,
FOREIGN KEY (request_id) REFERENCES request_task CONSTRAINT activity_to_request_id
) with crcols
--db.dbspace.activity_task--
--db.extent.activity_task--
lock mode row;
CREATE INDEX at_ntpid on activity_task (ntp_trace_id);
Sample data from Table 4
activity_id parent_task_id activity_status_code
1684 1682 act.stat.assigned
I need to update the value of activity_status_code to act.stat.closed. The parent_task_id at times may match request_id from Table 3.In either case the value of activity_status_code should be updated to act.stat.closed.
In addition to these, the value of assignee_org_unit_id in Table 2 should be updated to null.
I hope the problem is clear and I have provided enough data. I guess I'll be able to write a SQL query to manually execute this requirement for one record. But the number of records is close to 3000 and therefore need a stored procedure.
Experts please help. Thanks in advance.
Regards,
Harsha Shettigar
P.S: Can someone please give me a link to the usage of the forum, how to transfer points etc. ?
July 15, 2008 at 1:16 am
Hi Harsha,
As Steve already mentioned, how to create a procedure and pass the parameter. I can give a idea how can you make it.
just make a batch of query to achieve what you want with the real value. (ex: 40015) and check is it working properly or not?
Then try to handel the transaction for the batch.
(ex: Begin tran, Commit, Rollback)
copy the code after As statements like this.
CREATE PROCEDURE
@param1 int = 1
as
And Execute it....and check the table for result.
EXEC
Hope it will help you...
Cheers!
Sandy.
--
July 15, 2008 at 3:53 am
I did write baby steps. Quite unclear as to how to proceed. I need inputs. Please help.
CREATE PROCEDURE team_requestclose()
{
AS
DEFINE temp_id SERIAL8;
SELECT org_unit_id into temp_id FROM org_unit WHERE org_unit_name= 'XYZ';
SELECT * FROM task WHERE assignee_org_unit_id = temp_id;
}
END PROCEDURE
July 15, 2008 at 9:52 pm
Harsha,
What flavour of SQL are you using?
SERIAL8, NVARCHAR(100,30), INT8, LVARCHAR are not valid in T-SQL.
DEFINE should probably be DECLARE.
You don't want ; in the stored procedure
Have a look at BOL for the correct syntax.
Peter
July 15, 2008 at 10:15 pm
If I had to guess, I would guess MySQL. The syntax may be similar - but, not knowing MySQL I would not bet on what works for T-SQL (Microsoft SQL Server) will work for you.
Peter - actually, you do want ';' at the end of statements. Beginning in SQL Server 2005 there are some commands that require it (example, CTE's require the preceding command to end with a semi-colon) and moving into SQL Server 2008 even more situations will require the semi-colon.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 15, 2008 at 10:20 pm
Peter Lavelle (7/15/2008)
Harsha,What flavour of SQL are you using?
SERIAL8, NVARCHAR(100,30), INT8, LVARCHAR are not valid in T-SQL.
DEFINE should probably be DECLARE.
You don't want ; in the stored procedure
Have a look at BOL for the correct syntax.
Peter
I'm using Informix database. My understanding is that SQLs wouldn't vary greatly??
July 15, 2008 at 10:37 pm
Harsha,
The syntax of the various SQLs varies quite a bit. The differences that hit you first as a developer are things like column datatypes and the need/don't need semi-colons after statements.
You really need to have a read of SQL Server Books Online (BOL) or some SQL Server book to see an example of CREATE PROCEDURE and check the valid data types.
Good luck,
Peter
July 15, 2008 at 10:40 pm
Harsha Shettigar (7/15/2008)I'm using Informix database. My understanding is that SQLs wouldn't vary greatly??
Well, sort of. Each vendor implements the ANSI standards to a particular level - and each one does not implement the full standard. So, you can have varying differences based upon which ANSI standards have been implemented in each product and what level.
Also, each product implements their own extensions - so, you will have to take that into consideration also.
And finally, each product implements stored procedures (if implemented at all) differently. This can really cause some problems if you are working with SQL Server using T-SQL and someone posts a PL\SQL solution for Oracle.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 15, 2008 at 10:41 pm
Jeffrey,
Thanks for your point about ;
Yes you are right of course.
I am using SQL 2005 and could not get WITH to work until I realised it needed a ; before it.
Thanks,
Peter
July 15, 2008 at 10:54 pm
Peter Lavelle (7/15/2008)
Jeffrey,Thanks for your point about ;
Yes you are right of course.
I am using SQL 2005 and could not get WITH to work until I realised it needed a ; before it.
Thanks,
Peter
And, it gets worse in SQL Server 2008 where it is now required in more places. So, what I tell everybody to start doing now is to get in the habit of using ';' at the end of all statements and remove it if you get a syntax error (e.g. when creating a view).
This way, you won't be surprised when it is actually required - because you are already in the habit of putting it in.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 16, 2008 at 12:20 am
Thanks for the help guys. I had no idea of procedure writing before this. It is done now!
Thanks again.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply