Query for distinct? - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Query for distinct?

Author

Topic

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-04-14 : 22:51:20

hi

this is my stored procedure

CREATE PROCEDURE [dbo].[sp_AddAnsTemp]
@id int, @proid int, @prodesc text, @proansw text,@proreso text,@chk int
AS
insert into TempAns(UserID,ProblemID,ProblemDescription,ProblemAnswer,ProblemResolution,Chk)
values (@id, @proid, @prodesc, @proansw,@proreso,@chk)
GO

i want if problemid already exist in table,can't save this same problemid
for eg:problemid(3) exist for userid(2) and this userid try to save again for problemid(3),i want that he can't save

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-15 : 03:10:04

CREATE PROCEDURE [dbo].[sp_AddAnsTemp]
@id int, @proid int, @prodesc text, @proansw text,@proreso text,@chk int
AS
If not exists(select * from TempAns where ProblemID=@proid)
Begin
insert into TempAns(UserID,ProblemID,ProblemDescription,ProblemAnswer,ProblemResolution,Chk)
values (@id, @proid, @prodesc, @proansw,@proreso,@chk)
End
GO

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 03:13:50

[code]CREATE PROCEDURE dbo.sp_AddAnsTemp
(
@id int,
@proid int,
@prodesc text,
@proansw text,
@proreso text,
@chk int
)
AS

SET NOCOUNT ON

IF EXISTS (SELECT * FROM TempAns WHERE ProblemID = @ProID)
UPDATE TempAns
SET UserID = @id,
ProblemDescription = @prodesc,
ProblemAnswer = @proansw,
ProblemResolution = @proreso,
Chk = @chk
WHERE ProblemID = @ProID
ELSE
INSERT TempAns
(
UserID,
ProblemID,
ProblemDescription,
ProblemAnswer,
ProblemResolution,
Chk
)
VALUES (
@id,
@proid,
@prodesc,
@proansw,
@proreso,
@chk
)
GO[/code]

E 12°55'05.25"
N 56°04'39.16"

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-04-15 : 04:46:59

for this procedure ,how to modify to get same target?

CREATE PROCEDURE [dbo].[sp_ongoSubmit]
@uid int
AS
insert into sampleUserAns(UserID,ProblemID,ProblemDescription,ProblemAnswer,ProblemResolution,Chk)
select UserID,ProblemID,ProblemDescription,ProblemAnswer,ProblemResolution,Chk
from TempAns
where UserID=@uid
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 04:54:42

You really need to study the suggestion we give to you!
This last request is soo easy to accomplish. Just give it some time.

E 12°55'05.25"
N 56°04'39.16"

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2008-04-15 : 22:01:09

hi Peso,

really thanks for your warm help and patient.
I'm just a beginner in SQL and i don't know so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources