Where Clause Problem in Select Statement? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Where Clause Problem in Select Statement?

Author

Topic

Mits
Starting Member

48 Posts

Posted - 2004-08-02 : 06:36:56

Hi all,
Here is my Query in a stored procedure

@Type char(1),
@S1 char(1),
@FromDt datetime,
@ToDt datetime,
@NC char(4),
@InvStr as varchar(2000)

SELECT SUM(InvoiceItem.Net) AS TotalNet, SUM(InvoiceItem.[Base Cost] * InvoiceItem.Quantity) AS BaseCost, SUM(InvoiceItem.Net) - SUM(InvoiceItem.[Base Cost] * InvoiceItem.Quantity) AS BCProfit

FROM InvoiceItem,InvoiceStatus
WHERE InvoiceItem.[Invoice No] = InvoiceStatus.[Invoice No] And
(InvoiceStatus.[Date] between @FromDt and @ToDt) AND
(InvoiceStatus.Type = 'Invoice') AND
(InvoiceStatus.[Nominal Code] = @NC) AND
(InvoiceItem.[Stock Code] NOT IN ('NOWARRANTY','ACC/HOLD', 'CASH', 'SURCHARGE', 'D/CRD', 'TRANSURG',
'NOCHRGE', 'B/CHQ', 'C/CRD', 'P/CHQ')) AND
InvoiceStatus.[Invoice No] Not In(@InvStr)

Where @InvStr would be like '100023,100024,100025' list of invoice that i dont want to include.

How can i pass a list of invoice no seperated by comma? By passing as string it give me "Error converting data type varchar to numeric.".

Thanks

Mits

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-08-02 : 07:06:32

You would have to use dynamic sql for that.
You would do this by building a SQL String and then executing it.

Something like this:
[CODE]

DECLARE @SQLStr VARCHAR(4000)
SET @SQLStr = '
SELECT SUM(InvoiceItem.Net) AS TotalNet, SUM(InvoiceItem.[Base Cost] * InvoiceItem.Quantity) AS BaseCost, SUM(InvoiceItem.Net) - SUM(InvoiceItem.[Base Cost] * InvoiceItem.Quantity) AS BCProfit

FROM InvoiceItem,InvoiceStatus
WHERE InvoiceItem.[Invoice No] = InvoiceStatus.[Invoice No] And
(InvoiceStatus.[Date] between ''' + @FromDt + ''' and ''' + @ToDt + ''') AND
(InvoiceStatus.Type = ''Invoice'') AND
(InvoiceStatus.[Nominal Code] = ''' + @NC+ ''') AND
(InvoiceItem.[Stock Code] NOT IN (''NOWARRANTY'',''ACC/HOLD'', ''CASH'', ''SURCHARGE'', ''D/CRD'', ''TRANSURG'',
''NOCHRGE'', ''B/CHQ'', ''C/CRD'', ''P/CHQ'')) AND
InvoiceStatus.[Invoice No] Not In(' + @InvStr + ')'

exec(@SQLStr)
[/CODE]

Duane.

gpl
Posting Yak Master

195 Posts

Posted - 2004-08-02 : 07:51:08

Or see this article which avoids the whole dynamic SQL issue -> http://www.sqlteam.com/item.asp?ItemID=11499

By returning each entry in a string as a row.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-08-02 : 08:02:40

Great article.

I've put that in my Useful Scripts folder.

Duane.

Mits
Starting Member

48 Posts

Posted - 2004-08-02 : 09:23:56

thanks duane,
I will give it a go and see how it goes.

Mits

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources