updating different fields of a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

updating different fields of a table

Author

Topic

jonekim
Starting Member

35 Posts

Posted - 2011-05-27 : 16:12:19

I've created a table:
************************
stud_registraion #tablename
student_id
student_name
student_enrollment_date (datavalue->datetime)
student_roll
student_subject #one student can enroll for a single course only
*************************************
I want to update some of the values of the tables according to the user input.
Suppose I need to update:

student_roll 'OR' #only one at a time
student_subject
OR
student_roll 'AND' #both
student_subject

as per the user input.

How can I write stored procedure for this problem??

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-05-27 : 17:30:55

I'm not sure I follow, what does "as per user input" mean?

Here is a link that might help you form your question a little better as well as how to provide us with DDL, DML and expected output:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

but maybe something like this will help get you going??

IF (<some condition>)
BEGIN
    UPDATE <table name>
    SET student_roll = <some value>
END
ELSE IF (<some other condition>)
BEGIN
    UPDATE <table name>
    SET student_subject = <some value>
END    
ELSE
BEGIN
    UPDATE <table name>
    SET student_roll = <some value>,
    student_subject = <some value>
END

jonekim
Starting Member

35 Posts

Posted - 2011-05-28 : 00:51:45

@Lamprey
"as per the user i/p"-> user must be allowed to change one of the field at a time or both. I mean if the student wants to change the subject but not the roll he/she must be allowed to change it or if he/she wants to change both of the field it is also allowed.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-28 : 07:25:08

You can perhaps do it by creating a stored procedure that accepts the two parameters along with the student id, and update only if the parameter is not null. For example, something along these lines:


create procedure dbo.YourUpdateProcedure,
	@student_id int,
	@new_roll int = null,
	@subject varchar(32) = null
as
	update YourStudentTable set
		new_roll = isnull(@new_roll,new_roll),
		subject = isnull(@subject,subject)
	where
		student_id = @student_id;
GO

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-05-28 : 23:42:42

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help. It would also help if you knew how to normalize a schema

Let me do your work for you

CREATE TABLE Students
(student_id CHAR(10) NOT NULL PRIMARY KEY,
student_name VARCHAR(35) NOT NULL,
..);

CREATE TABLE Courses
(course_nbr CHAR(7) NOT NULL PRIMARY KEY,
course_name VARCHAR(35)NOT NULL,
..);

CREATE TABLE Registration
(student_id CHAR(10) NOT NULL
REFERENCES Students(student_id),
course_nbr CHAR(7) NOT NULL
REFERENCES Courses(course_nbr)
student_enrollment_date DATE NOT NULL,
PRIMARY KEY (student_id, course_nbr));

See how DRI enforces the 1:1 rule?

>> I want to update some of the values of the tables according to the user input. <<

Read that and see how vague this spec is. Your vague answer is “Use an UPDATE statement.”

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources