Using LIKE..
in [Server Configuration]
|
Prev: Optimizer on SQL 2005
Next: Restore using MDF and LDF
From: Abba on 3 Feb 2010 03:30 Hello, [SQL2008] CREATE PROCEDURE [dbo].[uspGdet_temp] @filby Varchar(10), @filval Varchar(50) AS BEGIN SET NOCOUNT ON IF @filby = 'AcNum' BEGIN SELECT * FROM Acc WHERE [AcNum] LIKE @filval + '%' END ELSE BEGIN SELECT * FROM Reg WHERE CASE @filby WHEN 'Eml' THEN [EMAIL] LIKE @filval + '%' WHEN 'FNme' THEN [FirstName] LIKE @filval + '%' WHEN 'LNme' THEN [LastName] LIKE @filval + '%' WHEN 'CmpNme' THEN [CompanyName] LIKE @filval + '%' WHEN 'LoginId' THEN [LoginID] LIKE @filval + '%' WHEN 'AcntLog' THEN [ID] IN (SELECT RegID FROM DAcc WHERE DAccNum = @filval) ELSE 1=1 END END END While executing this, Iam getting this error: Msg 156, Level 15, State 1, Procedure uspGdet_temp, Line 16 Incorrect syntax near the keyword 'LIKE'. Msg 156, Level 15, State 1, Procedure uspGdet_temp, Line 22 Incorrect syntax near the keyword 'ELSE'. Msg 102, Level 15, State 1, Procedure uspGdet_temp, Line 25 Incorrect syntax near 'END'. Can someone please help? tia, AbbA
From: Uri Dimant on 3 Feb 2010 03:59 Abba you cananot write LIKE in CASE expression ORDER BY CASE @Direction WHEN 'DESC' THEN CASE @OrderBy WHEN 'FirstName' THEN firstname WHEN 'TelNo' THEN TelNo WHEN 'Email' THEN email WHEN 'FaxNo' THEN FaxNo END END DESC One option is to write IF .. ELSE blocks "Abba" <sql_help(a)aaa.com> wrote in message news:OfxBBtKpKHA.3776(a)TK2MSFTNGP04.phx.gbl... > Hello, > > [SQL2008] > > CREATE PROCEDURE [dbo].[uspGdet_temp] > @filby Varchar(10), > @filval Varchar(50) > AS > BEGIN > SET NOCOUNT ON > IF @filby = 'AcNum' > BEGIN > SELECT * FROM Acc > WHERE [AcNum] LIKE @filval + '%' > END > ELSE > BEGIN > SELECT * FROM Reg > WHERE CASE @filby > WHEN 'Eml' THEN [EMAIL] LIKE @filval + '%' > WHEN 'FNme' THEN [FirstName] LIKE @filval + '%' > WHEN 'LNme' THEN [LastName] LIKE @filval + '%' > WHEN 'CmpNme' THEN [CompanyName] LIKE @filval + '%' > WHEN 'LoginId' THEN [LoginID] LIKE @filval + '%' > WHEN 'AcntLog' THEN [ID] IN (SELECT RegID FROM DAcc > WHERE DAccNum = @filval) > ELSE 1=1 > END > END > END > > > While executing this, Iam getting this error: > Msg 156, Level 15, State 1, Procedure uspGdet_temp, Line 16 > Incorrect syntax near the keyword 'LIKE'. > Msg 156, Level 15, State 1, Procedure uspGdet_temp, Line 22 > Incorrect syntax near the keyword 'ELSE'. > Msg 102, Level 15, State 1, Procedure uspGdet_temp, Line 25 > Incorrect syntax near 'END'. > > > Can someone please help? > > tia, > AbbA > >
From: Geoff Schaller on 3 Feb 2010 04:05 You will need to use dynamic SQL. Generate the full statement as a string and then EXEC the string. Geoff "Abba" <sql_help(a)aaa.com> wrote in message news:OfxBBtKpKHA.3776(a)TK2MSFTNGP04.phx.gbl: > Hello, > > [SQL2008] > > CREATE PROCEDURE [dbo].[uspGdet_temp] > @filby Varchar(10), > @filval Varchar(50) > AS > BEGIN > SET NOCOUNT ON > IF @filby = 'AcNum' > BEGIN > SELECT * FROM Acc > WHERE [AcNum] LIKE @filval + '%' > END > ELSE > BEGIN > SELECT * FROM Reg > WHERE CASE @filby > WHEN 'Eml' THEN [EMAIL] LIKE @filval + '%' > WHEN 'FNme' THEN [FirstName] LIKE @filval + '%' > WHEN 'LNme' THEN [LastName] LIKE @filval + '%' > WHEN 'CmpNme' THEN [CompanyName] LIKE @filval + '%' > WHEN 'LoginId' THEN [LoginID] LIKE @filval + '%' > WHEN 'AcntLog' THEN [ID] IN (SELECT RegID FROM DAcc > WHERE DAccNum = @filval) > ELSE 1=1 > END > END > END > > > While executing this, Iam getting this error: > Msg 156, Level 15, State 1, Procedure uspGdet_temp, Line 16 > Incorrect syntax near the keyword 'LIKE'. > Msg 156, Level 15, State 1, Procedure uspGdet_temp, Line 22 > Incorrect syntax near the keyword 'ELSE'. > Msg 102, Level 15, State 1, Procedure uspGdet_temp, Line 25 > Incorrect syntax near 'END'. > > > Can someone please help? > > tia, > AbbA
From: Frank Uray on 3 Feb 2010 04:30 Hi Well, usage of CASE in WHERE is not that easy. Here I have made you a sample of what you can do: DECLARE @Test varchar(10) SET @Test = 'SCHWEIZ'; SELECT * FROM _META._common.GeoData WHERE COUNTRY = CASE WHEN @Test = 'SCHWEIZ' THEN 'Schweiz' WHEN @Test = 'DEUTSCHLAND' THEN 'Bundesrepublik Deutschland' ELSE '' END I guess this is not exactly what you want to do but maybe it helps. In your case I would thy to do it with OR instead of using CASE. If you script me your tables I can try to do it. Regards Frank Uray "Abba" wrote: > Hello, > > [SQL2008] > > CREATE PROCEDURE [dbo].[uspGdet_temp] > @filby Varchar(10), > @filval Varchar(50) > AS > BEGIN > SET NOCOUNT ON > IF @filby = 'AcNum' > BEGIN > SELECT * FROM Acc > WHERE [AcNum] LIKE @filval + '%' > END > ELSE > BEGIN > SELECT * FROM Reg > WHERE CASE @filby > WHEN 'Eml' THEN [EMAIL] LIKE @filval + '%' > WHEN 'FNme' THEN [FirstName] LIKE @filval + '%' > WHEN 'LNme' THEN [LastName] LIKE @filval + '%' > WHEN 'CmpNme' THEN [CompanyName] LIKE @filval + '%' > WHEN 'LoginId' THEN [LoginID] LIKE @filval + '%' > WHEN 'AcntLog' THEN [ID] IN (SELECT RegID FROM DAcc > WHERE DAccNum = @filval) > ELSE 1=1 > END > END > END > > > While executing this, Iam getting this error: > Msg 156, Level 15, State 1, Procedure uspGdet_temp, Line 16 > Incorrect syntax near the keyword 'LIKE'. > Msg 156, Level 15, State 1, Procedure uspGdet_temp, Line 22 > Incorrect syntax near the keyword 'ELSE'. > Msg 102, Level 15, State 1, Procedure uspGdet_temp, Line 25 > Incorrect syntax near 'END'. > > > Can someone please help? > > tia, > AbbA > > > . >
From: Frank Uray on 3 Feb 2010 04:40
Here some additional informations: I am against using CASE expressions in the WHERE clause of a query to express conditional logic. I prefer to have everything translated to simple AND's, OR's and NOT's to keep things a) portable, b) easier to read and c) efficient. Learning some good boolean logic techniques will go a long way towards making your queries more efficient, and you won't need to rely on CASE's and other methods of doing conditional logic. First off, when I say “conditional logic”, I am talking about something like this: “If A then B” where A and B are both conditions. For example, in a WHERE clause, you might want to implement a condition like this: “If (@ReturnAll <>1) THEN (EmpID = @EmpID)” To express this logic in the WHERE clause, many people might code it like this: WHERE EmpID = CASE WHEN @ReturnAll<>1 THEN @EmpID ELSE EmpID END However, this is kind of counter-intuitive (why should we check that EmpID = EmpID ?) and can be really tough to implement when the condition spans more than 1 column in the table (you need multiple CASE's). Also, if EmpID is null this will fail. The alternative is to translate the condition into a regular boolean expression using only AND, OR and NOT. The logical translation of “IF A then B” is: “(Not A) or B” If you work it out on paper, you will see it makes sense. To translate our WHERE clause requirement using the above logic, it becomes: WHERE (@ReturnAll =1) OR (EmpID = @EmpID) We are saying if @ReturnAll is 1, don't bother evaluating more of the condition -- return the row because the expression is TRUE. But if @ReturnAll <> 1, then EmpID must be equal to @EmpID for the condition to be true and the row to be returned. Exactly the conditional logic we wish to express. To show another example, suppose we wish to say: “IF @Filter=1 THEN Date= @Date and Cust= @Cust and Emp= @Emp” Expressing this in a CASE clause results in: WHERE Date = CASE WHEN @Filter=1 THEN @Date ELSE Date END AND Cust = CASE WHEN @Filter=1 THEN @Cust ELSE Cust END AND Emp = CASE WHEN @Filter=1 THEN @Emp ELSE Emp END A little hard to read and quite inefficient -- all 3 case expressions must be evaluated for each row in the result set. Without CASE, we get: WHERE @Filter<>1 OR (Date= @Date and Cust= @Cust and Emp= @Emp) Much eaiser to read and maintain, and faster -- if @Filter <>1, the rest of the expression can be ignored by the optimizer. (This is in fact what SQL Server does).* Finally, to express: IF A THEN B ELSE C you would write it as: ((Not A) or B) AND (A or C) a little harder, but it does the job! No need for CASE in the WHERE clause .... trust me ! (of course, you may need CASE to manipulate some columns or expressions in the WHERE, just don't use it for boolean logic). ________________________________________ *To test this, just write: SELECT 1 WHERE 1=1 OR (1/0)=0 or Null=Null. Notice that the “1” is returned w/o generating an error, and also the Null=Null check has no effect on the results. "Abba" wrote: > Hello, > > [SQL2008] > > CREATE PROCEDURE [dbo].[uspGdet_temp] > @filby Varchar(10), > @filval Varchar(50) > AS > BEGIN > SET NOCOUNT ON > IF @filby = 'AcNum' > BEGIN > SELECT * FROM Acc > WHERE [AcNum] LIKE @filval + '%' > END > ELSE > BEGIN > SELECT * FROM Reg > WHERE CASE @filby > WHEN 'Eml' THEN [EMAIL] LIKE @filval + '%' > WHEN 'FNme' THEN [FirstName] LIKE @filval + '%' > WHEN 'LNme' THEN [LastName] LIKE @filval + '%' > WHEN 'CmpNme' THEN [CompanyName] LIKE @filval + '%' > WHEN 'LoginId' THEN [LoginID] LIKE @filval + '%' > WHEN 'AcntLog' THEN [ID] IN (SELECT RegID FROM DAcc > WHERE DAccNum = @filval) > ELSE 1=1 > END > END > END > > > While executing this, Iam getting this error: > Msg 156, Level 15, State 1, Procedure uspGdet_temp, Line 16 > Incorrect syntax near the keyword 'LIKE'. > Msg 156, Level 15, State 1, Procedure uspGdet_temp, Line 22 > Incorrect syntax near the keyword 'ELSE'. > Msg 102, Level 15, State 1, Procedure uspGdet_temp, Line 25 > Incorrect syntax near 'END'. > > > Can someone please help? > > tia, > AbbA > > > . > |