maxlezious (5/3/2013)
Thanks for your reply Lynn.We have this working php code and i am trying to convert this into sql
$db_name = "DB_admin1" ;
$conn = mssql_select_db($db_name) or die('Connection to the Database failed!
') ;
$query = "SELECT DISTINCT ClientName FROM tblClient WHERE ClientNumber IN (SELECT ClientNumber FROM tblSystem WHERE SystemNum = ".$_SESSION['system'].")";
$result = mssql_query($query) ;
if($row = mssql_fetch_row($result)){
$clientName = $row[0];
if(is_numeric(strrpos(strtolower($clientName), '_p_')))
The following query selects only one client name as it should
SELECT DISTINCT ClientNameFROM tblClient
WHERE ClientNumber
IN
(SELECT ClientNumber FROM tblSystem WHERE SystemNum = 30461)
All I want is a case/if statement so if the client name has '_p_' in it, then return 3 otherwise 0
This provides close enough insight that I think I can offer some suggestions. First of all you really should not be using pass through like you are. You should instead use stored procedures for data retrieval. The advantages to this are many. First, your developers don't have to write their own sql (but they can still write their own procs if they are able). The bigger advantage is that you separate the data from the business logic. This means you could write a new front end for you application and not have to rewrite all of the data in addition.
For your specific query I think you can get rid of the subquery and joing to tblSystem instead. This code is untested because we don't have ddl but I think this should be close.
create procedure GetSomeNumber
(
@SystemNum int
) as
SELECT DISTINCT case when ClientName like '%_p_%' then 3 else 0 end as ReturnSomething
FROM tblClient c
join tblSystem s on c.ClientNumber = s.ClientNumber
WHERE s.SystemNum = @SystemNum
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/