Matching two tables in SQL on certain Fields - Database Discussion Boards

Re: cursors in functions

Krish - KP18-Oct-07 23:24

Krish - KP

18-Oct-07 23:24

1. remove BEGINafter the Decleration of A & B
2. and include before before A & B

Regards
KP

Re: cursors in functions

Sonia Gupta18-Oct-07 23:35

Sonia Gupta

18-Oct-07 23:35

CREATE FUNCTION [dbo].[waste] ()
RETURNS table
AS
declare @a int
declare @b int

declare cur cursor for select a1,a2 from a
open cur
fetch next from a into @a,@b
while @@FETCH_STATUS =0
BEGIN
SELECT @a as a1 , @b as a2
fetch next from a into @a , @b
END
close cur
deallocate cur
return
GO

Now it is saying
incorect syntax near the keyword declare.
return statement statement i scalar valued function must include an argument

Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1

Re: cursors in functions

Rocky#19-Oct-07 0:55

Rocky#

19-Oct-07 0:55

youre declaring that the fn will return a table and look at the last line

Sonia Gupta wrote:
return
GO

whats happening here??

Rocky

You can't climb up a ladder with your hands in your pockets.

Re: cursors in functions

neeraj_indianic19-Oct-07 19:25

neeraj_indianic

19-Oct-07 19:25

You have not specified that which type of function you want to create means scalar valued function or table valued function.

I suppose that you want to create table valued function. For that your code is wrong in the starting line.
Look at the below code. Hope it will guide you.

CREATE FUNCTION [dbo].[waste]()
RETURNS @student1 TABLE
(
student_rec int,
St_name nchar(10)
)
AS
Begin
declare @a int
declare @b nchar(10)
declare cur cursor for select Id,name from student
open cur
fetch next from cur into @a,@b
while @@FETCH_STATUS =0
BEGIN
INSERT @student1 values(@a,@b)
--SELECT @a as a1 , @b as a2
fetch next from cur into @a , @b
END
close cur
deallocate cur
RETURN
end

Neeraj Gupta
IndiaNIC Infotech Ltd.

CORBA , MSP

hellogany18-Oct-07 17:36

hellogany

18-Oct-07 17:36

hi all,
i m ganesh new to code project....

i jus want to know all information about CORBA and MSP for database connection. Is anyone to help me out? n the details needed asap...thnks in advance...

GANESH

Re: CORBA , MSP

Colin Angus Mackay18-Oct-07 22:37

Colin Angus Mackay

18-Oct-07 22:37

hellogany wrote:
i jus want to know all information about CORBA and MSP for database connection

I think if we were to tell you ALL the information about that then we'd exceed the forum guidelines for length of post. Have you tried google?

Upcoming FREE developer events:
* Glasgow: SQL Server Managed Objects AND Reporting Services ...

My website

Re: CORBA , MSP

Paul Conrad21-Oct-07 10:31

Paul Conrad

21-Oct-07 10:31

hellogany wrote:
want to know all information about CORBA

It is a very big topic and not one that can have all parts covered in a post. You are best to start researching...

"Find it your bloody self - immediately!" - Dave Kreskowiak

Matching two tables in SQL on certain Fields

Vimalsoft(Pty) Ltd18-Oct-07 4:41

Vimalsoft(Pty) Ltd

18-Oct-07 4:41

hi all

i have a table called Employee and Employees. i want to see if these tables have a same value on the following Fields "EmployeeName","Lastname", all this tables have the a Primary key called "Primary_KEY", how can i check if there is any record that is not the same and Display it. thanks

Thanks

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sudden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

Re: Matching two tables in SQL on certain Fields

andyharman18-Oct-07 4:55

andyharman

18-Oct-07 4:55

For SQL-Server:

select E1.Primary_Key, E2.Primary_Key,
   IsNull(E1.EmployeeName, E2.EmployeeName) AS EmployeeName,
   IsNull(E1.LastName, E2.LastName) AS LastName
from Employee E1
full outer join Employees E2
on E1.EmployeeName = E2.EmployeeName
and E1.LastName = E2.LastName
where (E1.Primary_Key is null or E2.Primary_Key is null)

Regards
Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Re: Matching two tables in SQL on certain Fields

Vimalsoft(Pty) Ltd18-Oct-07 5:40

Vimalsoft(Pty) Ltd

18-Oct-07 5:40

Thanks and Rate for your Reply

I will try it

Rated

put a default value

Sarfaraj Ahmed18-Oct-07 4:36

Sarfaraj Ahmed

18-Oct-07 4:36

Hello Everybody !!

How Can I put a default value in dataColumn

Thanks

Sarfarj Ahmed

select statement to another database on another server

Support12318-Oct-07 2:11

Support123

18-Oct-07 2:11

Hi all,

I have this problem:

i want to write a select statement. But i am connected to one server and i want to select on another database on another server????

How do i go about doing so?

if i run this script:
SELECT

a.*

FROM
OPENROWSET('SQLOLEDB.1','Data Source=<OurServerName>;Initial Catalog=<OurDataBaseOnThatServer>;User ID=<OurUserName>;password=<OurPassword>;','SELECT TOP 1 FROM <OurServerName>.<OurDataBaseOnThatServer>.dbo.<TableNameInThatDataBase>') a

Am i doing something wrong??? i get an error: "Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB.1' reported an error."

Please advise Cry | :((

"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison

P.S.

Support12318-Oct-07 2:16

Support123

18-Oct-07 2:16

I found this code on the internet BUT i haven't run it yet.

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE<br />
go<br />
<br />
EXEC sys.sp_configure N'Ad Hoc Distributed Queries', N'1'<br />
go <br />
<br />
RECONFIGURE WITH OVERRIDE<br />
go<br />
<br />
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE<br />
go

Do you think this could solve my problem? Confused | :confused:

"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison

Re: P.S.

astanton197819-Oct-07 5:22

astanton1978

19-Oct-07 5:22

Probably.... Its better practice to add the other server as linked server if you are going to use the connection often enough. It will simplify your query writing (no openrowset crap needed).

Redneckin with www.wantedband.com

Re: select statement to another database on another server

neeraj_indianic22-Oct-07 20:10

neeraj_indianic

22-Oct-07 20:10

I have seen that to use OPENROWSET command the first parameter should be MSDASQL
the SQLOLEDB is used for OPENDATASOURCE. i have tried this query and working perfectly. Also i hope that you have already enabled the OPENROWSET and OPENDATASOURCE command from the SQL server surface area configuration wizard.

SELECT *
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=yourremoteserver_name;UID=your_remote_user_id;PWD=your_remote_password',
your_remote_database_name.dbo.remote_table_name)

Neeraj Gupta

how to capture count of records from stored procedrure

attalurisubbu18-Oct-07 2:01

attalurisubbu

18-Oct-07 2:01

Hi,

I want only count of a records returning by a particular stored procedure..

Im trying like this..but not coming..

DECLARE @COUNT INT

SET @COUNT = EXEC 'SP NAME'

SELECT @COUNT

please help me..

bala

Re: how to capture count of records from stored procedrure

Illegal Operation18-Oct-07 2:03

Illegal Operation

18-Oct-07 2:03

SELECT COUNT(*) FROM TABLE WHERE Variable = @Variable.

Hope it helps.

Illegal Operation

Making Computer Software Talk

Re: how to capture count of records from stored procedrure

attalurisubbu18-Oct-07 2:06

attalurisubbu

18-Oct-07 2:06

hi,
thanx for ur reply..
but i want count of (number only) records returing by a particular stored procedure..not table records count..

bye

Re: how to capture count of records from stored procedrure

pmarfleet18-Oct-07 2:05

pmarfleet

18-Oct-07 2:05

@@ROWCOUNT will return the no. of rows affected by the last operation in the current session.

Paul Marfleet

Re: how to capture count of records from stored procedrure

attalurisubbu18-Oct-07 2:15

attalurisubbu

18-Oct-07 2:15

hi paul, thanx for ur reply..

but in this scenario rowcount will not work.. im trying like this

DECLARE @COUNT INT

SET @COUNT = EXEC 'SP NAME'

SELECT @COUNT

can we capture only count of records returning by stored procedures like this??

Re: how to capture count of records from stored procedrure

pmarfleet18-Oct-07 2:23

pmarfleet

18-Oct-07 2:23

Works OK when I try it...

Paul Marfleet

Re: how to capture count of records from stored procedrure

neeraj_indianic19-Oct-07 21:12

neeraj_indianic

19-Oct-07 21:12

You can use @@ROWCOUNT in your stored procedure and take the value returned by @@ROWCOUNT as output parameter of stored procedure.

Automatic Add and Update

Sarfaraj Ahmed18-Oct-07 1:36

Sarfaraj Ahmed

18-Oct-07 1:36

Hello Everybody

I am new in SQL SERVER 2005 and ASP.NET 2.0 , Im doing a Online Library Management Project, I know you are expert and please help me.

On Borrower Table
MemberID, ItemID, DateTaken, Duedate, SubmitDate, Delay

On Penalty Table
MemberID, ItemID, Fine, Paid, DatePaid

(B as Borrower and P as Penalty) and a Member borrow a Item and Borrower below

B.MemberID = SCOM1001
B.ItemID=EBEG100302
B.DateTaken = 14/10/2007
B.DueDate= 16/10/2007
B.Submitdate= NULL
B.Delay=NULL

right now CurrentDate is 18/10/2007 and M.MemberID = SCOM1001 did not return ItemID = EBEG100302 .

Per day penalty is 1.500 and Member already delayed 2 days and Tomorrow it will be 3 days then 4 days then 5 days............

Just think Penalty Table is Empty, If you delay 1 day then Penalty Table will automatic add MemberID, ItemID, Fine

P.MemberID=SCOM1001
P.ItemID = EBEG100302
P.Fine=1.500
P.Paid=NO
P.PaidDate=NULL

Dealy day 2
Penalty Table will automati update P.Fine, Value will be 3.000

Delay day 3, day4....................... UpdateP.Fine

My Question is it possible to write some code inside my SQL SERVER datbase, Which will automatic check the B.DateDue column and (add or update Penaly Table where necessary)
I can write some function inside ASP.NET which will create a report for my needs but It will be taken more time.

I want my DataBase will check column status and put value to other table or same table where necessary.

Please tell me how can I do it? Any instruction will be really helpful.

Thanks
Sarfaraj Ahmed

Sarfarj Ahmed

Re: Automatic Add and Update

pmarfleet18-Oct-07 2:04

pmarfleet

18-Oct-07 2:04

Didn't you ask the same question last week? I gave you some advice then.

Use an INSERT...SELECT to select the records from the Borrower table where SubmitDate is greater than DueDate and insert corresponding entries into the Penalty table. Calculate the fine based on the logic I explained in my previous reply.

Paul Marfleet

Re: Automatic Add and Update

Sarfaraj Ahmed18-Oct-07 2:26

Sarfaraj Ahmed

18-Oct-07 2:26

Thanks For Your Reply

I followed your instruction last time.

I put that query inside ASP.NET and I have to call every single time for updating information.

I want this code inside my database which will be executed automatically and put that value on the correct column.

Could Please Help me about this.

Thanks

Sarfarj Ahmed

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.