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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-02-01 : 10:12:18
|
| Arun submitted "Arun writes "I have a Error_Message table with Error_Code and Description column. I need to fetch descriptions based on one or more error codes that I capture on the front end. Here is the SP I wrote that works when I pass multiple codes thru my VB program. But the same fails when I pass only one code.Create proc GetErrorDesc@CodeListAsSelect Description from Error_Message where Error_Code IN (@CodeList)VB Code:dim sCodeList as stringdim rs as new ADODB.RecordsetsCodeList = "'E001', 'E002', 'E003'"rs = Conn.Execute ("Exec GetErrorDesc """ & sCodeList & """")Right now I am using length of CodeList and using two separate SQLs within the SP as work around. Q: Is there a better way of dealing with this issue? SP that works:Create proc GetErrorDesc@CodeListAsIf Len(@CodeList) < 8Select Description from Error_Message where Error_Code = @CodeListElse Select Description from Error_Message where Error_Code IN (@CodeList)Also, my SP gives "length more than 128 characters" error when my codelist is too long. Q: Is there a workaround for the lenght problem?I do not like to use string functions within SP to handle this issue. Please let me know if there is a better way."" |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-02-01 : 11:57:16
|
| Try:Select Description from Error_Message where CHARINDEX(Error_Code,@CodeList) <> 0There are other examples on this website; search for CSV. There's a nice function somewhere that turns a CSV list into a table of values which is more efficient. But because your table of possible error messages probably isn't that big, the above should work find.- Jeff |
 |
|
|
|
|
|
How to pass string type IN criteria as parameter?