Create PROC ReportRequiredBatch
@Plan varchar(5)=null,
@ID varchar(5)=null,
@UserID int
AS
if PlanID= '0' or @Plan IS NULL or @Plan='-1' SET @Plan= ''
if @ID='0' or @ID IS NULL or @ID='-1' SET @ID= ' '
SELECT DISTINCT ICCode, HistoryCode, PlanID, UserID from PlanTable
Could I have done the upper given approach the following way:
CREATE PRoc ReportRequiredBatch
Select Distinct ICCode, HictoryCode, PlanIDCode Case PlanID When '0' or 'null or '-1' Then ' ',
case ID when '0' or 'null or '-1' Then ' ' '
My query might be not exact , free or error. but u get the idea.
What I am trying to see if we can complety take out the If/else and just work on the case.
So is it doable??
For example, in C# or visual basic
we can use if/else and also the select or case. so is it the samething.
or is it better to work with one rather than other.
I have created an SQL server table in the past on a server that was all case sensative. Over time I found out that switching to a server that is not case sensative still caused my data to become case sensative. I read an article that said you should rebuild your master database then re-create your tables. So after rebuilding the master database, a basic restore would not be sufficient? I would have to go and manually re-create every single table again?
Can someone point me to a tutorial on how to search against a SQL Server 2000 using a case insensitive search when SQL Server 2000 is a case sensitive installation?
We need to install CI database on CS server, and there are some issueswith stored procedures.Database works and have CI collation (Polish_CI_AS). Server hascoresponding CS collation (Polish_CS_AS). Most queries and proceduresworks but some does not :-(We have table Customer which contains field CustomerID.Query "SELECT CUSTOMERID FROM CUSTOMER" works OK regardless ofcharacter case (we have table Customer not CUSTOMER)Following TSQL generate error message that must declare variable @id(in lowercase)DECLARE @ID INT (here @ID in uppercase)SELECT @id=CustomerID FROM Customer WHERE .... (here @id in lowercase)I know @ID is not equal to @id in CS, but database is CI and tablenames Customer and CUSTOMER both works. This does not work forvariables.I suppose it is tempdb collation problem (CS like a server collationis). I tried a property "Identifier Case Sensitivity" for myconnection, but it is read only and have value 8 (Mixed) by default -this is OK I think.DO I MISS SOMETHING ????
Yesterday I received a response to my CI/CS Collation problem and therecommendation was to try and restore a CI Collation database to a CSCollation database. After creating a blank CS database a full restore(Force restore over existing database) does change the Collation toCI. I'm unsure as to how I can restore without changing theCollation. Any suggestions?
I am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?
I am curious with using replication in sql server 2005 one way from db A (source) replicating to db B(destination) in which db A has a collation of CS and db B has a collation of CI. Will there be any problems with this scenario? Thanks in advance!
I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.
Previously, I had been duplicating the CASE logic for both columns, like so:
Code Block...beginning of SQL view... shipment_status = CASE [logic for condition 1] THEN 1 WHEN [logic for condition 2] THEN 2 WHEN [logic for condition 3] THEN 3 WHEN [logic for condition 4] THEN 4 ELSE 0 END, shipment_status_text = CASE [logic for condition 1] THEN 'Condition 1 text' WHEN [logic for condition 2] THEN 'Condition 2 text' WHEN [logic for condition 3] THEN 'Condition 3 text' WHEN [logic for condition 4] THEN 'Condition 4 text' ELSE 'Error' END, ...remainder of SQL view...
This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.
This is what I'd like to do:
Code Block ...beginning of SQL view... shipment_status = CASE [logic for condition 1] THEN 1 WHEN [logic for condition 2] THEN 2 WHEN [logic for condition 3] THEN 3 WHEN [logic for condition 4] THEN 4 ELSE 0 END,
shipment_status_text =
CASE shipment_status
WHEN 1 THEN 'Condition 1 text'
WHEN 2 THEN 'Condition 2 text'
WHEN 3 THEN 'Condition 3 text'
WHEN 4 THEN 'Condition 4 text'
ELSE 'Error'
END, ...remainder of SQL view...
This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.
Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?
I currently have a SQL Server cluster setup with a Primary DB Server SERVER1 and the Standby server SERVER2. SERVER1 has been failing more than normal is the past few weeks and its takes upto 5 mins for SERVER2 realize that SERVER1 is down. I am looking for a better way to implement a backup server on production with minimum downtime. Please adivse..
Anyone have much experience with SQL Server T-SQL CASE statement? I have some code that someone else wrote that looks like this: SELECT m.messageID, m.isTop, CASE @Mode WHEN 10 THEN m.subject WHEN 12 THEN p.userID ENDFROM Messages mINNER JOIN People p ON m.userID=p.userIDWHERE [blah][blah][blah]. What I need to do is return something that is not the userID, but still have that name. This did not work: SELECT m.messageID, m.isTop, CASE @Mode WHEN 10 THEN m.subject WHEN 12 THEN pr.userName As userID ENDFROM Messages mINNER JOIN People p ON m.userID=p.userIDINNER JOIN Personalize pr ON m.userID=pr1.userIDWHERE [blah][blah][blah]. SQL dies on the "As"... (or, if I leave out the "As", it dies on "userID") Any idea how I can do this? Thanks,
I am working on a C#/asp.net web application. The application has a text box that allows a user to enter a name. The name is then saved to the database. Before the name is saved to the database, I need to be able to check if the name already exists in the database. The problem here is that what if the name is in the database as "JoE ScMedLap" and somoene enters the name as "Joe Schmedlap" which already exists in the database,but just differs in case. In other words how do deal with case sensitiviy issues.
I dont know the what is the exact replication case i am talking about but here is the scenerio. "I have a SQL Server database on my local machine. I want to update same database to a remote database (hosted SQL SERVER database on web) which i can access from anywhere from my laptop then I want to make these changes update back itself on my local machine connected with internet) I want both updation plan run at the end of day or when ever I want.
I thought first that it is merge replication. but it is that much complicated. it is quite simple as compared to merge replication ..... where we have Publisher ,subscriber & distributor etc
Environment1)We are using VBA,MS Access 2000/2003 and SQL Server 2000 in ourproject.2)We need to use Rational clear case as the SCM tool.Our WorkOur work primarily falls in developing SQL Server stored procedures,functions and few VisualBasicForAccess(VBA) code.Technical Help sought1) Does SQL Server 2000 provide any inbuilt capabilities of SoftwareConfiguration Management(SCM)?Not that I am aware of other thanextending it with other DM management tools like BMC,A&G,VERITAS etc.2)If not, Is there any plug-in available in SQL Server environment thatcan be installed on SQL Server so that any code developed [Storedprocedures/functions etc] can be managed in Clearcase.[I am looking forEclipse plugin's kind of plugins which would enable you to check in andcheck out from Eclipse after the initial configuration is done onEclipse]?3)If plugins are not available then the option we have is, manual andexternal check in and check out the code bits in to Clearcase.Can someone point me to concise and relevant user manual on this.
I am trying to convert this query to slq server 2000 and I cant figureout how to get rid of the IIF statements and make them case statements.If anyone could help I would greatly appreciate it!Thanks!spafaSELECT Jeopardy.Main, Jeopardy.Name, Jeopardy.COMMENTS2,Jeopardy.STATUS, Jeopardy.DENTAL_STATUS, Jeopardy.HLTH_INC,Jeopardy.DNTL_INC, Jeopardy.COMP_HLTH, Jeopardy.COMP_HLTH_DISC,Jeopardy.COMP_PLAN_DESIGN, Jeopardy.COMP_DNTL, Jeopardy.COMP_DNTL_DISC,Jeopardy.OUT_TO_BID, IIf([COMP_HLTH]="Mass BlueCross","YES",IIf([COMP_HLTH]="Out of State BlueCross","YES",IIf([COMP_HLTH]="CT BlueCross","YES",IIf([COMP_HLTH]="Empire Blue Cross","YES","NO"))))AS OTHER_BC_PLAN, IIf([other_bc_plan]="yes",[COMP_HLTH],"") ASBC_PLAN, Jeopardy.LG_RANKING, Jeopardy.LG_SCORE, Jeopardy.DATE_NOTIFIED,Jeopardy.DATE_UPDATED, Now()-([Jeopardy]![DATE_UPDATED]) AS DATEDIFF,Now()-([Jeopardy]![DATE_ADDED]) AS DATEDIFF2,IIf([DateDiff]<8,"*",Null) AS CHANGE, IIf([DateDiff2]<8,"+",Null) AS[ADD], Jeopardy.Rep_Id, Jeopardy.Rep_Name, tblIRIP_QA_NAMES.ADMIN_NAMEAS MSS, AccountOwnership.ANALYST_NAME, AccountOwnership.UND_NAME,AccountOwnership.DNTL_UND_NAME, AccountOwnership.SERVICE_REP,AccountOwnership.SIZE, AccountOwnership.SIZE2, Jeopardy.CYCLE,Jeopardy.DENTAL_CYCLE, IIf([Jeopardy]![cycle] Is Null,[Jeopardy]![DENTA-L_CYCLE],IIf([Jeopardy]![cycle]="N/A",[Jeopardy]![DENTAL_CYCLE],[Jeop-ardy]![cycle])) AS CYCLE2, AccountOwnership.Canc_Date,AccountOwnership.Dntl_Canc_Date, AccountOwnership.EFFDATE,AccountOwnership.Dntl_EFFDATE, AccountOwnership.TOTALHLTH,AccountOwnership.TOTALDNTL, [healthmate]+[classic] AS TotalCross,AccountOwnership.HEALTHMATE, AccountOwnership.CHIP,AccountOwnership.CLASSIC, AccountOwnership.BROKER,AccountOwnership.HLTH_BROKER_1, AccountOwnership_DSC.DISPOSITION,AccountOwnership_DSC.DISPOSITION_MONTH, IIf([DISPOSITION_month] Is NotNull,"YES","NO") AS OC, IIf([DISPOSITION_month] Is NotNull,[DISPOSITION_month],Null) AS OC_MONTHFROM ((AccountOwnership_DSC RIGHT JOIN AccountOwnership ONAccountOwnership_DSC.Main = AccountOwnership.Main) RIGHT JOIN JeopardyON AccountOwnership.Main = Jeopardy.Main) LEFT JOIN tblIRIP_QA_NAMES ONAccountOwnership.REP_ID = tblIRIP_QA_NAMES.Rep_Id ORDER BYJeopardy.DATE_UPDATED DESC; " );--Posted via http://dbforums.com
I have to use the Case expression in my query, so I search arround the web and got the following: SELECT title, price, Budget = CASE price WHEN price > 20.00 THEN 'Expensive' WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate' WHEN price < 10.00 THEN 'Inexpensive' ELSE 'Unknown' END,FROM titles It should run OK base on my research in the internet. But my SQL Server gave me error: syntax error arround '>'.I did several search and many people can use the ">" sign or "<" sign in the Case expression, but I just can't use it in my SQL Server, I can't even use any boolean expression, I can only use values.can anyone help me out? My SQL Server Version is SQL Server 2000 Sevice Pack 4.Thanks!
I have a Users table with UserName and Password columns, in SQL Server 2005. Currently the SQL Server is not set up to use case-sensitive criteria in the sprocs. How can I write my sproc to be case sensitive when searching on username and password?
I require to change the case sensitivity of a server. If I run rebuildm (changing the sensitivity to the desired value) will that not mean that I won't be able to reattach the databases?
/* I am having trouble with the CASE statement when ">50". It comes out as zero (0) when according to the data in the example should be ".9". */ CREATE TABLE #CLASS_TIMES ( START_TIME DATETIME, END_TIME DATETIME ) INSERT INTO #CLASS_TIMES (START_TIME, END_TIME) SELECT '2015-05-08 10:00:00.000','2015-05-08 10:45:00.000' UNION ALL --The record in question
I transferred data from Oracle to sql server 2005. Now what i want is to make data in the tables case-sensitive .(it has to be data inside the tables only and not table and column names). what i tried is :
alter database test collate Latin1_General_CS_AS
But to my horror it made the tables name case-sensitive . Plz help me out asap.
I have just been given a new SQL Server 2000 box to look after in production. I just tried to run a standard t-sql script I use for setting up backup jobs and so on. However, it failed with a long list of errors - quite a surprise at first since I have run the same script on many other servers wihtout a hitch. On close examination, the problem appears to be that the new serer is setup with a server default collation... Latin1_General_BIN (I think a binary based collation makes this a case sensitive server).
This is quite an urgenet one since I have to get this wrapped up today. I don't think I can change the server's default collation without a lot of red-tape. Is there a quick way to run my scripts in a 'case insenstive' context within Query Analyzer? If so, how?