Database

1-Mar-07 1:05

Hi G. to the U.R.U.S.!

I have a table with the following fields

MessageID
MessageTitle
MessageText

I want to create an SP which allows me to:
1. add new message if there's no similar message on it
2. edit the message is theres existing message already.

Please advice

regards
Dom Wink | ;)

Pete O'Hanlon1-Mar-07 1:42

Pete O'Hanlon

1-Mar-07 1:42

It's hard to know which of the fields you are intending to define as the control. If you just want to base the check on the message title, then the following will suffice:

IF Exists (SELECT MessageID FROM Message
  WHERE MessageTitle = @Title)
BEGIN
  UPDATE Message SET MessageText = @MessageText WHERE MessageTitle = @Title
END
ELSE
BEGIN
  INSERT INTO Message (MessageTitle, MessageText) VALUES
  (@Title, @MessageText)
END

the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer

Deja View - the feeling that you've seen this post before.

firestoper1-Mar-07 2:06

1-Mar-07 2:06

Hi John

Thanks for the help,

I tried the suggestion you've made but some weird thing comes up. I don't know where @message_id came from, I don't remember having it on the SP. any ideas?

Msg 201, Level 16, State 4, Procedure sp_altermessage, Line 0<br />
Procedure or Function 'sp_altermessage' expects parameter '@message_id', which was not supplied.<br />

Pete O'Hanlon1-Mar-07 2:45

Pete O'Hanlon

1-Mar-07 2:45

OK - my names Pete not John. John's the name of somebody I've quoted. (Note to self, change sig).

Secondly, you need to declare @message_id.

CREATE PROCEDURE dbo.PostMessage
  @Title NVARCHAR(50),
  @MessageText NVARCHAR(200),
  @message_id INT OUT
AS
SET NOCOUNT ON

IF Exists (SELECT @message_id = MessageID FROM Message  WHERE MessageTitle = @Title)
BEGIN
  UPDATE Message SET MessageText = @MessageText 
  WHERE MessageTitle = @Title
END
ELSE
BEGIN
  INSERT INTO Message (MessageTitle, MessageText) VALUES  (@Title, @MessageText)
  SET @message_id = SCOPE_IDENTITY()
END

firestoper1-Mar-07 12:35

1-Mar-07 12:35

woot woot! :->

Thanks Pete
Dom

gauthee1-Mar-07 1:45

gauthee

1-Mar-07 1:45

Hi,

Add when that does not exist or edit if exists, but what are you checking with message title column or message text column???

Gautham

firestoper1-Mar-07 2:10

1-Mar-07 2:10

Hi Gautham,
I'm checking the message title, picture this as a message board, if there's existing message title this means that that user is updating / posting on the message text, but if theres no existing title, this means the user intends to add another set of message.

btw im using messageID as an output (scope_identity) to reference the lates message that was added.

Thanks
regards
Dom

Error on SP I made

firestoper1-Mar-07 0:36

1-Mar-07 0:36

Hi Guru's

Im somehow stuck with the procedure I made, can you help me out with this, its a simple select but I put it on a declared variable.

Here's the code

SET ANSI_NULLS ON<br />
SET QUOTED_IDENTIFIER ON<br />
GO<br />
<br />
alter PROCEDURE trc_spVisitorPath<br />
(<br />
<br />
	@visitorID int = 0<br />
<br />
)<br />
<br />
as<br />
	begin<br />
	declare @Sel_Pageview varchar(255)<br />
<br />
		set @Sel_Pageview = <br />
		'select <br />
			convert(char(11),DateEntered) as ''Date Entered'', <br />
			browser,<br />
			platform,<br />
			MajorVersion,<br />
			MinorVersion<br />
		from<br />
			visitors <br />
		where <br />
			visitorID = ' + @visitorID<br />
<br />
	end<br />
<br />
	begin<br />
	declare @Sel_Visitor varchar(255)<br />
	<br />
		set @Sel_Visitor = <br />
		'select <br />
			convert(char(13),DateEntered) as ''Date Entered'',<br />
			PageName as ''Page Name''<br />
		where<br />
			visitorID = ' + @visitorID<br />
	end<br />
<br />
exec(@Sel_Pageview)<br />
exec(@Sel_Visitor)

