Hello — This probably doesn’t belong here, but here I am. I know that Office products to not necessarily include MSSQL Server, but here is a call to my server from an old, reliable report writer that I am using. Basically, I set up some hard coded prompts that the user is required to fill, which in this case I call “WholeClientorStartNumswPerct_orX.”
I want to offer the user the opportunity to enter the entire 6-digit client name, any amount of digits (followed by the percent), or “X”, which I translate to a “%”, which the code seems to be okay with. Actually, I could leave the “X” off entirely, because I find if I merely enter a “%” directly, it gets everything, so I’ll probably change the user prompt and the below CASE statement before we even get to the “SELECT” statement, can just be discarded. However, I was curious, and the below works like a charm, but it *is* running a little slowly. So, do you think this sort of SQL is bad bad bad?
DECLARE @ClientBit varchar(6)
set @ClientBit = case upper(:WholeClientorStartNumswPerct_orX)
when ‘X’ then “%” — get any and all clients
else :WholeClientorStartNumswPerct_orX — get whatever else the user has entered
end
SELECT …. FROM ….. WHERE …
Then, in the ‘Where’ clause, I limit it to “Client like @ClientBit” which could be 123456, 1234%, or %.
I do do stored procedures, so I could go that route, but for now.. just wanted to get any SQL-savvy people’s take on it.
thx
Pat
