|
Hi all,
I am trying to write a procedure which takes 1 parameter as @StudentID and returns Student Information for the given StudentId Parameter form Student and StudentDetail table.
Now i want that if parameter is not passed(i.e NULL) then procedure should return all students information,but if StudentId passed then for that student only...
I am trying this by using COALESCE function in sql server 2005.
Following is DDL for my procedure.
CREATE PROCEDURE [dbo].[GetStudentById]
@StudentId AS NUMERIC
AS
BEGIN
SELECT
S.StudentId,
S.StudentFName,
S.StudentMName,
S.StudentLName,
S.Address,
SD.StudentClassId
FROM
[dbo].Student S
JOIN StudentDetail SD ON S.StudentId = SD.StudentId
WHERE
S.StudentId = COALESCE(@StudentId,NULL)
END
But not getting desired result ..When i am passing NULL 0 rows are returned.
please help me out...
Thank & Regards
Dipak Thesiya
dipak
|
|
|
|
|
Your coalesce is wrong. Try this
WHERE S.StudentId = COALESCE(@StudentId,S.StudentId)
what you were doing is saying when (@StudentId use null, what you really wanted to say was when (@StudentId is null don't compare - which effectively means compare to the column value.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Thanks BOB....its working fine....and ur description is very clear..i mean short and sweet...
thanks.... 
dipak
|
|
|
|
|
No problem, pleased to help
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
I use the following
where (IsNull(@StudentID,0) = 0 OR StudentID = @StudentID)
and have done for years, I wonder if there is a benifit to using COALESCE. It is certainly more elegant than using the OR method.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
For years I used the same sort of thing, then I went to
where IsNull(@StudentID,StudentID ) = StudentID
but apparently COALESCE is preferable to isnull - not sure why in this instance. Its certainly good where you have several possible values, such as
select coalesce(val1,val2,val3) as mainvalue
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
hi,
I am using .NET to query indexing server (search engine for our Intranet)
If the user searches for "the" or "a" etc they will get an error page stating - The query contained only ignored words.
What is the best way to catch this and deliver something meaningful back to the page.
|
|
|
|
|
There is need to acquire a potentially large images (250 KB - 2.5 MB) from the client computer, now this has to be sent over the wire to the central DB. Question is, would it be advantageous to say, insert it into the local DB (on the client) and then have the local DB then post it to the central DB? This way, I should be able to maintain program response time fairly snappy. On the other hand, I could pipe it straight to the central DB asynchronously or on a separate thread. What do you suggest?
If the post was helpful, please vote, eh!
Current activities:
Book: Devils by Fyodor Dostoyevsky
Project: Hospital Automation, final stage
Learning: Image analysis, LINQ
Now and forever, defiant to the end.
What is Multiple Sclerosis[ ^]?
|
|
|
|
|
I would use the second thread method, having a local database that is communicating with the remote server implies WAY too many fracture points.
|
|
|
|
|
I was thinking that last night.
Mycroft Holmes wrote: having a local database that is communicating with the remote server implies WAY too many fracture points.
Why fracture points? I see it as a second, local copy/backup if you will. I'd love to hear your thoughts.
If the post was helpful, please vote, eh!
Current activities:
Book: Devils by Fyodor Dostoyevsky
Project: Hospital Automation, final stage
Learning: Image analysis, LINQ
Now and forever, defiant to the end.
What is Multiple Sclerosis[ ^]?
|
|
|
|
|
I see something like a sql express copy locally where you write the graphic to it. Then you need to have the local DB copy/replicate to the server. How were you going to get the local database to move the record to the server.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: I see something like a sql express copy locally where you write the graphic to it. Then you need to have the local DB copy/replicate to the server.
That's exactly what I was thinking of.
Mycroft Holmes wrote: How were you going to get the local database to move the record to the server.
Most likely a trigger. Once insertion is complete, then the DBs can take their time sending it over the wire.
If the post was helpful, please vote, eh!
Current activities:
Book: Devils by Fyodor Dostoyevsky
Project: Hospital Automation, final stage
Learning: Image analysis, LINQ
Now and forever, defiant to the end.
What is Multiple Sclerosis[ ^]?
|
|
|
|
|
Trigger is how you initiate it, how are you goung to get local db to talk to server db, this is the crucial question as I think your options break down to the following or some kludge connection.
Linked server
SSIS
Web service
I'm not sure express supports this tech
I would consider installing and maintaining a local db to be an outrageous cost for this requiremment. What happend when you move from Dev/UAT to production servers etc.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I was considering a Linked Server but the more I think about it, the less appealing the idea becomes.
If the post was helpful, please vote, eh!
Current activities:
Book: Devils by Fyodor Dostoyevsky
Project: Hospital Automation, final stage
Learning: Image analysis, LINQ
Now and forever, defiant to the end.
What is Multiple Sclerosis[ ^]?
|
|
|
|
|
Mustafa Ismail Mustafa wrote: appealing
Appealing AAAaahhhh runs screaming from the room
Appart from the fact that I don't think express supports linked servers, maitaining and deploying the solution would be plain nasty. Go for the addition thread doing the write back.
|
|
|
|
|
hi
i want save a picture from a picture box in dataset,and then update Access DataBase with this dataset.
how i do?
plz help me
Have a nice day
|
|
|
|
|
Plz Help me
Have a nice day
|
|
|
|
|
If you Google/Bing for something like c# save image to database or vb.net save image to database, depending on your language, you will get loads of help.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Thank You
Have a nice day
|
|
|
|
|
Hello,
I'm having this error during installation.I've tried a bunch of things like
copying to hard drive and installing, but to no avail.
The error:
cabinet File 'sql.cab' required for this installation is corrupt and cannot
be used. This could indicate a network error, an error reading from the CD-ROM, or a problem with this package.
Any solution would be greatly appreciated.
TIA
|
|
|
|
|
Clearly the installation is Corrupted.
Download SQL SERVER 2005 directly from internet or call Microsoft help desk for help. 
|
|
|
|
|
Im trying install SQL Server on windows XP.
I can install SQL Server on other machines but I can install it on my machine !!!
Where should the sql.cab file be on the installation disk? I can't find it anywhere...
So what shuld be cause and how can I overcome this error for proper installation into my machine .
|
|
|
|
|
Which version of XP?
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
Try a different cd/dvd unit - I had exactly the same problem over the weekend. Used an external USB dvd and no problems.
|
|
|
|
|
I want to learn how to design database ,i want some solved examples, samples,case studies,step by step solutions ,or some one has some materials etc ,
please guide me how to design DB,i have much problems in creating relationships.
you can send me any helpful material at waqarnaeem2@hotmail.com
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
