###### tags: `Sql`,`2022`

# 使用 Stored Procedure 動態組成 SQL 查詢指令

CREATE PROCEDURE dbo.QueryProduct
 (
  @ProductID smallint
 )
 AS
 --設定三組sql變數
 DECLARE @statement nvarchar(256)
 DECLARE @orderby nvarchar(40)
 DECLARE @where nvarchar(256)
 
 --給予變數初始值
 SET @statement = 'SELECT [ProductID] FROM Table'
 SET @orderby = ' ORDER BY [ProductID] '
/*------- WHERE 的 if 範例 -------------- */
IF (@ProductID IS NULL)
  BEGIN
   SET @where = ' WHERE 1 = 1 '
  END
 ELSE
  BEGIN
   --如果資料庫的ProductID是int的話就不用在這邊使用CAST轉型了
   SET @where = ' WHERE [ProductID] = ' + CAST(@ProductID AS varchar) 
  
  END
-- 組合字串
SET @statement = @statement + @where + @orderby
 
 PRINT 'STATEMENT: ' + @statement
 EXEC sp_executesql @statement
RETURN

### Reference

[storedProcedure](https://ryanchen34057.github.io/2019/09/25/storedProcedure/)

[sp](https://coolmandiary.blogspot.com/2018/02/t-sql2stored-procedure.html)

{%hackmd BJrTq20hE %}