x

MySQL to MSSQL: IF(value=5,1,NULL) how to do it in MSSQL?

How can I convert this MySQL statement to MSSQL?

IF(value=5,1,NULL)

It is a simple IF statement that returns the first value if TRUE (1), and the second if FALSE (NULL)

I am using this with PHP so it is not to be placed in a function/procedure.

Thanks!
more ▼

asked Jul 20, 2010 at 09:40 PM in Default

johnshaddad gravatar image

johnshaddad
43 8 8 10

(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest
SELECT CASE WHEN VALUE = 5 THEN 1 ELSE NULL END
more ▼

answered Jul 20, 2010 at 09:44 PM

Cyborg gravatar image

Cyborg
10.6k 36 40 45

The statement on its own is working, and i have tried it before on MSSQL Query window. But my question was about PHP inline mssql query... to put it in my PHP code.. not in a stored procedure or function.

I tried this but didn't work!

$query = "INSERT INTO notes (requestid,pmid,clientid,note) VALUES ('".$_GET['itemid']."',CASE WHEN LEN('$session->pmid') = 0 THEN NULL ELSE '$session->pmid',CASE WHEN LEN('$session->clientid') = 0 THEN NULL ELSE '$session->clientid','@note')";

Also tried it by adding SELECT before each CASE:

$query = "INSERT INTO notes (requestid,pmid,clientid,note) VALUES ('".$_GET['itemid']."', SELECT CASE WHEN LEN('$session->pmid') = 0 THEN NULL ELSE '$session->pmid',SELECT CASE WHEN LEN('$session->clientid') = 0 THEN NULL ELSE '$session->clientid','@note')";

Also tried adding "END" next to each CASE statement:

$query = "INSERT INTO notes (requestid,pmid,clientid,note) VALUES ('".$_GET['itemid']."', SELECT CASE WHEN LEN('$session->pmid') = 0 THEN NULL ELSE '$session->pmid' END,SELECT CASE WHEN LEN('$session->clientid') = 0 THEN NULL ELSE '$session->clientid','@note') END";

None works! What is wrong? I am good at MySQL but new to MSSQL, sorry!
Jul 20, 2010 at 10:08 PM johnshaddad

@Jhonshaddad

INSERT INTO notes (requestid,pmid,clientid,note)

SELECT @ItemID, CASE WHEN LEN('$session->pmid') = 0 THEN NULL ELSE '$session->pmid' END,CASE WHEN LEN('$session->clientid') = 0 THEN NULL ELSE '$session->clientid','@note') END
Jul 20, 2010 at 10:13 PM Cyborg
There is no point devolving this down to the SQL Server - you should clean your parameter up in the PHP first, then use a simpler construct for the actual insert.
Jul 21, 2010 at 01:04 AM Matt Whitfield ♦♦
I agree with @Matt - send only what you want to SQL Server. Use the front-end code to preselect the value. Fewer bytes crossing the network and less work for the back-end means better performance all round.
Jul 21, 2010 at 05:35 AM Blackhawk-17
lol sorry for the wrong name target @Matt Whitifield go grill that flying pig :P
Jul 21, 2010 at 07:27 PM johnshaddad
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x985
x68
x30

asked: Jul 20, 2010 at 09:40 PM

Seen: 7866 times

Last Updated: Jul 21, 2010 at 12:56 AM