I'm getting this error after I run it

Msg 245, Level 16, State 1, Procedure trc_spVisitorPath, Line 13<br />
Conversion failed when converting the varchar value 'select <br />
			convert(char(11),DateEntered) as 'Date Entered', <br />
			browser,<br />
			platform,<br />
			MajorVersion,<br />
			MinorVersion<br />
		from<br />
			visitors <br />
		where <br />
			visitorID = ' to data type int.

thanks
Dom

Re: Error on SP I made

Paddy Boyd1-Mar-07 0:44

Paddy Boyd

1-Mar-07 0:44

Why are you setting these up as strings and then executing them? You know that only the results from the final select will be returned?

The error comes from the fact that you have a string that you are trying to add (+) to an integer value (@VisitorId).

Why are you not just doing:

select
convert(char(11),DateEntered) as 'Date Entered',
browser,
platform,
MajorVersion,
MinorVersion
from
visitors
where
visitorID =  @visitorID

etc...

Re: Error on SP I made

firestoper1-Mar-07 0:56

Intersect in SQL Server2000

1-Mar-07 0:56

I'm just trying to shoot the moon.
Thanks for the advice Paddy!

regards
Dom;)

Prashant C28-Feb-07 23:34

Prashant C

28-Feb-07 23:34

how to get the result intersection of two queries.
can any one give code ???
I tried the following query

select * from tableA
intersect
select * from tableB

but it showing the error like:

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'intersect'.

please anybody help.

Krish - KP28-Feb-07 23:52

Krish - KP

28-Feb-07 23:52

there is no intersect in SQL Server 2000

select * from tableA where colA not in (select colA from tableB)

this returns all rows from tableA which has no corresponding row matching for colA in tableB

Regards
KP

gauthee1-Mar-07 0:06

gauthee

1-Mar-07 0:06

There are only 3 set operators i.e. union, except, crossjoin!!
However if you want the intersection to be done you could either use where exists , not exists or inner joins

examples:
SELECT DISTINCT city
FROM authors
WHERE EXISTS
(SELECT *
FROM publishers
WHERE authors.city = publishers.city)

SELECT DISTINCT authors.city
FROM authors INNER JOIN publishers
ON authors.city = publishers.city

SELECT DISTINCT city
FROM authors
WHERE NOT EXISTS
(SELECT *
FROM publishers
WHERE authors.city = publishers.city)

Gautham

Prashant C1-Mar-07 0:39

Prashant C

1-Mar-07 0:39

Thanks for solution but, still my problem is not solvedi will make clear what is my problem:

tableA tableB
------------------------------------
id skl.name id skl.name
--------- ---------
1 vb 2 java
2 c# 2 EJB
2 vb.net 3 J2me
3 asp.net 4 jsp

in these two table i need fetch the data like:

id skl.name
-------------
2 c#
2 vb.net
2 java
2 ejb
3 asp.net
3 j2me

regards,
PRT

Harini N K1-Mar-07 0:45

Harini N K

1-Mar-07 0:45

Hi
I have little doubt on your question. Do you want to intersect those two tables based on the column ID ??

If Table1 has {1,2,2,3} and Table 2 {2,2,3,4} then you want {2,3,4} display values from two tables.

Rate this message. Thank you. Harini Smile | :)

Harini N K1-Mar-07 0:54

Harini N K

1-Mar-07 0:54

Hi Parasu,

Your solution is here:

<br />
SELECT TABLEA.* FROM TABLEA<br />
WHERE EXISTS<br />
           (SELECT * FROM TABLEB   WHERE TABLEA.ID = TABLEB.ID)<br />
UNION <br />
SELECT TABLEB.* FROM TABLEB<br />
WHERE EXISTS <br />
   (SELECT * FROM TABLEA WHERE TABLEA.ID = TABLEB.ID)<br />

Please note the difference here:

UNION

This removes duplicate results

UNION ALL

Incorporates all rows into the results, including duplicates. If not specified, duplicate rows are removed.

Hope this solves your problem

Rate this message. Thank you. Harini Smile | :)

Prashant C1-Mar-07 1:11

Prashant C

1-Mar-07 1:11

Thank you very much Harini