sql-query with parameter-urgent

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)

sql-query with parameter-urgent

Author

Topic

sanjayanthan
Starting Member

5 Posts

Posted - 2002-07-12 : 05:02:27

i want to pass more than one value for a particular field name patient_id so i choose "IN" operator, it is not working.
I know i have problem in assigning at @patid value.
how to assign ?pl help me
since patient_id column is a string

declare @patid varchar(20)
set @patid ='pat57','pat19'
Select Patient_VisitID From Patient_Visit Where Patient_Id in (@patid)

regards
sanjayanthan

sanjayanthan

rksingh024
Yak Posting Veteran

56 Posts

Posted - 2002-07-12 : 05:44:21

Many times this problem is asked for dynamic in clause. Latest of this is

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=17590

Use sql in this way...
----------------------------
set quoted_identifier off
declare @patid varchar(20) ,
@sql varchar(1000)

set @patid ="'pat57','pat19'"
set @sql = "Select Patient_VisitID From Patient_Visit Where Patient_Id in (" + @patid + ")"

exec (@sql)

Ramesh

Edited by - rksingh024 on 07/12/2002 05:58:17

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-12 : 08:07:50

You can use the charindex function to do this without dynamic sql . . .


...
where charindex(patient_id, @patid)

depending on the datatype and nullability of patient_id you may you may need to do some convert() and isnull() stuff, but otherwise you should be able to bask in the enjoyment of a cached execution plan!!

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources