SELECT NOT IN (@paramater) - SQL Server Forums

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

Transact-SQL (2000)

SELECT NOT IN (@paramater)

Author

Topic

devonkyle
Starting Member

19 Posts

Posted - 2003-08-08 : 15:57:53

I am having problems with the following syntax

IF flag = 'Y'
Set @loantype = ('A','C','D','F','I','L','N','T','V')

select * from table where fieldA NOT IN @loantype

/*If the Flag is set to 'Y', select all records where fieldA does NOT equal any of the following values - the flag is being passed via a webform _ I am using NOT IN because the potential list of other values is infinite*/

My question is : how do I set '@loantype' to a sting that SELECT NOT IN can understand it? Cast, Convert?

Thank You
Devon

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-08 : 16:07:32

It is just SOOOOO WRONG that SQL 2000 doesn't support "IN @Variable" isn't it?

IN <rowset>

works

like: IN (SELECT MyCol FROM MyTable)

or hardcoded

like: IN ('A', 'B', 'C')

En'dat sumpin?

Rob wrote a Prettygood Purloinable Peerless Paper on Parsing that you should read:

[url]http://sqlteam.com/item.asp?ItemID=2652[/url]

You can write a user defined function to return a recordset from a @Variable using the technique in the article

like: IN dbo.UDF_MakeRowset(@MyVariable)

Sam

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-08 : 16:14:07

maybe this...?

Pass flag to sql proc

create proc dbo.GetLoanType @flag char(1)
as

IF @flag = 'Y'
begin
select * from table where fieldA NOT IN ('A','C','D','F','I','L','N','T','V')
end

begin
else condition here...
end

devonkyle
Starting Member

19 Posts

Posted - 2003-08-09 : 14:23:22

I like the UDF idea and the article you refer to should get me in the right direction - thanks Sam!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources