Incorrect Value Returned From Stored Procedure

Sep 26, 2006

I have an asp.net 1.1 website that uses sql server 2000 and vb.
I have a bit of a dilema, when I run a stored procedure in a webpage it returns the wrong value, but if I run it
in the query analyzer the correct value is returned. Dim orderHistory As nlb.OrdersDB = New nlb.OrdersDB

' Obtain Order ID from QueryString
Dim OrderID As Integer = CInt(Request.Params("ID"))

' Get the customer ID too
Dim myNewCustomerId As Integer = 0
myNewCustomerId = orderHistory.GetOrderCustomer(OrderID)



Public Function GetOrderCustomer(ByVal orderID As Integer) As Integer

' Create Instance of Connection and Command Object
Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myCommand As SqlCommand = New SqlCommand("nlbsp_OrdersCustomerID", myConnection)

' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure

' Add Parameters to SPROC
Dim parameterOrderID As New SqlParameter("@order_id", SqlDbType.Int, 4)
parameterOrderID.Value = orderID
myCommand.Parameters.Add(parameterOrderID)

Dim parameterOrderCustID As New SqlParameter("@customer_id", SqlDbType.Int, 4)
parameterOrderCustID.Value = ParameterDirection.Output
myCommand.Parameters.Add(parameterOrderCustID)

'Open the connection and execute the Command
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

' Return the customer_id (obtained as out paramter of SPROC)

If parameterOrderCustID.Value <> 0 Then
Return CInt(parameterOrderCustID.Value)
Else
Return 0
End If

End Function


the stored procdure is
CREATE PROCEDURE [dbo].[nlbsp_OrdersCustomerID]
(
@order_id int,
@customer_id int OUTPUT
)
AS

/* Return the customer_id from the Orders. */
SELECT
@customer_id = customer_id

FROM
nlb_Orders

WHERE
order_id = @order_id
GO


 I know a particular order_id returns a value of 1. But when I run it in the webpage it always comes back as 2.
 
Any ideas would be appreciated
 
Thanks
Pete

View 1 Replies


ADVERTISEMENT

How To Get The Returned Value From A Stored Procedure?

Sep 26, 2007

Hi,
I use a strong-typed DataSet to build the data access tier of my application, and the IDE generates most of code for me. However I can't find a way to get the value returned by a stored procedure(The stored procedure seems like 'RETURN 0').I noticed that a @RETURN_VALUE parameter is added automatically for each query method, but I don't know how to read the value.
Anybody could help me with the problem? Thanks.

View 2 Replies View Related

Use Resultset Returned From A Stored Procedure In Another Stored Procedure

Nov 15, 2006

I have a store procedure (e.g. sp_FetchOpenItems) in which I would like to call an existing stored procedure (e.g. sp_FetchAnalysts). The stored proc, sp_FetchAnalysts returns a resultset of all analysts in the system.
I would like to call sp_FetchAnalysts from within sp_FetchOpenItems and insert the resultset from sp_FetchAnalysts into a local temporary table. Is this possible?
 Thanks,
Kevin

View 3 Replies View Related

Stored Procedure - Too Many Rows Returned

Jan 12, 2007

The following query only brings back too much data in the table - there are duplicates and the lab it shows is wrong.
The problem is:  
AND a.calLab = f.ID
in the Where statement.
Some equipment does not need calibration, so, a.calDueDate = 0 AND a.calLab would be NULL or 0.
tblLabs ID field has 1 - 18 (Labs) - no 0 and of course no null
I need to get the rest of the data.
Any suggestions?
Thanks.....
Zath
 
SELECT a.assignedID,
b.Manufacturer,
a.modelNumber,
a.serialNumber,
e.equipmentType,
a.Description,
c.Location,
d.Status,
a.modifiedDate,
a.modifiedBy,
a.Notes,
a.Picture,
f.LabName,
a.calibrationRequired,
a.calDate,
a.CalDueDate,
a.assetNumber,
a.ID
FROM tblEquipments a,
tblManufacturers b,
tblLocation c,
tblStatus d,
tblEquipment_Type e,
tblLabs f
WHERE a.manufacturer = b.manufacturerID
AND a.location = c.locationID
AND a.Status = d.statusID
AND a.EquipmentType = e.ID
AND (a.calLab = f.ID or a.calLab Is NULL or a.calLab = 0)   
   ORDER BY a.ID
 
 

View 1 Replies View Related

Stored Procedure && Returned Values

Dec 23, 2007

I have a stored procedure that selects * from my table, and it seems to be working fine:
 USE myDB
GO
IF OBJECT_ID ( 'dbo.GetAll', 'P') IS NOT NULL
DROP PROCEDURE GetAll
GO
CREATE PROCEDURE GetAll
AS
DECLARE ref_cur Cursor
FOR
SELECT * FROM myTable
Open ref_cur
FETCH NEXT FROM ref_cur
DEALLOCATE ref_cur
 
The problem is, I'm trying to create a DB class, and I'm not sure what Parameter settings I'm supposed to use for my returned values.  Can anyone help me finish this?public class dbGet_base
{
public dbGet_base()
{
_requestId = 0;
}

public dbGet_base(Int64 RequestId)
{
this._requestId = RequestId;
getDbValues(RequestId);
}
public void getDbValues(Int64 RequestId)
{
getDbValues(RequestId, "GetAll");
}
public void getDbValues(Int64 RequestId, string SP_Name)
{
using(SqlConnection Conn = new SqlConnection(ConfigurationManager.AppSettings["WSConnection"]))
using (SqlCommand Command = new SqlCommand(SP_Name, Conn))
{
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Add("@Request_Id", SqlDbType.Int).Value = RequestId;
Command.Parameters.Add(??
}

View 3 Replies View Related

Stored Procedure Works But Nothing Returned?

Mar 4, 2004

Hello,

I have the following stored prod in SQL server:

CREATE PROC spValidateUserIdAndPassword

@UserIdvarchar(50),
@Passwordvarchar(50)
AS
SELECT tblAdvertisers.UserID, tblAdvertisers.Password
FROM tblAdvertisers
WHERE ((tblAdvertisers.UserID = @UserId) AND (tblAdvertisers.Password = @Password))


I can run it in Query Analyzer and it returns one record as it should. I want it in ASP.NET to return the amount of rows that are effected, e.g that the login is correct.

The code I have is:


public bool ValidateUserIdAndPassword(string userId, string password)
{
sqlCommand.CommandType = CommandType.StoredProcedure;
//the name of the stored procedure
sqlCommand.CommandText = "spValidateUserIdAndPassword";

SqlParameter myParam;

//add the param's to the SP

//userId information for the user
myParam = new SqlParameter("@UserId", SqlDbType.VarChar, 50);
myParam.Value = CStr(userId);
sqlCommand.Parameters.Add(myParam);

//password information for the user
myParam = new SqlParameter("@Password", SqlDbType.VarChar, 50);
myParam.Value = CStr(password);
sqlCommand.Parameters.Add(myParam);

try
{
int rows = sqlCommand.ExecuteNonQuery();

if(rows == 1)
return true;
else
return false;

}
catch(System.Exception er)
{
//for design time error checking
return false;
}



This returns -1...

Please help me

Kind Regards


KitkatRobins :-)

View 3 Replies View Related

Trying To Retrieve A Value Returned By A Stored Procedure

Apr 30, 2006

I have a stored procedure that return either 1 or -1 : IF EXISTS( ) BEGIN return 1 ENDELSE BEGIN return -1 ENDAnd then I try to retrieve the returned value by doing this:int value = Convert.ToInt32(command.ExecuteScalar());But the value I get is always 0. Anyone see what is wrong?  

View 1 Replies View Related

Stored Procedure Syntax - Value Not Returned

Feb 5, 2004

I have the following stored procedure. The good thing is I finally got the syntax to the point where it doesn't blow up, but I was under the impression the "print" statement would print my result. In this case all I get is the statement ' 1 Row Affected ' which by the way is not the answer I was expecting.

Here's the code:
'
CREATE PROCEDURE createevents
AS

declare @myvariable varchar(700)

declare @myvar varchar(700)

begin
Select @myvariable = d.id
from table1 d, table2 dc where d.class = dc.class

If @myvariable > 0
begin
Select @myvar =dp. class
from table3 dp, table2 dc
where dp.class = dc.class

If @myvariable >= 1
begin
print @myvariable + (',') + @myvar
end
end

else
begin
print @myvariable + ('is empty')
end
end

'
Thanks in advance for any help.

View 5 Replies View Related

Can A Stored Procedure's Result Set Be Returned

Apr 4, 2004

Pls give some samples

View 3 Replies View Related

SQL 2012 :: Getting Returned Value Of A Stored Procedure?

Jun 2, 2015

I am running a sproc we have developed and I am getting a -6 as a returned value.what that actually means.

View 7 Replies View Related

Value That Is Returned If An Insert Is Not Performed From A SQL Stored Procedure.

Sep 14, 2007

I am using the following stored procedure to insert a value into the database. I am new to stored procedures so I need help. Basically this stored procedurewill only insert a value for "CustomerName" if it is not a duplicate value.
So I noticed in My C# when I call this stored procedure, a negative one "-1"is returned if the insert is not performed because of a duplicate value.
Is that correct? Should I be getting back a negative one "-1" ? You seeI thought that a Zero "0" would be returned if the insert was not performed.Not a negative one?
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO
CREATE PROCEDURE [dbo].[CustomerCreate](  @CustomerDescription nvarchar(100),  @CustomerName nvarchar(100),  @LastUpdateDate datetime,  @LastUpdateUser nvarchar(32),  @inserted_record smallint output)
AS
 if exists (select 1 from Customer where CustomerName = @CustomerName) BEGIN
    set @inserted_record = 0
 END    ELSE BEGIN INSERT INTO Customer (  CustomerDescription,  CustomerName,        Active,  LastUpdateDate,  LastUpdateUser ) VALUES (  @CustomerDescription,  @CustomerName,  @LastUpdateDate,  @LastUpdateUser ) END

View 5 Replies View Related

Reading Values Returned From A Stored Procedure

Nov 10, 2000

I am trying to use a stored procedure inside the scripter in a site server pipeline. Can anyone tell me how the scripter will read the the result which is a variable. The stored procedure is returning the right value when run in query analyzer but I don't know how to retrieve it inside the pipeline.

Thank you
JG

View 1 Replies View Related

Append Recordset Returned From A Stored Procedure

May 12, 2004

I have a recordset returned from a stored procedure executed in the form open event. Could this recordset append to the form's recordsource property in the form's open event in VB? if so, what's the syntax?

Private Sub Form_Open(Cancel As Integer)
dim ...

Set add_bag_results = Nothing
With add_bag_results
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdStoredProc
.CommandText = "spSampling_add_bag_results"
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
'.Parameters.Append .CreateParameter("@lotnum", adInteger, adParamInput, 4, rs_add_bag_results(1))
.Parameters.Append .CreateParameter("@lotnum", adInteger, adParamInput, 4, lot_n)
..
Set rs_add_bag_results = .Execute
End With

Me.RecordSource = rs_add_bag_results ?

Thanks!

View 2 Replies View Related

Convert Stored Procedure's Returned Output To Varchar From Int?

Jun 23, 2006

I have 1 files, one is .sql and another is stored procedure. SQL file will call the stored procedure by passing the variables setup in the SQL file. However, everything ran well except at the end, I try to get the return value from SP to my SQL file ... which will send notification email. But, I get the following msg ... I am not sure how to fix this ... help!!! :(
Syntax error converting the varchar value 'ABC' to a column of data type int.
SQL file
======================
DECLARE @S AS VARCHAR(1000)
EXEC @S = PDT.DBO.SP_RPT     'ABC'
SELECT CONTENT = @S  -- this is the value I am trying to pass as content of the email
EXEC PRODUCTION.DBO.SENDEMAIL 'xxx@hotmail.com', 'Notification', @S
======================
Stored Procedure
======================
CREATE procedure sp_RPT( @array varchar(1000) ) AS
DECLARE @content AS VARCHAR(2000)
SET @content = 'RPT: ' + @array + ' loaded successfully '
SET @array = 'ABC'RETURN CONVERT(VARCHAR(1000),@array)
GO

View 2 Replies View Related

Stored Procedure/results Returned Double Problem

Oct 18, 2006

Stored Procedure ProblemThis is probably a simple problem but i would appreciate some help.I have a stored procedure that takes the order date time plus other information fromvarious tables but the information is being returned double:ie 4 rows are being returned instead of two. Can anyone see where i am going wrong?Many thanksMartinThis is the stored procedureALTER PROCEDURE dbo.SP_RetrieveOrdersASSELECT distinct OD.DateCreated, O.OrderID,O.UserID,O.OrderTotal,O.Sent,O.Delivered,O.Paid,C.CustomerNameFROM Orders As O,Customers As C,OrderDetails as ODWHERE O.UserID=C.UserID And O.OrderTotal  >0 RETURNThese are the results that are returnedDateCreated             OrderID     UserID                               OrderTotal       Sent   Delivered Paid   CustomerName               ----------------------- ----------- ------------------------------------ ---------------- ------ --------- ------ --------------- 18/10/2006 14:49:00     41          7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 500              <NULL> <NULL>    <NULL> bill                     18/10/2006 14:49:00     42          7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 590              <NULL> <NULL>    <NULL> bill                     18/10/2006 15:05:00     41          7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 500              <NULL> <NULL>    <NULL> bill                      18/10/2006 15:05:00     42          7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 590              <NULL> <NULL>    <NULL> bill                     No rows affected.(4 row(s) returned)If I leave OD.DateCreated ie use ALTER PROCEDURE dbo.SP_RetrieveOrdersASSELECT distinct O.OrderID,O.UserID,O.OrderTotal,O.Sent,O.Delivered,O.Paid,C.CustomerNameFROM Orders As O,Customers As C,OrderDetails as ODWHERE O.UserID=C.UserID And O.OrderTotal  >0 RETURNthen there is no problem. I get:41          7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 500              <NULL> <NULL>    <NULL> bill                     42          7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 590              <NULL> <NULL>    <NULL> bill                     No rows affected.(2 row(s) returned) 

View 2 Replies View Related

Running Returned Results Through A Stored Procedure All In One Trip

Nov 6, 2006

Hi,
I need to write a select query that will run all returned results through a separate stored procedure before returning them to me.
Something like....
SELECT TOP 10 userid,url,first name FROM USERS ORDER by NEWID()
......and run all the selected userids through a stored procedure like.....
CREATE PROCEDURE AddUserToLog (@UserID int ) AS INSERT INTO SelectedUsers (UserID,SelectedOn) VALUES (@UserID,getdate())
 Can anyone help me to get these working togethsr in the same qurey?
 
Thanks

View 7 Replies View Related

Limit The Number Of Records Returned In Stored Procedure.

Aug 17, 2007

In my ASP page, when I select an option from the drop down list, it has to get the records from the database stored procedure. There are around 60,000 records to be fetched. It throws an exception when I select this option. I think the application times out due to the large number of records. Could some tell me how to limit the number of rows to be returned to avoid this problem. Thanks.
 Query
SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))' + @Criteria + ' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000.dbo.payhistory ph1 LEFT JOIN Collect2000.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT1 = 1))'

