|
hi all
i want to use some thing like that
set @a=@AnimalName == "Elephant"? "savannah": "unknown"
but it has an error in ==
how can i fix it?
besides i want to use conditional ?: operation to concat some strings like bellow how i should so;ve this problem?
N'insert into TBL Values(@Val1,'+ @Condition = 1? N'val2':N'val3')+',...'
|
|
|
|
|
Conditionals in Transact-SQL are expressed using the CASE statement[^]. Examples in the documentation should help you construct what you need.
|
|
|
|
|
dear friend
1- thanks to your reply it works for part of my storedprocedure
2- i heard that CASE is not high performance statement
3- what should i do if i want to shorten this block of code?
if(@flag=1) then
select * from TBL1
ELSE
select * from TBL2
i think this is not applicable to using CASE
am i right?
|
|
|
|
|
1. No problem
2. This does not make any more sense than "for loops are not high performance statements".
3. Assuming TBL1 and TBL2 have identical structure, you can do it like this:
select * from TBL1 where @flag=1
union all
select * from TBL2 where @flag<>1
<pre>
|
|
|
|
|
|
Hi all,
I'm using POCO[^] ODBC libraries to access SQL Server 2005, and have hit a problem.
If I enter a plain text statement:
session << "INSERT INTO TableName VALUES(44)", now;
it works fine. If I use a parameterised query:
int size=44;
session << "INSERT INTO TableName VALUES(:size)", use(size), now;
it throws an exception. The table contains a single int column.
Any suggestions gratefully received!
|
|
|
|
|
viaducting wrote: session << "INSERT INTO TableName VALUES(:size)", use(size), now;
Read up on parameterised queries, I don't recognise this syntax, it look like you are trying to insert the text "size" into the int field.
Sorry, just realised the POCO thingy may support that syntax. I suggest you chase this down using their support, this is not a problem with sql server but the POCO library.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
That was it - it supports the ? syntax but fell over on the :name syntax, but only the :name syntax is in their documentation.
|
|
|
|
|
Could you give more details on the exception that you are getting? Is it coming from POCO or from the DB?
The code appears lifted straight from the POCO example book - the only difference is that you use an int, while they use a string. Assuming that their example works, and that you also use SQLite, there's only a small number of places where you could get an error. First thing I would try is specifying the list of column names: INSERT INTO TableName (myIntColumn) VALUES(:size) : the single-column syntax looks suspicious. Then I'd look at the error coming back, and try to decipher it.
|
|
|
|
|
I want to create the existing sql2000 DTS in sql 2008, i installed sql2000 backward compatibility. i can open the DTS in sql2008 and save as the location sql server. It is saving in sql2008 and when i open the dts in the popup window asking to select the version, but i can't see any version in the popup window.how can i sort this issue.
|
|
|
|
|
DTS is sort of gone in 2005 and up. Look at SSIS.
|
|
|
|
|
Rewrite the bloody thing, you are relying on a conversion utility to work perfectly, they almost never do, especially with anything that is even mildly complex.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
So I'm trying to get rid of the DAO classes and references in my MFC app, and there seem to be some good drop in replacement classes here.
But does anyone know of an easy way to re-do "DoFieldExchange" in ADO?
|
|
|
|
|
|
let suppose i have table like this:
id name key
1 sam sam1
2 joy joy2
name and key are inserted by user. Now whenever i am inserting new value, I need to check whelther I have that key exists or not. If yes, do nothing and if no, insert it. I know how to do it in dot net page but can I do this in sql query itself ? If yes, can anyone please help me..
suchita
modified on Wednesday, June 29, 2011 9:32 AM
|
|
|
|
|
Yes. You could try:
IF NOT EXISTS (SELECT DISTINCT keyname FROM <table_name>)
BEGIN
INSERT INTO.....
END
As an aside: this may not be the case because I can only see two rows of sample data, but if the [keyname] value is a concatenation of [name] + [id] and [id] is an identity column, then you should always have a unique value in [keyname].
Hope that helps.
|
|
|
|
|
If uncrush is right then one has to ask why you are creating the KeyName field, it is redundant.
When I have to check for existing keys, I return the record instead of using exists, then test if I got a record, that way I have all the information about the record. I always put this type of work into a stored procedure that returns the record, the UI may not even know if the records was inserted ot not.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Not knowing what database you are using, but you could also put a UNIQUE CONSTRAINT on the keyname column.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
I dislike using these, they tempt the developer into programming by error, try and add the dupe and trap the error. Whereas checking first makes the constraint redundant an does not generate an error.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I disagree, on the same terms that I do not check whether a file is readonly/accesible/existing when I delete it.
Bastard Programmer from Hell
|
|
|
|
|
Eddy Vluggen wrote: on the same terms that I do not check whether a file is
readonly/accesible/existing when I delete it
If I am coding file I/O I certainly do!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Again depending upon the database and how the constraint operation is implemented, there may also be performance gains by using the constraint instead of coding it into a program. But your mileage may vary extensively.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
SayamiSuchi wrote: keyname ... inserted by user
Then why should it be unique?
If there's something that needs to be unique, then it should be created/set by the system not the user.
You already have id , what more do you need?
|
|
|
|
|
PIEBALDconsult wrote: If there's something that needs to be unique, then it should be created/set by the system not the user
So next time you signup for an online service your username will be papsd9308skjfd9kdi32 .
|
|
|
|
|
Yeah, pretty much, at least I won't have to waste a half hour trying things and trying things always having it tell me it exists.
Think of Animal House -- "Your name is Flounder. Your name is Pinto." Simple, saves the user a lot of needless trouble.
|
|
|
|