存储过程-Switch语句? [英] Stored Procedures- Switch statements?

问题描述

基本上是为了减少代码量，我试图将搜索查询合并为一个过程而不是3.

用户可以按标题，描述或作者搜索报告.
当前过程看起来像这样

Basically in a bid to lower the amount of code I have im trying to combine my search query into one procedure instead of 3.

Users can search reports by Title,Description or Author.
The current procedure looks something like this

	(
	@SearchQuery NVARCHAR(100),
	@FolderID INT,
	@SearchType NVARCHAR(100)
	)
AS
BEGIN
	SELECT 
	ReportDetailsID,
        Name

	FROM 
	Reports
	WHERE

	FolderID = @FolderID AND
		
	CASE @SearchType
	WHEN ''Name'' THEN Name  LIKE ''%''+@SearchQuery+''%'' 
	WHEN ''Desc'' THEN Description  LIKE ''%''+@SearchQuery+''%'' 
	WHEN ''Author'' THEN CreatedBy  LIKE ''%''+@SearchQuery+''%'' 
	
END 

我的想法是，我运行该过程并为其提供一个字符串值，该字符串值是我要搜索的搜索类型，最后的where语句基于该选择

我该如何正确地做这种陈述?我的尝试是基于类似的SQL，但是它们没有问题的like

The idea was that I run the procedure and give it a string value of what kind of search it is I want to search by and the final where statement is based off that choice

How do I do this kind of statement properly? My attempt was based off similar SQL but they didn''t have the like element to the problem

推荐答案

您可以在SQL中包含所有选项，并使用布尔表达式来关闭不需要的内容:

You could include all options in the SQL and use boolean expressions to turn off the ones you don''t need:

<br />
SELECT ...<br />
FROM ...<br />
WHERE<br />
    ( @SearchType = 'Name' AND Name LIKE '%'+@SearchQuery+'%' )<br />
    OR<br />
    ( @SearchType = 'Desc' AND Description LIKE '%'+@SearchQuery+'%' )<br />
    OR<br />
        ...<br />

尼克

Nick

具有以下可能的解决方案:
优化条件WHERE子句:避免OR和CASE表达式 [ ^ ]
SQL WHERE子句:避免使用CASE，请使用布尔逻辑 [

Have at these possible solutions:
Optimizing Conditional WHERE Clauses: Avoiding ORs and CASE Expressions[^]
SQL WHERE clauses: Avoid CASE, use Boolean logic[^]

这篇关于存储过程-Switch语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！