时间戳

首页 > 解决方案 > 将表名和 where 条件动态传递到存储过程中，并将所有数据放入 #table 以供将来按摩

sql-server - 将表名和 where 条件动态传递到存储过程中，并将所有数据放入 #table 以供将来按摩

问题描述

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[myreport]
    @lob_name VARCHAR(255), 
    @lob_id VARCHAR(255)
AS
BEGIN
    DECLARE @Sql NVARCHAR(MAX);

    SET @Sql ='SELECT  TOP 100 l.id as lid,l.last_name as customer_name, l.phone_mobile into #maintable
               FROM customer l with (nolock) 
               LEFT JOIN customer_cstm as c ON l.id = c.id_c 
               LEFT JOIN' + @lob_name +' AS m ON m.id=l.motor_id
               LEFT JOIN report_retention p ON p.policy_no = m.policy_no
               WHERE l.deleted = 0  AND (l.set_lob_type ='+ @lob_id+') 
                 AND p.lob ='+ @lob_name+'
               ORDER BY l.id ASC'
    /*
    Some massaging will here then will select #table
    */

    SELECT * #maintable

我正在传递这些无法在#table 中复制的东西以进行按摩

  1. 我需要动态传递表格
  2. 在 where 条件下也动态传递参数

标签： sql-server

解决方案

推荐阅读