|
Thats great. Thank you.
Just one question though: If this were to be run against a large table, would the fact that a where clause is included impact the performance? or would the 1=1 condition make it run just as fast as if there were no where clause?
oooo, the Jedi's will feel this one....
|
|
|
|
|
I like the ISNULL example above, but I have a feeling it will affect performance - I have reservation as to whether SQL will correctly use indexes when resolving the function call.
Ive always used this method, which I know works:
SELECT *
FROM myTable
WHERE @ItemID IS NULL OR ItemId = @ItemId
Also, depending on which version of SQL Server you're using, prefixing your stored proc with sp_ affects performance in that it looks in the master db first. I think you'd have to be using a very old version for this to be true, but its good practice not to use an sp_ prefix - if you really like prefixes use usp_ to distinguish from in-build sp's.
|
|
|
|
|
Thats just what I'm after. Thanks.
oooo, the Jedi's will feel this one....
|
|
|
|
|
If it's a simple query then it's OK to do this:
IF @ItemID IS NULL
BEGIN
SELECT * FROM myTable;
END
ELSE
BEGIN
DECLARE @like NVARCHAR(6)
SET @like = '%' + CAST(@ItemID AS NVARCHAR(5))
SELECT * FROM MyTable WHERE ItemID LIKE %like;
END
Or similiar depending on your needs.
Jeremy Likness
http://csharperimage.jeremylikness.com/
|
|
|
|
|
Ive also used this method, but i think the OP suggested that the query was long and complex - and therefore duplicating the code inside IF logic was going to get tough.
Good answer though. The OP now has all 3 possible methods!
|
|
|
|
|
Hi. I've a table Project(ProjectId, ParentProjectId, ProjectName)
the sample data are --------------------------
1,0, X
2,1,Y
3,2,Z
4,3,W
now for a given project id i want to get projectid, projectname for a given projectid.
e.g. i want 1,X for projectid = 4
i want to SQL query for this. plz help me.
|
|
|
|
|
So what have you tried? Anyone who has read more than 1 chapter of a book on SQL or has spent more than 10 minutes learning what to do can answer this. It is so basic that I suspect you tobe a troll.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
To be fair, it's not quite that straightforward if you look at the data.
ProjectId ParentProject ProjectName
1 0 X
2 1 Y
3 2 Z
4 3 W
What he wants (I think) is to start from project 4, and go all the way back to the root parent. So, 4 is a child of 3 which is a child of 2 which is a child of 1 which has no parent. So, he wants to know that project 4 (name W) is part of the hierarchy ultimately descending from project 1 (name X).
The SQL to pick out the ultimate root parent when you don't know how deep the hierarchy might be is actually quite challenging. I'm not sure a beginner could write this. I'm not sure I could, to be honest.
|
|
|
|
|
I'm impressed, I did not get that from the question at all. And yes building the hierarchy stuff is quite challenging. I did an article on using it in C# but the SQL stuff I left out for this reason. It is also not well covered.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
First of all the question is unclear.
What I made out is that, you want to get the parent record of 4 i.e. 1
What if I pass the Projectid = 3 or 2?
You should give the relevant scenarios so that others can also make out.
Any way, based on what I understand, here is the solution
SELECT PROJECTID,PROJECTNAME
FROM PROJECT
WHERE PROJECTID = ( SELECT P1.PARENTPROJECTID + 1
FROM PROJECT P1
INNER JOIN PROJECT P2
ON P2.PROJECTID - <code>4</code> = P1.PARENTPROJECTID )
Hope this helps.
But as what I found that if for 4 the parent is 1, the same applies to 2 or 3 also.
In that case why not you are making a top count?
Let me know in case of any concern
Niladri Biswas
|
|
|
|
|
Take a look at this[^] article. If it does not actually solve your problem, it should at least give you some terms to Google.
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.”
|
|
|
|
|
Select @parentid=parentid from tablename where projectid=4
if(@parentid<>0)
Begin
WHILE (@PARENTID <> 0 )
BEGIN
Select @parentid=parentid from tablename where projectid=@parentid
if(@parentid is null)
break
set @id=@parentid
End
End
You can use these logic to get the parentid, Variable @id will contain your final parentid I had not tested this code so u may have to made some change in this.
It may get in infinite loop so i used
if(@parentid is null)
break
|
|
|
|
|
Now all of my stored procs are coming in with a ;1 at the end of them. I haven't seen them since the old VMS days...
It only does it on the CommandText of the tableadapters, and only on one of my machines. Both are using the same connection string...
Ok, so what setting did I change?
Thanks,
Nick
This are my own opinions. You know the rest.....
|
|
|
|
|
Nick Jacobs wrote: Ok, so what setting did I change?
Oh goody this is one of those guess the setting games, aaahhhhmmmm, nope I dunno what setting did you change. Bloody hell Nick how are we supposed to guess that?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
This
Mycroft Holmes wrote: Bloody hell Nick how are we supposed to guess that?
My hope is somebody ran across the same problem. That's how we're suppose to "Guess" that one.
This are my own opinions. You know the rest.....
|
|
|
|
|
I want to get the values from a database table cells and set them as text for say four radiabuttons.
please help.
ML Lingwati
|
|
|
|
|
And what is your concrete problem? Reading from the database? Setting the text? Please be more specific.
|
|
|
|
|
SeMartens wrote: And what is your concrete problem? Reading from the database? Setting the text? Please be more specific
Both I guess
ML Lingwati
|
|
|
|
|
Take and look this.[^]
I Love T-SQL www.aktualiteti.com
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hi Experts
i Want To search fuzzy word search using where clause in sql server 2005
Thank u
|
|
|
|
|
Sharma Dinesh Kumar wrote: i Want To search fuzzy word search using where clause in sql server 2005
Thank u
Take a look at FTS in SQL (to search the "Full Text"), and decide your level of fuzziness. Then search for background information on SoundEx and Levenstein
I are troll
|
|
|
|
|
this is a sample account statement generation for an e-banking software
Plz i want the accounts and customers details to appear even if no transactions exists.
am finding it difficult making that work..i have tried full outer joins and outer joins
any help?
code below
IF @type='RANGE'
BEGIN
print 'Query began'
SELECT (SELECT Openbal FROM Accounts WHERE SUBSTRING(AccNum,9,4)=@AccNum) AS openBal,(SELECT Name FROM ThisCompany) AS CompName,
(SELECT Branch FROM ThisCompany) AS Branch,
(SELECT StreetAdd FROM ThisCompany) AS StreetAddress,
(SELECT AddLine1 FROM ThisCompany) AS AddLine1,
(SELECT AddLine2 FROM ThisCompany) AS AddLine2,
@dt1 as Date1,@dt2 as date2,
(SELECT Tel1 FROM ThisCompany) AS Tel,
(SELECT Website FROM ThisCompany) AS Web,
SUBSTRING(Accounts.AccNum,9,4) AS CustNum,
Accounts.AccType, Accounts.Currency, Customers.FName+' '+
Customers.LName as FName, Customers.CorrespAdd,
Transactions.Dt, Transactions.Debit, Transactions.Credit, Transactions.TranDetails, Transactions.Bal
FROM Accounts FULL OUTER JOIN
Customers ON Accounts.CustNum = Customers.CustNum FULL OUTER JOIN
Transactions ON Accounts.AccNum = Transactions.AccNum
WHERE SUBSTRING(Accounts.AccNUM,9,4)=@AccNum
AND Transactions.Dt BETWEEN DATEADD(dd,1,@dt1) AND DATEADD(dd,1,@dt2)
ORDER BY Transactions.Dt ASC
RETURN
END
|
|
|
|
|
prubyholl wrote: Plz i want the accounts and customers details to appear even if no transactions exists.
Replace the "Where" keyword with "And".
The outer join fails because the where condtion executes after the completion of outer join. Hence you dont get any results.
Whereas when you use an "And" clause , the condition is evaluated while the execution of the outer join.
Also handle the null values gracefully when there is no transaction for a customer.
When you fail to plan, you are planning to fail.
|
|
|
|
|
|
I want to display one row at a time on a gridview from my database table, where i'll be selecting the row by its primary key randomly and some condition using sql server 2005.
please help.
ML Lingwati
|
|
|
|