IF EXISTS QUESTION PLEASE

Question

Post reply

IF EXISTS QUESTION PLEASE

BuilderBits

SSCertifiable

Points: 6071
More actions
November 29, 2005 at 10:01 am

#89978

ALTER PROCEDURE
Admin_SelectUser_OrdersAddress_ForDataGrid

@Admin_Email
Varchar (200), /*Admin Check */
@Admin_FName
Varchar (30), /*Admin Check */
@Admin_UserType
Varchar (20), /*Admin Check */
@user-id
int,
@OrderID
int,
@FirstName
Varchar (30),
@LastName
Varchar (30),
@Email
Varchar (200)
As
BEGIN
IF EXISTS
(SELECT Email, FirstName, UserType FROM Users
WHERE
Email = @Admin_Email AND
FirstName = @Admin_FName
AND
UserType = @Admin_UserType)
BEGIN
IF EXISTS SELECT
Orders.OrderID, OrderAddress.FirstName, OrderAddress.LastName, OrderAddress.Address1, OrderAddress.Address2, OrderAddress.City,
OrderAddress.Zip, OrderAddress.Phone, State.StateName, Country.CountryName
FROM
Users INNER JOIN
Orders
ON Users.UserID = Orders.UserID INNER JOIN
OrderAddress
ON Orders.OrderID = OrderAddress.OrderID INNER JOIN
State
ON Users.StateCD = State.StateCD AND OrderAddress.StateCD = State.StateCD INNER JOIN
Country
ON Users.CountryCD = Country.CountryCD AND OrderAddress.CountryCD = Country.CountryCD
WHERE
Users.UserID =@UserID AND Users.Email = @Email AND Users.LastName = @LastName AND Users.FirstName = @FirstName AND Orders.OrderID = @OrderID
END
ELSE
BEGIN

****** THIS IS WHERE I WOULD LIKE TO SEND THE Predefinded values back to the client If the If Not exists from above.
========================================================
Example... like for here send the orderid back as 100 ( would like to do this in quite a few different situations... so i am going to leave 100 for my own personal error flag) ....then send the orderaddress back to the client as 'This user has no previous orders'....ect....ect...
Orders.OrderID, OrderAddress.FirstName, OrderAddress.LastName, OrderAddress.Address1, OrderAddress.Address2, OrderAddress.City,
OrderAddress.Zip, OrderAddress.Phone, State.StateName, Country.CountryName

END
end

**Reason for doing this is that i am cutting down on checking for nulls in my vb script... and would like to just send something more understandable back to the client... and for safeness...
Dam again!

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

Erik - what's your question..?!?!

**ASCII stupid question, get a stupid ANSI !!!**

BuilderBits SSCertifiable Points: 6071 More actions · Answer 2

oops.

i would like to send data back to the client regardsless if the client has a order in the database.

I have a complex set of nested datagrids and datlist that display a users information to employees or admins....so if the admin or employee looks up a users information and trys to pull of their ordre history ,,, and the user does not have a order histrory....this is where i want to send data back to the client,,, and not send null values back... because if i send null values back i will have alot of code to write.

==================================================================

ELSE

BEGIN

****** THIS IS WHERE I WOULD LIKE TO SEND THE Predefinded values back to the client If the If Not exists from above.

========================================================

Example... like for here send the orderid back as 100 ( would like to do this in quite a few different situations... so i am going to leave 100 for my own personal error flag) ....then send the orderaddress back to the client as 'This user has no previous orders'....ect....ect...

Orders.OrderID, OrderAddress.FirstName, OrderAddress.LastName, OrderAddress.Address1, OrderAddress.Address2, OrderAddress.City,

OrderAddress.Zip, OrderAddress.Phone, State.StateName, Country.CountryName

END

end

=================================================

So if my user has no order history and does not exists... i want to send something like this back to the client..'

orders.orderid = 100 , orders.ordershipaddress ='This user has no ship address', State.StateName='User has no order' ECT.......ect....

Dam again!

BuilderBits SSCertifiable Points: 6071 More actions · Answer 3

Maybe this needs to be a select... like select orders.orders as orderid=100.. i am not sure i have never done one like this

Dam again!

sushila SSC-Dedicated Points: 35293 More actions · Answer 4

Still not sure if I've understood correctly, but if I have...why don't you just set defaults for your orderID, shipAddress etc...check @@rowcount - if equal to 0, then send select @orderID, @shipAddress etc...?!?!

**ASCII stupid question, get a stupid ANSI !!!**

BuilderBits SSCertifiable Points: 6071 More actions · Answer 5

I do not want to break the o'll arm... but a little demo.... would be a great thing...

?

erik

Dam again!

sushila SSC-Dedicated Points: 35293 More actions · Answer 6

Here's a demo using Northwind...

CREATE PROCEDURE DemoForErik
@CategoryID Int = 100,  -- default values
@CategoryName VarChar(50) = 'Erik Little'
AS

IF EXISTS(SELECT * FROM Northwind.dbo.Categories WHERE CategoryID = 10)
   BEGIN
SELECT CategoryID, CategoryName FROM Northwind.dbo.Categories WHERE CategoryID = 10
   END
ELSE
   BEGIN
SELECT @CategoryID , @CategoryName
   END

EXEC DemoForErik

- btw - who're you calling "o'll"

**ASCII stupid question, get a stupid ANSI !!!**

BuilderBits SSCertifiable Points: 6071 More actions · Answer 7

ha ha....

Thanks.!! this well help out alot!!

pappy,,,,, jj

really,,, thanks...

Dam again!