query with different data types

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 2000 Forums

SQL Server Development (2000)

query with different data types

Author

Topic

alecken
Starting Member

2 Posts

Posted - 2008-02-19 : 15:59:01

I need to add a select statement to a store procedure that's been written.
It is a simple select except that MTypeID has data type int while @KeyList is the result from a different query with data type varchar.

select distinct MType from V_ObjMed where MTypeID in (@KeyList)

Since MSSQL doesn't like this, I keep getting error so to get around it,I'm doing it this way:

EXEC('select distinct MType from V_ObjMed where MTypeID in ('+KeyList+')')

That, get the query running just find BUT unfortunately I need to apply more filters to this query and this is the task that is very difficult for me.
When I added the following to the select statement I keep getting error saying that @OrderID is not declare.
orderID=@OrderId and PlanID=@PlanID and LangID=@LangID .

Here is my complete code for this testing:
Declare @KeyList varchar (100)
set @KeyList='70,37'

Declare @OrderID int
set @OrderID=1

Declare @PlanID int
set @PlanID=2

Declare @LangID int
set @LangID=1

EXEC('select distinct MType from V_ObjMed where
orderID=@OrderId and PlanID=@PlanID and LangID=@LangID and MTypeID in ('+KeyList+')')

I can't run this completed query, keep getting error saying I have not declared the variable (@OrderID or PlanID, etc)

When I hardcode the value,for a testing, I get the result:
EXEC('select distinct MType from V_ObjMed where
orderID=1 and PlanID=2 and LangID=1 and MTypeID in ('+KeyList+')')

Can anyone help me?

Thanks,
Alec

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-20 : 00:21:40

use like this

EXEC('select distinct MType from V_ObjMed where
orderID='+ CAST(@OrderId AS varchar(4))+ ' and PlanID='+ CAST(@PlanID AS varchar(4))+' and LangID='+ CAST(@LangID AS varchar(4))+' and MTypeID in ('+@KeyList+')')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-20 : 01:04:09

Why do you need dynamic sql when you dont pass object names as parameter?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-20 : 01:28:12

quote:
Originally posted by madhivanan

Why do you need dynamic sql when you dont pass object names as parameter?

Madhivanan

Failing to plan is Planning to fail

Thats right Madhi. It doesnt seem like OP need to use dynamic sql. This will be enough for him i guess

select distinct MType from V_ObjMed where
orderID=@OrderId and PlanID=@PlanID and LangID=@LangID and @keylist like '%'+CAST(MTypeID AS varchar(4))+ '%'

alecken
Starting Member

2 Posts

Posted - 2008-02-20 : 08:57:05

Thank you for all the responses.
I ran A (see below), I got an error saying: Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near 'CAST'.

Actually I like script A better but somehow it still doesn't work. I can't see what is wrong with cast and I'm not a MSSQL expert.

When I ran B (see below), (I modified the script a little bit, I changed MTypeID to @KeyList) and I got the result. For this test I got the result I wanted.

A.
EXEC('select distinct MType from V_ObjMed where
orderID='+CAST(@OrderId AS varchar(4))+ ' and PlanID='+CAST(@PlanID AS varchar(4))+' and LangID='+CAST(@LangID AS varchar(4))+' and MTypeID in ('+@KeyList+')')

B.
select distinct MType from V_ObjMed where
orderID=@OrderId and PlanID=@PlanID and LangID=@LangID and @keylist like '%'+CAST(@keylist AS varchar(4))+ '%'

Those parameters are hard coded because I'm testing the script separately from other queries within the stor proc.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-20 : 09:01:53

B
should be

select distinct MType from V_ObjMed where
orderID=@OrderId and PlanID=@PlanID and LangID=@LangID and ','+cast(MTypeId as varchar(4))+',' like '%,'+@keylist+ ',%'

If @keylist contains comma seperated values

Madhivanan

Failing to plan is Planning to fail

johnsql
Posting Yak Master

161 Posts

Posted - 2008-06-19 : 09:39:40

quote:
Originally posted by madhivanan

B
should be

select distinct MType from V_ObjMed where
orderID=@OrderId and PlanID=@PlanID and LangID=@LangID and ','+cast(MTypeId as varchar(4))+',' like '%,'+@keylist+ ',%'

If @keylist contains comma seperated values

Madhivanan

Failing to plan is Planning to fail

I checked your scripts but the returned DTS is nothing.
Any idea?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 10:24:48

quote:
Originally posted by johnsql

quote:
Originally posted by madhivanan

B
should be

select distinct MType from V_ObjMed where
orderID=@OrderId and PlanID=@PlanID and LangID=@LangID and ','+cast(MTypeId as varchar(4))+',' like '%,'+@keylist+ ',%'

If @keylist contains comma seperated values

Madhivanan

Failing to plan is Planning to fail

I checked your scripts but the returned DTS is nothing.
Any idea?

DTS? were you calling sp from a DTS?

johnsql
Posting Yak Master

161 Posts

Posted - 2008-06-19 : 11:48:15

quote:
Originally posted by visakh16

quote:
Originally posted by johnsql

quote:
Originally posted by madhivanan

B
should be

select distinct MType from V_ObjMed where
orderID=@OrderId and PlanID=@PlanID and LangID=@LangID and ','+cast(MTypeId as varchar(4))+',' like '%,'+@keylist+ ',%'

If @keylist contains comma seperated values

Madhivanan

Failing to plan is Planning to fail

I checked your scripts but the returned DTS is nothing.
Any idea?

DTS? were you calling sp from a DTS?

I run his script in SQL QA and I do not see any row to be returned. You can combine OP's data and the mentioned query and you can see the problem.
I do not know if there is an extra split procedure to be used instead of pattern matching with LIKE that seems not working.

any idea?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 12:00:21

quote:
Originally posted by johnsql

quote:
Originally posted by visakh16

quote:
Originally posted by johnsql

quote:
Originally posted by madhivanan

B
should be

select distinct MType from V_ObjMed where
orderID=@OrderId and PlanID=@PlanID and LangID=@LangID and ','+cast(MTypeId as varchar(4))+',' like '%,'+@keylist+ ',%'

If @keylist contains comma seperated values

Madhivanan

Failing to plan is Planning to fail

I checked your scripts but the returned DTS is nothing.
Any idea?

DTS? were you calling sp from a DTS?

I run his script in SQL QA and I do not see any row to be returned. You can combine OP's data and the mentioned query and you can see the problem.
I do not know if there is an extra split procedure to be used instead of pattern matching with LIKE that seems not working.

any idea?

How were you passing values for @Keylist?

johnsql
Posting Yak Master

161 Posts

Posted - 2008-06-19 : 13:15:28

quote:
Originally posted by visakh16

quote:
Originally posted by johnsql

quote:
Originally posted by visakh16

quote:
Originally posted by johnsql

quote:
Originally posted by madhivanan

B
should be

select distinct MType from V_ObjMed where
orderID=@OrderId and PlanID=@PlanID and LangID=@LangID and ','+cast(MTypeId as varchar(4))+',' like '%,'+@keylist+ ',%'

If @keylist contains comma seperated values

Madhivanan

Failing to plan is Planning to fail

I checked your scripts but the returned DTS is nothing.
Any idea?

DTS? were you calling sp from a DTS?

I run his script in SQL QA and I do not see any row to be returned. You can combine OP's data and the mentioned query and you can see the problem.
I do not know if there is an extra split procedure to be used instead of pattern matching with LIKE that seems not working.

any idea?

How were you passing values for @Keylist?

Here is the one from the OP
Declare @KeyList varchar (100)
set @KeyList='70,37'

cvipin
Yak Posting Veteran

51 Posts

Posted - 2008-06-19 : 14:20:04

Try this:

select distinct MType from V_ObjMed where
orderID=@OrderId and PlanID=@PlanID and LangID=@LangID and charindex( ','+cast(MTypeId as varchar(4))+',' , ','+@keylist+ ',') > 0

Note: Do not include any spaces in the comma separated params list.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources