|
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 
|
|
|
|
|
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.
|
|
|
|
|
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 />
|
|
|
|
|
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
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.
|
|
|
|
|
woot woot! :->
Thanks Pete
Dom
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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...
|
|
|
|
|
I'm just trying to shoot the moon.
Thanks for the advice Paddy!
regards
Dom;)
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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 
|
|
|
|
|
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
|
|
|
|
|
Thank you very much Harini
|
|
|
|
|
Select * from tablea,tableb where tablea.columnname=tableb.columnname.
Generally Intersection means having data inboth the tables should be displayed.
For this we have to put a column in both tables which contains same data
So, Select * from two tables where columnin tablea=column in tableb are equal
Priya
|
|
|
|
|
Is it possible to run a .sql file on sql server through .net code?
Nana
|
|
|
|
|
NanaAM wrote: Is it possible to run a .sql file on sql server through .net code?
Yes.
However, remember that GO is not part of SQL. It is a preprocessor commant to the Query Analyser that tells it where to split the Script into individual batches. You must make the same splits when issuing the SQL via the SqlCommand.
I blogged in more detail about this here[^] - It will show you how to handle the situation with GOs too.
|
|
|
|
|
Thanks for your help.
But I wanted to ask whether we can run the script file, the .sql file direclty?
For this I need to store the file contents in string and pass it to commandText
Nana
|
|
|
|
|
NanaAM wrote: But I wanted to ask whether we can run the script file, the .sql file direclty?
For this I need to store the file contents in string and pass it to commandText
Yes, my previous answer shows you how to do that. Please look at the source code to the application to see how that is done.
|
|
|
|
|
Dear All,
i want to know how to get top three salary getters from the employee(eid , ename, salary) table
i tried this
select top 3 salary from employee order by salary desc
but it gives me top three salary record say there is salary 1000,1200,1300,1300,1500
then my query return me 1500,1300,1200 whereas i want to 1500,1300,1300,1200
how can i do it
please help
thanks
regards
imran khan
|
|
|
|
|
SELECT * FROM Employee
WHERE (Salary >=SELECT TOP 1 Salary FROM (SELECT DISTINCT TOP 3 Salary FROM Employee ORDER BY Salary DESC) DERIVEDTBL ORDER BY Salary))
|
|
|
|
|
Here is the pur SQL query. This will work irrespective of any RDBMS
select eid , ename, salary from employee
where salary in
(
(select max(salary) from employee),
(select max(salary) from employee
where salary<
(select max(salary) from employee)),
(select max(salary) from employee
where salary<(select max(salary) from employee
where salary<(select max(salary) from employee)))
)
order by salary desc
Regards,
Sylvester G
sylvester_g_m@yahoo.com
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
