SQL Server Stored Procedure: SP_Get_EOL with Dynamic WHERE Clause

{ "title": "SQL Server Stored Procedure: SP_Get_EOL with Dynamic WHERE Clause", "description": "This SQL Server stored procedure, SP_Get_EOL, retrieves data from the Aras PLM database with dynamic WHERE clause based on input parameters @yyzx (营运中心) and @pinhao (品号). Learn how to create a flexible stored procedure for efficient data retrieval.", "keywords": "SQL Server, stored procedure, dynamic WHERE clause, Aras PLM, SP_Get_EOL, @yyzx, @pinhao, 营运中心, 品号, data retrieval", "content": "CREATE PROCEDURE [dbo].SP_Get_EOL\n\t@yyzx NVARCHAR(20), --营运中心\n\t@pinhao NVARCHAR(128) --品号\nAS\nEXEC SP_PLM_COLPIVOT\n@TABLENAME = N'select t1.*,t2.FIELD_NAME,t2.FIELD_VALUE from Aras_PLM.innovator.LABEL_AND_EOL t1\nleft join Aras_PLM.innovator.EOL_INFO t2 on t2.SOURCE_ID = t1.id\nwhere 1 = 1\nand t1.eol_state in(''released'', ''in change'')\nand t1.old_or_new_signs = ''2''',\n@GROUPCOLUMN = N'KEYED_NAME',\n@JSTYPE = N'MAX',\n@ROW2COLUMN = N'FIELD_NAME',\n@ROW2COLUMNVALUE = N'FIELD_VALUE',\n@SQL_WHERE = N'where 1 = 1' +\n CASE WHEN @yyzx IS NOT NULL THEN ' and t1.yyxx = ' + @yyzx ELSE '' END +\n CASE WHEN @pinhao IS NOT NULL THEN ' and t1.pinhao = ' + @pinhao ELSE '' END;

In this modified code, conditions are added to the WHERE clause based on the values of the @yyzx and @pinhao parameters. If these parameters are not null, the corresponding condition is added to the WHERE clause.

To use this stored procedure, you can pass in the desired values for @yyzx and @pinhao. For example, to retrieve data for the '运营中心' (yyzx) 'ABC' and '品号' (pinhao) '1234567890', you would execute the following statement:

EXEC [dbo].SP_Get_EOL @yyzx = 'ABC', @pinhao = '1234567890'

This will retrieve data for all records that match the specified values for '运营中心' and '品号'.

This approach allows for greater flexibility and customization of your data retrieval process.