use select query only once

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

hi to all,

i am maintaining history with remark column.
means

if table(tblclass) have some change say cname= manoj after update cname=ravi
than in history(tblclasshistory) remark refelct as :

SQL

Remarks
ClassName(ravi)(manoj)

i use antoher table that is descritption

SQL

fieldname         Description
cname              classname
rno                 rollnumber

problem is that i have to use select query two time when i need to use of description of change record

SQL

create procedure test
(
@cname varchar(20),
@rno int

)
as

update tblclass set cname=@cname, rno=@rno 
--to chech if their is any diffence for history
select desription from [description] where fieldname='cname'
select desription from  [description] where fieldname='rno'


problem is that if 20 reocrd change than i dont want use avobe select query twenty time to get description of change reocrd

please help

Posted 13-May-12 23:53pm

balongi

Add a Solution

Comments

Maciej Los 14-May-12 7:45am

Not clear!
UPDATE and SELECT commands will be run only one time, unless you call procedure test from your application in a loop.
Please, provide more details...

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RDBurmon · Accepted Answer · 2012-05-14T02:40:00

We are little bit clear about your queries.

Here is what we understand :
"You have one table called as tblclass and another table called "Description" to track history of changes.Suppose there is one row in tblclass which has cname as "manoj" if some one update that record and made cname as "ravi" then you need history in description table as "Classname(ravi)(manoj)" "

If above understanding is correct then below is my solution

You have not specified exact table schema of tblclass and description so I am considering this
tblclass (rno bingint,cname nvarchar(50))
description (rno bigint , remark NVARCHAR(MAX))

1) Write below trigger on tblclass table

SQL

CREATE  TRIGGER [dbo].[tblclass_Update_History]
   ON  [dbo].[tblclass]
   AFTER UPDATE
AS
BEGIN
   INSERT INTO Description(rno,remark)
   SELECT rno,'Classname (' + d.cname + ')(' + I.cname + ')' FROM INSERTED I INNER JOIN Deleted D ON  I.rno=D.rno WHERE I.cname <> D.cname

END

Hope this helps if yes then accept and vote the answer otherwise revert back with your queries.
--RDBurmon Sr.Software Engineer