View 2 Replies View Related

Naming The Result Sets Returned By Stored Procedure

Jul 6, 2004

Greetings

I have a SQL Server stored procedure that performs several queries and therefore returns several "result sets" at any one time.

Wiring it up via ADO.NET I populate a DataSet with a number of items in the Tables collection - which is great - and I can give each item a name for identification purposes once the DataSet is populated.

But I'd like to know if there is some way I can set the names of each result set *within the text of the stored procedure*, i.e. before the DataSet gets populated.

Any help greatly appreciated.
Stuart

View 14 Replies View Related

How To Suppress Result Sets Returned From A Stored Procedure?

Aug 28, 2006

I have a novice question. How does one suppress result sets returned from a stored procedure?

I have created a procedure which makes use of multiple stored procedures . The purpose of this procedure (lets call it procA), is to count the rows returned from other procedures. The €œOther€? procedures will return rows having an unknown number of columns. I would like to limit any changes which may be needed to be made to the €œOther€? procs.

Once procA has collected all of the information (@@rowcount) from the inner procedures, then it will return a result set having several columns €“ mainly the subProcedure name and number of rows returned.

The purpose of procA is to query several subsystems and identify which ones need attention.

Cursor While Loop
exec @ProcName @ObjectName,@userID,@syncDate
set @recs = @@rowcount;


My c# program calls the sp as follows:

cmd = DataUtility.GetSQLCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "FetchAdminData";
cmd.Parameters.AddWithValue("@userID", userAlias);
cmd.Parameters.AddWithValue("@adminDate",userDate);
reader = cmd.ExecuteReader();

I do not wish to iterate over each resultSet when I am only interested in the last one. Any suggestions?



View 2 Replies View Related

Stored Procedure Format Incorrect - Inserting To Two Tables

Dec 9, 2007

Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

View 1 Replies View Related

SCOPE_IDENITY() - Incorrect Value (Returned)

Dec 21, 2007

There are two tables Table_1:ProductID  (Identity Increment) ProductDescription (nvarchar) Table_2:ProductID (int)I have a sql statement and it's like this.  This SQL is probably incorrect - but hopefully gives you an idea of what I am trying to do:   DECLARE @getTheNewProductID intINSERT INTO Table_1 (ProductDescription) VALUES ('SomeValue') ; SET @getTheNewProductID = SELECT SCOPE_IDENTITY() INSERT INTO Table_2 (ProductID) VALUES (@getTheNewProductID)  What I need is: when inserting into Table_1 to get the Exact New Product Id from it that occurs from the identity increment - then insert that exact same product ID into table_2The problem is it is returning incorrect values on the scope identity. Such as values from two transaction ago.How do you do this? I did try using @@identity which may have stuffed things up?? Thanks for your help

View 1 Replies View Related

Incorrect Value Returned To Page

Apr 21, 2008

 Hi,I have a shopping cart and before each product is added to the cart it checks to see if the stock control is turned on if so then checks the quantity available.My problem is that when i run the query  select [dbStatus] from InventoryControl where [ID]=1 to view the status it is returning the incorrect bit value for dbStatus.If "dbStatus" is 0 in the database it returns 0 which is correct, but if the "dbStatus" is 1 in the db it returns -1!! If i run the query in query analyser the correct values are returned. Any ideas???Thanks in Advance 

View 2 Replies View Related

T-SQL Error In Creating A Stored Procedure That Has Three Parameters: Incorrect Syntax Near 'WHERE'

Feb 17, 2008

Hi all,

I copied the the following code from a book to the query editor of my SQL Server Management Studio Express (SSMSE):
///--MuCh14spInvTotal3.sql--///
USE AP --AP Database is installed in the SSMSE--
GO
CREATE PROC spInvTotal3
@InvTotal money OUTPUT,
@DateVar smalldatetime = NULL,
@VendorVar varchar(40) = '%'
AS

IF @DateVar IS NULL
SELECT @DateVar = MIN(InvoiceDate)

SELECT @InvTotal = SUM(InvoiceTotal)
FROM Invoices JOIN Vendors
WHERE (InvoiceDate >= @DateVar) AND
(VendorName LIKE @VendorVar)
GO
///////////////////////////////////////////////////////////////
Then I executed it and I got the following error:
Msg 156, Level 15, State 1, Procedure spInvTotal3, Line 12
Incorrect syntax near the keyword 'WHERE'.
I do not know what wrong with it and how to correct this problem.

Please help and advise.

Thanks,
Scott Chang

View 18 Replies View Related

Receiving Error 156 - Incorrect Syntax When Compiling Stored Procedure

May 14, 2008

The following query works fine in query analyzer, but when I add it to my stored procedure I receive an error 156. How do I work around this?

select distinct(dateposted)
from billingprocedures bp1,
billingprocedureordercomponentvalues bpocv,
ordercomponentvalues ocv
where bp1.billingid = @billingid
and bp1.procedureid = bpocv.billingprocedureid
and bpocv.ordercomponentvalueid = ocv.ordercomponentvalueid

Thanks,
Bryan

View 12 Replies View Related

SQL 2012 :: SSRS - Force Report To Use Columns Returned From Stored Procedure?

Jul 10, 2015

I have a stored procedure which returns a result set as follows:

(Headers)Total,WV1,WV2,WV3,WV4,WV5.....
(Example data) "Some total name",1,2,3,4,5.....

The WV1, WV2, WV3 column names will be different depending on parameters passed to the stored procedure. In other words, the column names or number of columns aren't fixed (apart from "Total").

What I would like to be able to do is to just force SSRS to use the column headers supplied by the stored procedure as the column names in the report.

View 9 Replies View Related

SUM And JOIN And Possibly GROUP BY - Incorrect Value Returned By SUM

Mar 23, 2006

It's me again :)

So; if you read my earlier thread here (http://www.dbforums.com/showthread.php?t=1214353), you'll know that I'm trying to build stored procedures to deal with ticketing queries, and that it's all getting a bit complicated. I have, however, made a bit of progress and now have the following working:


CREATE PROCEDURE [dbo].[getAvailableTickets]
@eventId INT,
@standId INT,
@admissionDateId INT,
@concessionId INT,
@userId INT

AS

DECLARE @startyear DATETIME
DECLARE @endyear DATETIME
SELECT @startyear=CONVERT(datetime, '2006/01/01')
SELECT @endyear=CONVERT(datetime, '2006/12/31')


SELECT
[tblTickets].[id] AS ticketId,
[tblEvents].[id] AS eventId,
[tblStands].[id] AS standId,
[tblAdmissionDates].[id] AS admitDateId,
[tblEvents].[event_name],
[tblStands].[stand_name],
[tblTicketConcessions].[concession_name],
[tblMemberships].[membership_name],
[tblAdmissionDates].[admission_start_date],
[tblAdmissionDates].[admission_end_date],
[tblBookingMinQuantities]. AS minBookingQuantity,
[tblBookingMaxQuantities].[booking_quantity] AS maxBookingQuantity,
MIN([tblQuotas].[quota]) AS Quota,
[B]SUM([tblBasket].[ticket_quantity]) AS History,
[tblTickets].[price],
[tblTickets].[availability]

FROM [tblTickets]
LEFT JOIN [tblEvents]ON [tblEvents].[id] = [tblTickets].[event_id]
LEFT JOIN [tblStands]ON [tblStands].[id] = [tblTickets].[stand_id]
LEFT JOIN [tblBookingDates]ON [tblBookingDates].[id] = [tblTickets].
LEFT JOIN [tblTicketConcessions]ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id]
LEFT JOIN [tblBookingQuantities] AS tblBookingMinQuantities ON [tblBookingMinQuantities].[id] = [tblTickets].[booking_min_quantity_id]
LEFT JOIN [tblBookingQuantities] AS tblBookingMaxQuantitiesON [tblBookingMaxQuantities].[id] = [tblTickets].[booking_max_quantity_id]
LEFT JOIN [tblAdmissionDates]ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id]
LEFT JOIN [tblMemberships]ON [tblMemberships].[id] = [tblTickets].[membership_id]
LEFT JOIN [tblQuotas]ON
([tblQuotas].[event_id] = [tblTickets].[event_id] OR [tblQuotas].[event_id] IS NULL) AND
([tblQuotas].[stand_id] = [tblTickets].[stand_id] OR [tblQuotas].[stand_id] IS NULL) AND
([tblQuotas].[admission_date_id] = [tblTickets].[admission_date_id] OR [tblQuotas].[admission_date_id] IS NULL) AND
([tblQuotas].[concession_id] = [tblTickets].[ticket_concession_id] OR [tblQuotas].[concession_id] IS NULL) AND
([tblQuotas].[membership_id] = [tblTickets].[membership_id] OR [tblQuotas].[membership_id] IS NULL) AND
([tblQuotas].[ticket_id] = [tblTickets].[id] OR [tblQuotas].[ticket_id] IS NULL)
[B]LEFT JOIN [tblBasket] ON [tblBasket].[ticket_id] = [tblTickets].[id]
LEFT JOIN [tblOrders] ON [tblOrders].[id] = [tblBasket].[order_id]

WHERE 1=1
AND ([tblTickets].[ticket_open] = 1)
AND (([tblEvents].[id] = @eventId OR @eventId = 0)AND ([tblEvents].[event_open] = 1))
AND (([tblStands].[id] = @standId OR @standId = 0)AND ([tblStands].[stand_open] = 1))
AND (([tblAdmissionDates].[id] = @admissionDateId OR @admissionDateId = 0)AND ([tblAdmissionDates].[date_open] = 1))
AND ([tblTicketConcessions].[id] = @concessionId OR @concessionId = 0)
AND ((getdate() BETWEEN [tblBookingDates]. AND [tblBookingDates].[booking_end_date]) OR ([tblBookingDates].[booking_start_date] IS NULL AND [tblBookingDates].[booking_end_date] IS NULL))
AND (([tblMemberships].[id] IN (SELECT [membership_id] FROM [tblUsers_Memberships] WHERE [user_id]=@userId)) OR [tblMemberships].[id] IS NULL)
[B]AND ([tblOrders].[user_id] = @userId OR @userId=0)

GROUP BY
[tblTickets].[id],
[tblEvents].[id],
[tblStands].[id],
[tblAdmissionDates].[id],
[tblEvents].[event_name],
[tblStands].[stand_name],
[tblTicketConcessions].[concession_name],
[tblMemberships].[membership_name],
[tblAdmissionDates].[admission_start_date],
[tblAdmissionDates].[admission_end_date],
[tblBookingMinQuantities].[booking_quantity],
[tblBookingMaxQuantities].[booking_quantity],
[tblTickets].[price],
[tblTickets].[availability]
GO



Except... there's two problems with it. One is that it only returns tickets that you've already bought, the other is that it doens't work out correctly how many of those tickets you've bought in past orders.

This is what's in tblBasket:


idticket_idquantityorder_iddate
152321/03/2006
262321/03/2006
3144421/03/2006
4154421/03/2006
551421/03/2006


Both the orders in there are for the same user id: "1". All the tickets in there are tied to event "2".

When I run Exec dbo.getAvailableTickets 2,0,0,0,1, it tells me it's found 6 of ticket 5, 4 of ticket 6 and 12 of ticket 14. And I can't for the life of me figure out how it's calculating it. Any ideas?

And how do i get it to return all tickets regardless of whether you've bought them previously or not?

View 4 Replies View Related

Can The OLE DB Command Transformation Support Multiple Values Returned By A Stored Procedure Or Is The Limit One Value Only?

Apr 10, 2007

I have no problem getting OLE DB Command transformations to support single returns by a procedure.

For example, exec name_of_procedure ?,?,? OUTPUT



However, I have a stored procedure which accepts 1 input and returns 5 outputs. This procedure works fine at the command line but when I try to incorporate it into a OLE DB Command I don't get the multiple values returned. There's no problem at all configuring the transform as it recognizes all input and output parameters. For some reason I just don't get values returned.



thanks

John

View 14 Replies View Related

Stored Procedure Returning Multiple Resultsset, Yet Reporting Services Only Uses The First Resultset Returned

Nov 9, 2007

Hi

I have written a stored procedure that returns 8 tables.

When I try to design a server report based on this stored procedure, reporting services only recognises the first table and not the other 7 tables.

I am using SQL Server 2005 and Visual Studio 2005.

Thank you in advance

Jav

View 4 Replies View Related

Heavily Fragmented Index - Incorrect Data Returned?

Dec 1, 2007

Hi everyone,
If I have a table with some indexes on the foriegn keys and these indexes are heavily fragmented (80%+), is it normal for queries to return incorrect results?

For example if I had a table called Customer( CustID, Name) and Orders (OrderID, CustID, Product, Date).
Lets say I have a non clustered index on CustID in Orders table, and the clustered indexes are Customer.CustID and Orders.OrderID


If the non clusterd index on Orders.CustID becomes heavily fragmented and I am querying the Orders table with TSQL "SELECT * FROM Orders where CustID = @CustID" I sometimes get missing data or incorrect results. In one case all orders for a particular year were missing, but if I queried using OderID they were returned. Rebuilding the index fixed the problem.

I know the index should be rebuilt or reorganized depending on the fragmentation but if one happened to become this fragmented should it start returning incorrect data?

- Using SQL Server Express 2005

View 3 Replies View Related

How Can I Assign Data Returned From A Stored Procedure Into The Return Table Of A Table Valued Function

Apr 18, 2007

Here is the scenario,
I have 2 stored procedures, SP1 and SP2

SP1 has the following code:

declare @tmp as varchar(300)
set @tmp = 'SELECT * FROM
OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'',
''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'

EXEC (@tmp)

SP2 has the following code:

SELECT *
FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)

Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used.
Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used.
Functions - My last resort is to use a table valued function as shown:

FUNCTION MyFunction
( )
RETURNS @retTable
(
@Field1 int,
@Field2 varchar(50)
)
AS
BEGIN
-- the problem here is that I need to call SP1 and assign it's resulting data into the
-- @retTable variable

-- this statement is incorrect, but it's meaning is my goal
INSERT @retTableSELECT *FROM SP1

RETURN
END

View 2 Replies View Related

ADO.NET 2-VB 2005 Express Form1:Printing Output Of Returned Data/Parameters From The Parameters Collection Of A Stored Procedure

Mar 12, 2008

Hi all,
From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:


Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlDbType

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")

Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)

testCMD.CommandType = CommandType.StoredProcedure

Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)

RetValue.Direction = ParameterDirection.ReturnValue

Dim auIDIN As SqlParameter = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11)

auIDIN.Direction = ParameterDirection.Input

Dim NumTitles As SqlParameter = testCMD.Parameters.Add("@numtitlesout", SqlDbType.Int)

NumTitles.Direction = ParameterDirection.Output

auIDIN.Value = "213-46-8915"

PubsConn.Open()

Dim myReader As SqlDataReader = testCMD.ExecuteReader()

Console.WriteLine("Book Titles for this Author:")

Do While myReader.Read

Console.WriteLine("{0}", myReader.GetString(2))

Loop

myReader.Close()

Console.WriteLine("Return Value: " & (RetValue.Value))

Console.WriteLine("Number of Records: " & (NumTitles.Value))

End Sub

End Class

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.

Thanks in advance,
Scott Chang

View 29 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') 
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). 
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
 

View 1 Replies View Related

If Exists Capture Value Returned From Stored Proc

Feb 26, 2006

I have a stored proc with a query which checks whether an identical
value is already in the database table. If so, it returns a value of 1.
How do I caputure this value in an asp.net page in order to display a
message accordingly? (using ASP.NET 1.1)

Currently my stored proc looks something like this (snippet only):
If Exists(
SELECT mydoc WHERE....
)
Return 1
Else
...INSERT INTO.... code here.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved