Database

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

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 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

How to run script file through .net?

Priya_20071-Mar-07 20:29

Priya_2007

1-Mar-07 20:29

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

NanaAM28-Feb-07 21:33

Re: How to run script file through .net?

28-Feb-07 21:33

Is it possible to run a .sql file on sql server through .net code?

Nana

Colin Angus Mackay28-Feb-07 23:35

Colin Angus Mackay

28-Feb-07 23:35

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.

Upcoming events:
* Glasgow: Geek Dinner (5th March)
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)

My: Website | Blog | Photos

Re: How to run script file through .net?

NanaAM1-Mar-07 19:24

Re: How to run script file through .net?

1-Mar-07 19:24

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

Colin Angus Mackay1-Mar-07 21:24

Colin Angus Mackay

1-Mar-07 21:24

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.

Upcoming events:
* Glasgow: Geek Dinner (5th March)
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)

My: Website | Blog | Photos

how to get top theree salary getters from table

mohd imran abdul aziz28-Feb-07 19:51

mohd imran abdul aziz

28-Feb-07 19:51

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

Re: how to get top theree salary getters from table

sam#28-Feb-07 20:09

sam#

28-Feb-07 20:09

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))

Re: how to get top theree salary getters from table

Sylvester george2-Mar-07 0:01

Sylvester george

2-Mar-07 0:01

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

How to get script of a table?

NanaAM28-Feb-07 19:08

Re: How to get script of a table?

28-Feb-07 19:08

Hello,

Can someone tell me how can I get the entire script using query of table.
What I mean is, in SQL server, if we right click on a table, and say script table as-create to-New Query Editor Window, we get the entire script of the table.

I want that using a query.
Please help me its urgent.

Nana

sam#28-Feb-07 20:11

sam#

28-Feb-07 20:11

please enlighten us more . give us an example

Re: How to get script of a table?

NanaAM1-Mar-07 19:26