|
check this link[^]
I Love SQL
|
|
|
|
|
I have 3 Tables (Person-1------1->Employee-1------*>Career)
an employee can have multiple Career. I created the sql statement below
to return some information on about 200 employees
##################################################
select p.FamilyName,
p.AddressLine1,
Career.CareerDescription
from Person as p
inner join Employee on Employee.EmployeeID =p.PersonID
inner join Career on Employee.UniqueIdentifier = Career.ParentIdentifier and Career.PrimaryPostYN = 'Y'
###############################################
The "Career.PrimaryPostYN = Y" indicates which Post is active for an employee with multiple Career.
So only one post should be active at any one time. All other should be set to "Career.PrimaryPostYN = N"
QUESTION:
If an employee has multipe Career and all his career entries is set to "Career.PrimaryPostYN = Y". In such a case i want to return the first case where "Career.PrimaryPostYN = Y"
How do i alter the above statement to do this while returning information for all other employee. I tried "top 1" but that only returned 1 record out of the 200?
|
|
|
|
|
ONeil Tomlinson wrote: If an employee has multipe Career and all his career entries is set to "Career.PrimaryPostYN = Y". In such a case i want to return the first case where "Career.PrimaryPostYN = Y"
The first thing you need to answer is: If the person has multiple rows where PrimaryPost is 'Y' and you want the "first", how do you define "first"?
Databases are set based systems. There is no concept of first. It may appear that way, but that is just a manifestation of the algorithms used to store and retrieve the data and the result order may change if the database engine decides on a different optimisation for a particular query.
|
|
|
|
|
Somthing like this is stored in the tables
Name : PrimaryPostYN : Post
-------------------------------------
john: Y : Electrical Engineer
John: Y : Engineer
Ben : N : Editor
Ben : Y : Senior Copywriter
Tom : Y : Developer
In this case there are two valid entries for "John". i want to display
the first entry when "John" is an "Electrical Engineer" or "Enginner" (Whichever one is return first in the record set) plus all the other Employees as well. How can i alter the Statement in my first post to do this?
|
|
|
|
|
ONeil Tomlinson wrote: In this case there are two valid entries for "John". i want to display
the first entry when "John" is an "Electrical Engineer" or "Enginner" (Whichever one is return first in the record set)
As I've mentioned before, there is no "first". What you see is an illusion. The key is the term "record set". A "set" has no order.
If you want "first" you have to artificially create it. How do you define first? You have to define an order for the result set.
|
|
|
|
|
Sorry my fault i thought Record set had an indexed order. Ok basically what I want is one entry (for employee john) to be returned (doesnt matter which one) plus all the other employees
|
|
|
|
|
If i want to display message when user enters NULL for some field ,when I call stored procedure from the database?
|
|
|
|
|
Master Mind'z wrote: If i want to display message when user enters NULL for some field ,when I call stored procedure from the database?
Are you asking if a stored procedure can open up a message box to display a message to the user?
If so, the answer is no. Well, I suppose you could if you hacked around a bit. However, it is not a good idea. SQL Server is a database engine, often it will reside on another machine and if you were to get it to open a message box, it would only do so on the machine in which the SQL Server was running - it would be absolutely useless to the person using your application as they wouldn't see it.
Finally, the suggestion that SQL Server generates the message box is betrays somewhat a poor design. You are attempting to put presentation layer functionality in the database layer.
|
|
|
|
|
Oh! I had heard that you can validate data in SQL stored procedure.
I thought this might be the way.
I always validate data at UI level only and take care that everything is validated before I call stored proc. Is this
|
|
|
|
|
Master Mind'z wrote: Oh! I had heard that you can validate data in SQL stored procedure.
Yes, you can. But what does that have to do with putting messages to the user?
Master Mind'z wrote: I always validate data at UI level only and take care that everything is validated before I call stored proc
And for security you should also validate in the stored proc too. What if someone finds a different route to your database that isn't through your application? If you don't validate in the database too your system is vulnerable.
Master Mind'z wrote: Is this
Is this what? You didn't complete the question.
|
|
|
|
|
Thank you for the right guidance.. and quick reply.
I wanted to ask, Is this correct way to validate?
Any ways, you have cleared my doubt.
As you said we can also validate in stored Proc as well. how to do that?
|
|
|
|
|
Master Mind'z wrote: As you said we can also validate in stored Proc as well. how to do that?
Just like you validate anything else. You use conditional statements.
IF @someValue > validRange
BEGIN
-- Put code here to handle invalid data.
-- e.g. Could RAISEERROR
-- Could return a value indicating an error
END
ELSE
BEGIN
-- The data is correct. Apply it to the database
-- e.g. INSERT/UPDATE/DELETE
END
|
|
|
|
|
Thank you Colin Angus Mackay!
|
|
|
|
|
I need a stored procedure example for validating Login page ....If someone could help i can do this assignment.......
SAJAN A PILLAI
ASP.NET,C#.NET Programmer
dWise Solutions and Services Pvt Ltd.
BANGALORE
"Winners don't do different things. They do things differently. ...
|
|
|
|
|
Hi
I understood from ur post,You want to SP for check username and password ,when you pass args. Is it rite?
If rite?
create Proc dbo.urtablename_User_Validation
(
@username char(10),
@pwd char(10)
)
AS
BEGIN
check ur select query with ur params.
END
i think this may be help to u.
|
|
|
|
|
Ravenet wrote: ur
Ravenet wrote: rite
Ravenet wrote: u
Could you write in proper English please. There are people on this forum for whom English is not their strongest language and being confronted by crap like this won't help them.
ur = your or you're
rite = right
u = you
|
|
|
|
|
Please read here[^].
sajan ss wrote: BANGALORE
I'm afraid that kind of says it all.
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
Hi,
I need some help to clarify some confusion I have. If I access some data from the db and then create objects which hold the data--for example, access the customer table and create a customer object--am I using Connected technique or Disconnected? My understanding is I am using disconnected, however my teachers disagree.
I think to use connected technique I will query the db and keep connection open and every time a change is made it is made directly in db.
In other words, if the changes are made anywhere except the data storage source--the database in this case--then it is disconnected.
Furthermore, when you bind controls to the db, is it using connected or disconnected?
Please write a detailed answer and you can assume I am well familiar with ADO.NET.
CodingYoshi
|
|
|
|
|
CodingYoshi wrote: keep connection open
It is best practice to open the database only when you need to. Open it, do what you need, then close it. If you keep open persistently, then you can run into problems having too many connection pools...
"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon
|
|
|
|
|
Thanks but that is not the answer to my question.
|
|
|
|
|
Whether you're disconnected or connected, you'll obviously need to access the data to create your object. The difference lies in what you do next.
If you leave the database connection open and and write changes to the object back to the database immediately then you are working in a connected state. If you close the database once you've created your object, do some processing with it and then re-establish a connection to the database to write the changes back then you're disconnected.
The answer to your question about binding will be answered in the way you've implemented your system; as you should know, working in a connected state is generally considered bad practice (though there are cases where it is required). In general, if you can get away with only connecting to the database at the start and end of an operation you should probably do that.
It definitely isn't definatley
|
|
|
|
|
Good clear answer! Your answer goes in agreement with most texts I have read but the teachers think otherwise. This proves the teachers are wrong.
Thanks
|
|
|
|
|
Hi All,
I am trying to display a list of databases in SQL Server 2005. When I used to use MySQL I used a SHOW DATABASE which returned a list of databases.
With SQL Server 2005 this command is not accepted. Can I perform the same list in SQL Server ?
Fritzables.
|
|
|
|
|
exec sys.sp_databases is a stored procedure that will list all the databases for you...
"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
