Dynamic input

Transact-SQL
1

Looking to update this working SQL from Mike01 with a dynamic input list, say from a .xlsx, .csv or .txt file with one parameter in one a single column of PN101, PN102, etc. Normally, no commas, only in .csv or .txt. Thanks.

MS SQL Server 2017 Schema Setup:

 Create table Sample (
        Item int,
        Part_Num varchar(6),
        Replaced_Part_Num varchar(5),
        Part_Noun varchar(30)
    )
    Insert into Sample
    select 1 ,'PN501','PN401', 'PartName01' union
    select 2 ,'PN502','PN402', 'PartName02' union
    select 3 ,'PN503','PN403', 'PartName03' union
    select 4 ,'PN401','PN301', 'PartName11' union
    select 5 ,'PN3402','PN302', 'PartName12' union
    select 6 ,'PN403','PN303', 'PartName13' union
    select 7 ,'PN2301','PN201', 'PartName21' union
    select 8 ,'PN302','PN202', 'PartName22' union
    select 9 ,'PN303','PN203', 'PartName23' union
    select 10 ,'PN201','PN101', '2_rpld_pn' union
    select 11 ,'PN202','PN102', '3_rpld_pn' union
    select 12 ,'PN203','PN103', '4_rpld_pn'
;

Query 1:

with cte as
 (   SELECT
        [S].[Item]
        , [S].[Part_Num]
        , [S].[Replaced_Part_Num]
        , [S].[Part_Noun]
    FROM
        [Sample] AS [S]
    WHERE
        [S].[Replaced_Part_Num] = 'PN102'
	union all
	  SELECT
        [S].[Item]
        , [S].[Part_Num]
        , [S].[Replaced_Part_Num]
        , [S].[Part_Noun]
    FROM
        [Sample] AS [S]
			join cte c
				on s.Replaced_Part_Num = c.Part_Num		)

	select * from cte

[Results][2]:

| Item | Part_Num | Replaced_Part_Num |  Part_Noun |
|------|----------|-------------------|------------|
|   11 |    PN202 |             PN102 |  3_rpld_pn |
|    8 |    PN302 |             PN202 | PartName22 |
|    5 |   PN3402 |             PN302 | PartName12 |
2

Hermie,

not sure I understand what you want to update. Can you provide a little more detail as to what you want to pass in dynamically?

3

trying to create a procedure to take in a variable string, Replace_Part_Num, and pass that string to the. Assume format of input are excel column, csv or txt file with Replaced_Part_Num listed. The input string will need to add single quote around each data (i.e. PN101 to 'PN101', and concatenate them as an input string) Hope this makes sense.

Create Procedure spGetReplacePN
@Replaced_pnString varchar(max)
As
Begin
with cte as
( SELECT
[S].[Item]
, [S].[Part_Num]
, [S].[Replaced_Part_Num]
, [S].[Part_Noun]
FROM
[Sample] AS [S]
WHERE
[S].[Replaced_Part_Num] = @Replaced_pnString
union all
SELECT
[S].[Item]
, [S].[Part_Num]
, [S].[Replaced_Part_Num]
, [S].[Part_Noun]
FROM
[Sample] AS [S]
join cte c
on s.Replaced_Part_Num = c.Part_Num )

	select * from cte
    End
4

look into the following function (DelimitedSplit8k)

use sqlteam
go


if OBJECT_ID('Sample') is not null
	drop table [Sample]

Create table Sample (
        Item int,
        Part_Num varchar(6),
        Replaced_Part_Num varchar(5),
        Part_Noun varchar(30)
    )
    Insert into Sample
    select 1 ,'PN501','PN401', 'PartName01' union
    select 2 ,'PN502','PN402', 'PartName02' union
    select 3 ,'PN503','PN403', 'PartName03' union
    select 4 ,'PN401','PN301', 'PartName11' union
    select 5 ,'PN3402','PN302', 'PartName12' union
    select 6 ,'PN403','PN303', 'PartName13' union
    select 7 ,'PN2301','PN201', 'PartName21' union
    select 8 ,'PN302','PN202', 'PartName22' union
    select 9 ,'PN303','PN203', 'PartName23' union
    select 10 ,'PN201','PN101', '2_rpld_pn' union
    select 11 ,'PN202','PN102', '3_rpld_pn' union
    select 12 ,'PN203','PN103', '4_rpld_pn'
	go


alter Procedure spGetReplacePN
@Replaced_pnString varchar(max)
As
begin
	with cte as
	( 
	SELECT
	[S].[Item]
	, [S].[Part_Num]
	, [S].[Replaced_Part_Num]
	, [S].[Part_Noun]
	FROM [Sample] AS [S]
	 join (
			select * from  dbo.DelimitedSplit8k(@Replaced_pnString, ',')
		  ) split
	on [S].[Replaced_Part_Num] = split.Item
)
select * from cte
end
go


declare @inputparm varchar(max) = 'PN501,PN502,PartName01,PN401'

exec spGetReplacePN @inputparm;
5

hi hermie

hope this helps !!!

Please click arrow to the left for Create Proc Script

Create Proc 
CREATE PROCEDURE [spGetReplacePN]
    @Replaced_pnString VARCHAR(MAX)
AS
    BEGIN

        CREATE TABLE [#Sample]
             (
                 [Item] INT
                 , [Part_Num] VARCHAR(6)
                 , [Replaced_Part_Num] VARCHAR(5)
                 , [Part_Noun] VARCHAR(30)
             );

        INSERT INTO [#Sample]
        SELECT  1, 'PN501', 'PN401', 'PartName01'
        UNION
        SELECT  2, 'PN502', 'PN402', 'PartName02'
        UNION
        SELECT  3, 'PN503', 'PN403', 'PartName03'
        UNION
        SELECT  4, 'PN401', 'PN301', 'PartName11'
        UNION
        SELECT  5, 'PN3402', 'PN302', 'PartName12'
        UNION
        SELECT  6, 'PN403', 'PN303', 'PartName13'
        UNION
        SELECT  7, 'PN2301', 'PN201', 'PartName21'
        UNION
        SELECT  8, 'PN302', 'PN202', 'PartName22'
        UNION
        SELECT  9, 'PN303', 'PN203', 'PartName23'
        UNION
        SELECT  10, 'PN201', 'PN101', '2_rpld_pn'
        UNION
        SELECT  11, 'PN202', 'PN102', '3_rpld_pn'
        UNION
        SELECT  12, 'PN203', 'PN103', '4_rpld_pn';
        WITH [cte]
        AS (
               SELECT
                    [S].[Item]
                    , [S].[Part_Num]
                    , [S].[Replaced_Part_Num]
                    , [S].[Part_Noun]
               FROM
                    [#Sample] AS [S]
               WHERE
                    [S].[Replaced_Part_Num] = @Replaced_pnString
               UNION ALL
               SELECT
                    [S].[Item]
                    , [S].[Part_Num]
                    , [S].[Replaced_Part_Num]
                    , [S].[Part_Noun]
               FROM
                    [#Sample] AS [S]
                    JOIN [cte] AS [c]
                        ON [S].[Replaced_Part_Num] = [c].[Part_Num]
           )
        SELECT
            [cte].[Item]
            , [cte].[Part_Num]
            , [cte].[Replaced_Part_Num]
            , [cte].[Part_Noun]
        FROM
            [cte];

        DROP TABLE [#Sample];
    END;
GO

EXEC [spGetReplacePN] 'PN102';

DROP PROCEDURE [spGetReplacePN];

image

How do you want it ??

image
image820×170 3.05 KB