0

I'm writing a Command for a Crystal Report that queries an SQL Database. The Command will use parameters/inputs that are generated from a different program. I've put parameters directly in Commands before, but this one has to be handled differently.

Said input will be a string that is numbers with an & in between such as this: "6&12&15", order is irrelevant in this case. For understanding purposes, we'll say that the numbers are product ID's and are unique. When a user wants to search for multiple products in this database, the string above will be how it looks.

I have used the following code in the past for non-number based strings and it works well because of how other fields are set up:

    CASE WHEN '{?WearhouseState}' = '' THEN 1
    WHEN CHARINDEX(Products.WearhouseState,'{?WearhouseState}',0)>0 THEN 1
    ELSE 0 
    END = 1

That code will search for the field's value as a substring essentially anywhere in the given input parameter, which works for things like a state because "Texas" is never going to be a substring of any other state. However, this doesn't work so well with numbers. For example, if a product has an ID of 3, then the search will return that record if the parameter is '31', which I do not clearly want (it would also return product 1 as well).

For the mean time, I have been splitting the string up with a delimiter in Crystal Reports which works fine, but slows down the overall time to create the document. Most of the parameters I use I tend to put right in the query and it drastically improves the speed. The Crystal code is as follows:

{?ProductID}="" or {Command.ProductID} in split({?ProductID},"&")

This works exactly as intended but again, time is of the essence. Any additional information can be provided. It is technically InterSystems SQL so keep that in mind because I know the commands/clauses can vary between SQL.

Dharman
  • 30,962
  • 25
  • 85
  • 135
ComplexTrig27
  • 1

1 Answers1

0

I'd do the split string operation in SQL Server instead of CR. See e.g. T-SQL split string for a working code sample. Note that this logic does not need to run as a function, but you could also include it directly in your CR command.

mweber
  • 670
  • 7
  • 18
  • Unfortunately, I have to use an older form of SQL, and split_string does not exist. – ComplexTrig27 Oct 06 '22 at 19:39
  • I'm aware of that. The SO question linked above has a user defined function that splits strings – you could use this as a user defined function on your server, or just include the code with your command in CR. No SQL Server built-in function needed. – mweber Oct 07 '22 at 12:11