Prev: Optimizer on SQL 2005
Next: Restore using MDF and LDF
From: Hugo Kornelis on 3 Feb 2010 08:00
On Wed, 3 Feb 2010 14:00:47 +0530, Abba wrote:

>Can someone please help?

Hi Abba,

Others have already pointed out the problem. The best solution (IMO)
would be to use somthing like this:

IF @filby = 'AcNum'
BEGIN;
SELECT * FROM Acc
WHERE AcNum LIKE @filval + '%';
END;
ELSE IF @filby = 'Eml'
BEGIN;
SELECT * FROM Reg
WHERE EMAIL LIKE @filval + '%';
END;
(...)
ELSE IF @filby = 'AcntLog'
BEGIN;
SELECT * FROM Reg
WHERE ID IN (SELECT RegID FROM DAcc WHERE DAccNum = @filval);
END;
ELSE
BEGIN;
SELECT * FROM Reg;
END;

Also note that SELECT * should not be used in production code; always
supply a column list instead.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
From: Paul Shapiro on 3 Feb 2010 08:13
"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'.

Instead of @filterBy and @filterValue, you could use a separate parameter
for each potential filter, with the parameters all defaulted to Null and
your code ignoring the value if it's null. That would also allow multiple
active filters in a request. The SQL would be something like:

Create Procedure
@emailFilter Varchar(50) = Null
,@firstnameFilter Varchar(50) = Null
....
Select * From Reg
Where
@emailFilter Is Null Or [Email] Like @emailFilter + '%'
And @firstnameFilter Is Null Or [FirstName] Like @firstnameFilter + '%'
...

From: Plamen Ratchev on 3 Feb 2010 09:45
Hugo already provided the best solution to this. Here is just a way to write it using a CASE expression:

SELECT <columns>
FROM Reg
WHERE CASE WHEN @filby = 'Eml' AND [EMAIL] LIKE @filval + '%' THEN 'T'
WHEN @filby = 'FNme' AND [FirstName] LIKE @filval + '%' THEN 'T'
WHEN @filby = 'LNme' AND [LastName] LIKE @filval + '%' THEN 'T'
WHEN @filby = 'CmpNme' AND [CompanyName] LIKE @filval + '%' THEN 'T'
WHEN @filby = 'LoginId' AND [LoginID] LIKE @filval + '%' THEN 'T'
WHEN @filby = 'AcntLog' AND [ID] IN (SELECT RegID FROM DAcc WHERE DAccNum = @filval) THEN 'T'
ELSE 'F'
END = 'T';

--
Plamen Ratchev
http://www.SQLStudio.com
From: Abba on 3 Feb 2010 21:30
Thanks Uri.
>> One option is to write IF .. ELSE blocks
Thats the existing code. Iam trying to see if CASE can be used...

"Uri Dimant" <urid(a)iscar.co.il> wrote in message
news:u9ort8KpKHA.4648(a)TK2MSFTNGP06.phx.gbl...
> 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: Abba on 3 Feb 2010 21:31
That would mean I may have to give explicit SELECT perms.

"Geoff Schaller" <geoffx(a)softxwareobjectives.com.au> wrote in message
news:k%aan.5376$pv.1577(a)news-server.bigpond.net.au...
> 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
>


First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Optimizer on SQL 2005
Next: Restore using MDF and LDF