searched CASE and syntax error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

searched CASE and syntax error

Author

Topic

pitolo
Starting Member

2 Posts

Posted - 2010-02-23 : 23:12:35

I wrote this simple CASE, and Management Studio Express show me error

"Incorrect syntax near '='."

I have no idea what I do wrong.

use builder
go
alter proc spSelect
@imie varchar(30),
@nazwisko varchar(30),
@pesel numeric(11,0)

as
declare @minn int
select @minn = 1

select @imie, @nazwisko =
CASE WHEN (@imie is not NULL and datalength(@imie)>@minn) then imie = @imie
WHEN (@nazwisko is not NULL and datalength(@nazwisko)>@minn) then nazwisko = @nazwisko
WHEN (@pesel is not null and datalength(@pesel)>@minn)
END
from klient

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-23 : 23:30:46

your syntax for CASE WHEN is wrong.

I don't know the logic of your code to make the changes for you.

Please refer to the Books Online for correct syntax
http://msdn.microsoft.com/en-us/library/ms181765.aspx

anyway, it should be something like


select @imie, 
       @nazwisko =
        CASE WHEN (@imie is not NULL and datalength(@imie)>@minn) then @imie
             WHEN (@nazwisko is not NULL and datalength(@nazwisko)>@minn) then @nazwisko
             WHEN (@pesel is not null and datalength(@pesel)>@minn) then <some value here>
             END
from klient

KH
[spoiler]Time is always against us[/spoiler]

pitolo
Starting Member

2 Posts

Posted - 2010-02-24 : 00:58:28

What I wanted to do.

I receive values of these three parameters

@imie varchar(30),
@nazwisko varchar(30),
@pesel numeric(11,0) )

from C# program, and a I want to make simple select with them, just like "select imie, nazwisko, pesel from klient).
But for first I have to check them, they are not NULL or empty. Some of them can be empty and some not.
If I execute select with any empty values a I'll get empty reply.
I made it with (if else)
--------------------------------------------------------
go
alter proc spSelect
@imie varchar(30) = null,
@nazwisko varchar(30) = null,
@pesel numeric(11,0) = null

as

if((@imie is not NULL and datalength(@imie)> 1)
and (@nazwisko is not NULL and datalength(@nazwisko)>1)
and (@pesel is not null and datalength(@pesel)>1))
begin
select * from klient where imie = @imie and nazwisko = @nazwisko and pesel = @pesel
end

else
if((@imie is not NULL and datalength(@imie)>1)
and (@nazwisko is not NULL and datalength(@nazwisko)>1) )
begin
select * from klient where imie = @imie and nazwisko = @nazwisko
end

else
if(@imie is not NULL and datalength(@imie)>1)
and (@pesel is not null and datalength(@pesel)>1)
begin
select * from klient where imie = @imie and pesel = @pesel
end

else
if(@nazwisko is not null and datalength(@imie)>1)
and (@pesel is not null and datalength(@pesel)>1)
begin
select * from klient where nazwisko = @nazwisko and pesel = @pesel

----------------------------------------------------

, but if it'll be more parameters, it'll be definitely more combinations.

these are parameters in store procedure:
@imie varchar(30),
@nazwisko varchar(30),
@pesel numeric(11,0)

These are names of columns in table:
imie varchar(30),
nazwisko varchar(30),
pesel numeric(11,0)

Any idea? Thanks for help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-24 : 01:06:03

[code]
ALTER proc spSelect

@imie varchar(30) = NULL,
@nazwisko varchar(30) = NULL,
@pesel numeric(11,0) = NULL

AS
BEGIN

SELECT *
FROM klient
WHERE (
@imie IS NULL
OR imie = @imie
)
AND (
@nazwisko IS NULL
OR nazwisko = @nazwisko
)
AND (
@pesel IS NULL
OR pesel = @pesel
)

END
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources