, want to write A stored procedure, the existing list A few rows of data number column KQ1, respectively, KQ101, KQ102, KQ10101, KQ10, KQ1001, KQ100101 (actually contains the relationship between the level of location, location, 2 tertiary location, but the table was built when the primary location number digits, have A plenty of 1 or 2 digits, conditions are not allowed to change again), and A parameter @ code, when announced to participate as' 1 ', just want to query KQ1, KQ101, KQ102, KQ10101, when announced to participate as "10", just want to check KQ10 KQ1001, KQ100101, advice on how to write,

CodePudding user response:

To deal with this very bad design, feeling is only get a location corresponding to the other table,
What number is one digit location, which is 2 digits, configured in advance, query time to use this form to do auxiliary to get up,

CodePudding user response:

Or you can try it with regular expressions can help you, that I'm not familiar with, you search online to see,

CodePudding user response:

Don't quite understand what you mean, so can you

 

CREATE PROCEDURE dbo. InventoryLocationEnquiry 
@ code varchar (10) 
AS the SET NOCOUNT ON 

IF @ code='1' 
Select * From A Where clause LocationNo in (' KQ1 ', 'KQ101', 'KQ102', 'KQ10101') 

IF @ code='10' 
Select * From A Where clause LocationNo in (' KQ10 ', 'KQ1001', 'KQ100101') 


GO