March 20, 2008 at 8:29 am
I have a stored procedure that (1)declares a table, (2)inserts data from a select statement,(3) uses the declared data as an inner join to update a table that was involved in the insert select, and (4)finally selects all records from the declared table. My issue is this: When (3) is not commented out in vb.net I get a null dataset, when it is comented out I get my records. When I run this query in the query analyzer I get records either way. Any ideas what I can do to still get my dataset after (3) update occurs?
ALTER PROCEDURE spRMGetEcardBatch
(
@vintCategoryNum VARCHAR(4),
@vvcrSeating VARCHAR(3),
@vvcrCategory VARCHAR(20)
) AS
DECLARE @tblEcards TABLE(
Lname VARCHAR(30),
Fname VARCHAR(30),
FullName VARCHAR(90),
Address VARCHAR(100),
City VARCHAR(20),
State VARCHAR(20),
Zip VARCHAR(12),
[Section] VARCHAR(20),
Row VARCHAR(4),
Seat VARCHAR(4),
Invoice_Num Varchar(12),
ScanID VARCHAR(50),
Barcode Varchar(50),
PackageName Varchar(50),
TP_ID VARCHAR(50))
DECLARE @decNewID DECIMAL, @intQuantity INTEGER
IF @vvcrSeating = 'GA'
BEGIN
INSERT @tblEcards
SELECT DISTINCT dbo.Accounts.Lname,
dbo.Accounts.Fname, dbo.Accounts.Fname + ' ' +dbo.Accounts.Lname As FullName, dbo.Accounts.Addr, dbo.Accounts.City,
dbo.Accounts.State, dbo.Accounts.Zip,
dbo.TicketPackageInstances.[Section], dbo.TicketPackageInstances.Row, dbo.TicketPackageInstances.Seat,
dbo.Invoice.Invoice_Num, dbo.TicketPackageInstances.Scan_ID, 'D' + Convert(Varchar, dbo.TicketPackageInstances.Scan_ID) As BarCode,
dbo.TicketPackages.Name As PackageName, dbo.TicketPackageInstances.TPI_ID
FROM dbo.InvoiceItems INNER JOIN
dbo.Invoice ON dbo.InvoiceItems.I_GUID = dbo.Invoice.I_GUID INNER JOIN
dbo.Accounts ON dbo.Invoice.UID = dbo.Accounts.UID INNER JOIN
dbo.Tickets ON dbo.Invoice.Invoice_Num = dbo.Tickets.Invoice_Num INNER JOIN
dbo.TicketPackageInstances INNER JOIN
dbo.TicketPackages ON dbo.TicketPackageInstances.TP_ID = dbo.TicketPackages.TP_ID ON
dbo.Tickets.TPI_ID = dbo.TicketPackageInstances.TPI_ID
WHERE (dbo.TicketPackages.Category_Num = @vintCategoryNum) and (dbo.TicketPackageInstances.[Section] = '') AND ((dbo.Invoice.AuthCode IS NOT NULL) OR (dbo.Invoice.Type = 'Receivable')) AND ((dbo.InvoiceItems.TicketType = 'E') OR (dbo.InvoiceItems.TicketType = 'M')) AND (dbo.TicketPackageInstances.Status <> 'V') AND (dbo.TicketPackageInstances.EcardPrintBatch is null)
ORDER BY dbo.Accounts.Lname, dbo.Accounts.Fname, dbo.TicketPackageInstances.[Section], dbo.TicketPackageInstances.Row, dbo.TicketPackageInstances.Seat
END
ELSE
IF @vvcrSeating = 'RES'
BEGIN
INSERT @tblEcards
SELECT DISTINCT dbo.Accounts.Lname, dbo.Accounts.Fname, dbo.Accounts.Fname + ' ' +dbo.Accounts.Lname As FullName, dbo.Accounts.Addr, dbo.Accounts.City,
dbo.Accounts.State, dbo.Accounts.Zip,
dbo.TicketPackageInstances.[Section], dbo.TicketPackageInstances.Row, dbo.TicketPackageInstances.Seat,
dbo.Invoice.Invoice_Num, dbo.TicketPackageInstances.Scan_ID, 'D' + Convert(Varchar, dbo.TicketPackageInstances.Scan_ID) As BarCode,
dbo.TicketPackages.Name As PackageName, dbo.TicketPackageInstances.TPI_ID
FROM dbo.InvoiceItems INNER JOIN
dbo.Invoice ON dbo.InvoiceItems.I_GUID = dbo.Invoice.I_GUID INNER JOIN
dbo.Accounts ON dbo.Invoice.UID = dbo.Accounts.UID INNER JOIN
dbo.Tickets ON dbo.Invoice.Invoice_Num = dbo.Tickets.Invoice_Num INNER JOIN
dbo.TicketPackageInstances INNER JOIN
dbo.TicketPackages ON dbo.TicketPackageInstances.TP_ID = dbo.TicketPackages.TP_ID ON
dbo.Tickets.TPI_ID = dbo.TicketPackageInstances.TPI_ID
WHERE (dbo.TicketPackages.Category_Num = @vintCategoryNum) and (dbo.TicketPackageInstances.[Section] <> '') AND ((dbo.Invoice.AuthCode IS NOT NULL) OR (dbo.Invoice.Type = 'Receivable')) AND ((dbo.InvoiceItems.TicketType = 'E') OR (dbo.InvoiceItems.TicketType = 'M')) AND (dbo.TicketPackageInstances.Status <> 'V') AND (dbo.TicketPackageInstances.EcardPrintBatch is null)
ORDER BY dbo.Accounts.Lname, dbo.Accounts.Fname, dbo.TicketPackageInstances.[Section], dbo.TicketPackageInstances.Row, dbo.TicketPackageInstances.Seat
END
SELECT @intQuantity = @@ROWCOUNT
[highlight=#ffff00]IF @intQuantity > 0
BEGIN
INSERT INTO ecard_batches (category, seating, quantity)
VALUES (@vvcrCategory, @vvcrSeating, @intQuantity)
SELECT @decNewID = @@IDENTITY
UPDATE TicketPackageInstances
SET EcardPrintBatch = @decNewID
FROM TicketPackageInstances T
INNER JOIN @tblEcards E on
E.ScanID = T.Scan_ID
END[/highlight]
SELECT * FROM @tblEcards E
March 20, 2008 at 12:40 pm
After changing the declared table to a temporary table I am still not getting any results in my asp.net dataset. This makes no sense. If the temporary or declared tables are already holding the data from the select insert, then why would it matter if one of the tables from the select insert changed for the temporary or declared table.. This makes no sense.
Please someone shed some light on this...
March 20, 2008 at 1:59 pm
I ended up creating a separate query for the update and rearranged by asp.net code to work with it. My beliefs about this, until someone changes it, is that temporary and declared tables are dependent upon their Insert Select child tables on the data they hold. At least this seems to be the case...
March 20, 2008 at 2:00 pm
jreed (3/20/2008)
When I run this query in the query analyzer I get records either way.
What happens if you run the stored procedure in Query analyzer, instead of the query?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 20, 2008 at 2:03 pm
It returns the record, yet when i run my app the dataset is null (until I remove the update).
March 20, 2008 at 2:35 pm
Try tracing it with Profiler.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply