March 28, 2012 at 3:09 pm
Hi Experts.
Ill try my best to explain this
I have a table called OrderHandsets which has a column call ConDate which holds a date and time of when a Mobile phone was connected to the network.
What im trying to do is pass in a OrderID to a stored procedure which then gets all records that correspond to that OrderID so it could return one record, or two, three, four etc
But this is the difficult part i need to pass in the orderID get all records that belong to that Order and then check if the ConDate on each record has a value in it, if it does then return true, else return false...im not strong in SQL as im a front end developer so im hardly ever in SQL... this is what i have so far i no its not much but like i mentioned im not a SQL developer i probably only go in there once a blue moon... so any help would be highly appreciated.
CREATE PROCEDURE sp_CheckConDate
@spOrderID int = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Select ConDate, OrderID from OrderHandset
--This is were i need to loop through and check all the conDates
--P.s conDate stands for ConnectionDate someone designed the DB that way.
Where OrderID = @spOrderID
END
GO
Hopefully speak to someone soon.
Thank you.
March 28, 2012 at 3:24 pm
Not to sure what you are looking for here. It would help if you could post the DDL (CREATE TABLE statement) for the table, some sample data (as a series of INSERT INTO statements) to populate the table (about 6 to 10 rows), the expected results based on the sample data.
Please read the first article I reference below in my signature block for information on how to post this information.
March 28, 2012 at 3:24 pm
Am I correct in saying you want to return true (1) if all the records for that order have a non-null condate, otherwise false (0)?
If so:
CREATE PROCEDURE sp_CheckConDate (
@spOrderID int
)
AS
BEGIN
DECLARE @Result BIT = 1; -- default to success.
IF EXISTS (SELECT 1 FROM dbo.OrderHandset WHERE OrderID = @spOrderID AND ConDate IS NULL) -- there's at least one null condate for that order
SET @Result = 0;
SELECT @spOrderID AS OrderID, @Result AS HasNoNullConDates
END
GO
p.s. Don't name stored procedures sp_. That's a naming that means system procedure, and procedures you write are not system procedures. There's different behaviour around name resolution for stored procs that start sp_ that could cause you problems in the future.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 28, 2012 at 3:35 pm
Hi GilaMonster
Thats exactly what i want,
So if a order has 5 items attached to it i need to check the condate on each for a value if any of the 5 is Null then i need to return false,
Thank you for your example, and the information in regards to naming a Stored Proc i will rename it
Appreciate your time on this.
March 28, 2012 at 5:21 pm
My script should do exactly that (0, not false, SQL doesn't have a boolean datatype)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply