|
where
L.ProductID=
case coalesce(@productid,'') when '' then L.ProductID else @ProductID end
|
|
|
|
|
Ok could you explain exactly what this does. I don't understand how it performs the same task as my original code
|
|
|
|
|
If @param is null or '' then where will be:
...where l.product=l.porduct and will do nothing
but if @param has eny value where will be:
...where l.product=@param and will do filtering by column l.product
CASE..when...wnen...END is very powerfull and can be used in meny ways
|
|
|
|
|
OK thanks for that but im still not following. Perhaps this might help.
The code should add a filtering AND statement if the parameter was passed. As it stands I am checking for either a null value or no value to be passed and then doing the work in the else part of the statement . So really all I require is the code to check that a parameter has a value. If it does i want to perform the filter.
|
|
|
|
|
ok try this
IF @ProductID IS not NULL OR @ProductID <> ''
begin
SET @MainQuery = @MainQuery + ' AND L.ProductID = ''' + @ProductID + ''''
end
execute(@MainQuery)
|
|
|
|
|
I got the
@ProductID IS NOT NULL working fine.
The <> does't seem to work though. If I set ProductID to '' and use it I still get a succesfull match. Which of course not right?!
|
|
|
|
|
Change it to:
IF @ProductID IS NOT NULL AND @ProductID <> ''
The OR condition you were shown will evaluate to true if either part is true, which setting @ProductID to an empty string will do.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
That makes no sense. This will never evaluate to true ever. If @ProductID is null then it can't = ''. If @ProductID = '' it wont be null. Both side of this statement would have to be true for the whole thing to return true and this is not possible.
Unless of course SQL works in a completely different way to every other programming language I've used??
|
|
|
|
|
hi friend
what type is your @param
be fast
|
|
|
|
|
|
try
coalesce(@param,'') that mean if is null translate it like ''
|
|
|
|
|
Welcome to the wonderful world of NULLs. Try the following test:
DECLARE @ProductID NVARCHAR(10)
SET @ProductID = null
IF @ProductID IS NOT NULL AND @ProductID <> ''
BEGIN
PRINT 'Started 1st test'
END
ELSE
BEGIN
PRINT 'Failed 1st test'
END
-- Will print Failed 1st test
SET @ProductID = ''
IF @ProductID IS NOT NULL AND @ProductID <> ''
BEGIN
PRINT 'Started 2nd test'
END
ELSE
BEGIN
PRINT 'Failed 2nd test'
END
-- Will print Failed 2nd test
SET @ProductID = 'Hi'
IF @ProductID IS NOT NULL AND @ProductID <> ''
BEGIN
PRINT 'Started 3rd test'
END
ELSE
BEGIN
PRINT 'Failed 3rd test'
END
-- Will print Started 3rd test
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
|
Hi 2 all,
How can i access remote sql server 2000 from QueryAnalyzer or StoredProc
over TrustedConnection for InsertUpdateDelete
|
|
|
|
|
This requires a WAN setup of some sort. I'm no network admin, but a VLAN might work also. But either way there has to be a way for the account on your LAN to be trusted in the remote domain. You'll have to discuss it with the network admin in charge of the network where your SQL Server is located. Otherwise you're stuck using SQL Server Authentication.
|
|
|
|
|
Hello everyone,
I am trying to get the list of table in a Database and the total number of rows in each table. I have created the loop which loops and get the Table names. I have problem getting the total number of rows for each table in the Database. I successfully get the Table's name but once it comes to getting the total number of the row, I get problem. I am not sure if I can use the Odbc[0] once again to get the total number of the row since it is in use for the loop to get the Table's name.
My code is:
OdbcCom = new System.Data.Odbc.OdbcCommand("USE " + DataBaseName + ";", OdbcCon);
OdbcDR = OdbcCom.ExecuteReader();
OdbcCom = new System.Data.Odbc.OdbcCommand("SHOW TABLES;", OdbcCon);
OdbcDR = OdbcCom.ExecuteReader();
txtLog.AppendText("Tables inside " + DataBaseName + ":\r\n");
while (OdbcDR.Read())
{
txtLog.AppendText(">> " + OdbcDR[0] + "\r\n");
ListViewItem item1 = new ListViewItem(OdbcDR[0].ToString());
MessageBox.Show("Table name:" + OdbcDR[0].ToString());
// START COUNT
OdbcCom = new System.Data.Odbc.OdbcCommand("SELECT COUNT(*) FROM " + OdbcDR[0].ToString() + ";", OdbcCon);
OdbcDR = OdbcCom.ExecuteReader();
// What should I put to get the return value "Total row number" in here?
item1.SubItems.Add("Need to add the total number of rows here!");
// END COUNT
listView1.Items.Add(item1);
}
Could you be kind enough to help me out on this one? Thank you very much and have a great day.
Khoramdin
-- modified at 16:16 Sunday 4th March, 2007
|
|
|
|
|
You will need to create a new instance of the DataReader in the loop where you are trying to get the count. Or instead of getting the data reader you could call the ExecuteScalar function on the command.
Mike Lasseter
|
|
|
|
|
hey guys,
i have quite a few SQL databases on my PC. I want to script all the databases into a text file. The script should contain both the data and the structure of the databases. How can i do this? I want to execute this process from a batch file. Is there a tool or something that i could use? I've been hunting for something like this for a long time but I haven't found anything. In MySQL tis is a relatively simple process which can be accomplished by using the mysqldump utility.
|
|
|
|
|
|
Hi 2 all,
Like use statment can select Db
is there any statment for Server
like use MyServer.Mydatabase
|
|
|
|
|
No
But you can reference tables on other linked servers using fully qualified names:
Servername.DBName.ObjectOwner.ObjectName
|
|
|
|
|
To fetch data from a different instance need ot link to the current instance
for which need to use sp_addserver / sp_addlinkedserver
Regards
KP
|
|
|
|
|
I am so f. tierd so plese be more specific if it is ok with u
|
|
|
|
|
Hi,
I have three tables with two relations.
First table is users, which just stores userids (autonumber), and a lastseen (text).
Second table is Names. Every user has names, right? So it's just rows of userid(long)/name(text).
Third table is IPs. Every user has a few ips, right? So again, it's just userid/ip.
The relations are just the ones I mentioned, a user has names and ips.
I have a name and an ip. I want to select a union from the IPs and Names.
The union needs to have only users that have the ip given in their ips, or - here's a tricky part - have an ip with first two octets the same as the given ip but also the name must appear in the user's names.
The idea is that given a name and ip, I want to select all previously entered users who are probably the same user.
But, after finding all these users, I also want to merge everything that I found to just one user. So all the ips I found should be updated to relate to the first user found, and the same for the names.
Any ideas?
I tried this: (given name is MasterShin and given ip is 204.15.0.0)
SELECT Players.userid, Names.name as [value], 1 as [type] FROM (Players RIGHT OUTER JOIN [Names] ON Players.userid = Names.userid) LEFT OUTER JOIN IPs ON Players.userid = IPs.userid
WHERE IPs.ip = '204.15.0.0' OR (IPs.ip LIKE '204.15.*' AND Names.name = 'MasterShin')
UNION
SELECT Players.userid, IPs.ip as [value], 2 as [type] FROM (Players RIGHT OUTER JOIN IPs ON Players.userid = IPs.userid) LEFT OUTER JOIN Names ON Players.userid = Names.userid
WHERE IPs.ip = '204.15.0.0' OR (IPs.ip LIKE '204.15.*' AND Names.name = 'MasterShin')
And after the select I can go over everything, save on the side what updates need to be done, and later do them.
But this big statement seems repetitive and not efficient...
|
|
|
|
|
It is a little late for me, so I did not read your hole problem. Maybey tomorrow. But, youe have one table with userids and a table with usernames. Is it possible that a userid has more names? If not, why not combine those two tables to one?
Wout Louwers
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
