simple query of store proc- need help

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)

simple query of store proc- need help

Author

Topic

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-01 : 10:48:09

i have created one table-there r 7rows :

create table topictab
(
t1 char(11),
t2 char(11),
t3 char(11),
t4 char(11),
t5 char(11),
t6 char(11),
t7 char(11)
)

values r :

insert into topictab
(t1,t2,t3,t4,t5,t6,t7)
values
('ch', 'ch+ha','ch+hp','ch+yri','ch-gui','CH+YG1','CH+HA-RI')

now i want result like if @indx=0 then t1,t2,t3,t4,t5,t6
else if @indx=1 then t7

so i used like this in store procedure

CREATE PROCEDURE P_topcod1
@INDX INT
AS
BEGIN
select
a,b,c,
TOPIC = CASE @INDX
WHEN '0' THEN t in (t1,t2,t3,t4,t5,t6)
WHEN '1' THEN t.t7
END,
d,e(rest of the columns)
from taba a
left outer join topictab t
on t.t1=a.a1

but it gives me error that :
Incorrect syntax near the keyword 'in'.

so can u tell me correct way.

thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-01 : 10:53:37

Didnt understand your requirement here? how do you want to set value of TOPIC? can you explain? also why you are putting'1','0'...for integer parameter @INDX comparison?

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-01 : 11:06:34

quote:
Originally posted by visakh16

Didnt understand your requirement here? how do you want to set value of TOPIC? can you explain? also why you are putting'1','0'...for integer parameter @INDX comparison?

well, i have o set value of topic..if indx=0 then topic will like 'ch', 'ch+ha','ch+hp','ch+yri','ch-gui','CH+YG1', else if indx=1 then 'CH+HA-RI'..i can write like topic like 'ch%'...but it will give me 'ch+ha-ri' also which i have to use in else part...
and i will use @indx without quotation mark as its int..thanks for that..but u know i didn't get any error of that..

anyways, now do u understand the requirement?? can u help me plz?

thanks a lot!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-01 : 11:15:09

Ok. try like this then:-

CREATE PROCEDURE P_topcod1
@INDX INT
AS
BEGIN
select
a,b,c,
TOPIC = CASE @INDX
WHEN 0 THEN t =t1 + ',' + t2 + ',' + t3 + ',' + t4 + ',' + t5 + ',' + t6
WHEN 1 THEN t.t7
END,
d,e(rest of the columns)
from taba a
left outer join topictab t
on t.t1=a.a1
GO

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-01 : 11:20:44

here it says incorrect syntax near '='

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-01 : 11:27:43

quote:
Originally posted by ri16

here it says incorrect syntax near '='

i did like this:

TOPIC = CASE @INDX
WHEN '0' THEN (t.t1 + ',' + t.t2 + ',' + t.t3 + ',' + t.t4 + ',' + t.t5 + ',' + t.t6)
WHEN '1' THEN t.t7
END,

then it says
String or binary data would be truncated.
The statement has been terminated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-01 : 11:45:35

quote:
Originally posted by ri16

quote:
Originally posted by ri16

here it says incorrect syntax near '='

i did like this:

TOPIC = CASE @INDX
WHEN '0' THEN '(' +t.t1 + ',' + t.t2 + ',' + t.t3 + ',' + t.t4 + ',' + t.t5 + ',' + t.t6 + ')'WHEN '1' THEN t.t7
END,

then it says
String or binary data would be truncated.
The statement has been terminated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources