Stored Proc help needed - 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 2005 Forums

Transact-SQL (2005)

Stored Proc help needed

Author

Topic

o9z
Starting Member

23 Posts

Posted - 2007-07-15 : 21:33:44

My stored proc is not functioning as I want it to..here is what I have

I am trying to execute this stored proc

Here is the stored proc I am trying to execute in VB.Net code


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_CarcCombo]
    @KSId int
AS

IF (SELECT Count(*) AS RecCount FROM Carcass2.dbo.t_GroupData
LEFT JOIN Carcass2.dbo.Kilsheet
ON Carcass2.dbo.t_GroupData.ksid = Carcass2.dbo.Kilsheet.kilsheetid
WHERE
Carcass2.dbo.Kilsheet.kilsheetid = @KSid AND Carcass2.dbo.t_GroupData.shiprptno = Carcass2.dbo.Kilsheet.ship_report)
< 1

BEGIN
INSERT INTO Carcass2.dbo.t_groupdata(ShipRptNo, ksID, site, barn, id, head, weight)
SELECT ShippingReportData.dbo.HeadShipped.ShipRptNo, Carcass2.dbo.KILSHEET.KILSHEETID AS ksID, ShippingReportData.dbo.HeadShipped.Site, ShippingReportData.dbo.HeadShipped.Barn, ProdData.dbo.vOpenNonDupGroupIDs.LotNum AS ID, ShippingReportData.dbo.HeadShipped.Head, ShippingReportData.dbo.[Shipping Rpt].LiveWt AS weight
FROM ShippingReportData.dbo.HeadShipped LEFT OUTER JOIN ShippingReportData.dbo.[Shipping Rpt] ON ShippingReportData.dbo.HeadShipped.ShipRptNo = ShippingReportData.dbo.[Shipping Rpt].ShipRptNo LEFT OUTER JOIN ProdData.dbo.vOpenNonDupGroupIDs ON ShippingReportData.dbo.HeadShipped.Site = ProdData.dbo.vOpenNonDupGroupIDs.fSite AND ShippingReportData.dbo.HeadShipped.Barn = ProdData.dbo.vOpenNonDupGroupIDs.Barn LEFT OUTER JOIN Carcass2.dbo.KILSHEET ON Carcass2.dbo.KILSHEET.SHIP_REPORT = ShippingReportData.dbo.HeadShipped.ShipRptNo
WHERE (Carcass2.dbo.KILSHEET.KILSHEETID = @KSid)
END

Now, I am not sure if I have done this right, but I only want the insert to happen if the record count is 0(less than 1). If the record already exists, I want it to do nothing. Is this the proper method?

tmitch
Yak Posting Veteran

60 Posts

Posted - 2007-07-16 : 23:30:53

At first glance, I don't see any errors here. Are you experiencing a problem with this proc, or are you asking if there is a better way to do this?

Tim

rahulnadkarni
Starting Member

5 Posts

Posted - 2007-07-16 : 23:57:06

Check you first condition

SELECT Count(*) AS RecCount FROM Carcass2.dbo.t_GroupData
LEFT JOIN Carcass2.dbo.Kilsheet
ON Carcass2.dbo.t_GroupData.ksid = Carcass2.dbo.Kilsheet.kilsheetid
WHERE
Carcass2.dbo.Kilsheet.kilsheetid = @KSid AND Carcass2.dbo.t_GroupData.shiprptno = Carcass2.dbo.Kilsheet.ship_report

The left join might be causing the problem.

Rahul

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-17 : 01:56:30

[code]IF NOT EXISTS (
SELECT *
FROM Carcass2.dbo.t_GroupData AS gd
LEFT JOIN Carcass2.dbo.Kilsheet AS k ON k.kilsheetid = gd.ksid
AND k.ship_report = gd.shiprptno
AND k.kilsheetid = @KSid
)
INSERT Carcass2.dbo.t_groupdata
(
ShipRptNo,
ksID,
site,
barn,
id,
head,
weight
)
SELECT hs.ShipRptNo,
k.KILSHEETID,
hs.Site,
hs.Barn,
v.LotNum,
hs.Head,
sr.LiveWt AS weight
FROM ShippingReportData.dbo.HeadShipped AS hs
LEFT JOIN ShippingReportData.dbo.[Shipping Rpt] AS sr ON sr.ShipRptNo = hs.ShipRptNo
LEFT JOIN ProdData.dbo.vOpenNonDupGroupIDs AS v ON v.fSite = hs.Site
AND v.Barn = hs.Barn
LEFT JOIN Carcass2.dbo.KILSHEET AS k ON hs.ShipRptNo = k.SHIP_REPORT
AND k.KILSHEETID = @KSid[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources