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)
 Another transact sql tricky question

Author  Topic 

gamaz
Posting Yak Master

104 Posts

Posted - 2010-05-13 : 13:28:07
OK,
Here is the following select statement I have

SELECT cust_num,inv_num,type,amount,misc_charges,sales_tax,sales_tax_2,freight,inv_date,due_date,acct_unit3
FROM artran
where artran.cust_num between @StartingCustnum and @EndingCustnum
and artran.inv_date between @StartingInvDate and @EndingInvDate
and CHARINDEX(artran.type, @type) <> 0
and ((artran.type = 'D' and artran.inv_num = '0') or artran.inv_num <> '0')


@type is a parameter to be passed. I have three types I, D and P.
Now the stored procedure associated works only if I pass either I or D or P. How does one not pass a paramter to @type so that all the three types are returned in the final result set?


Currently if I skip the @type parameter then the final result of the associated stored procedure is now row. I appreciate any help.
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 13:34:08
same as before

and (CHARINDEX(artran.type, @type) <> 0 OR @type IS NULL)

make NULL default value for @type

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

gamaz
Posting Yak Master

104 Posts

Posted - 2010-05-13 : 13:42:15
Excellent Visakh. I am getting the grasp of it now.
Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 13:53:14
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -