Multiple values in a parameter

  • Dear All,

    I want to use these values in the parameter for further usese, but it's giving me an error

    WITH ctevalues(recp_name, ing_name, qty) AS (

    values

    ('pasta pollo', 'paprika', '1')

    )

    INSERT INTO ing(recp_id, ing_name_id,qty)

    SELECT rec.recp_id, ing.ing_name_id,d.qty

    FROM ctevalues AS d

    INNER JOIN ing

    ON d.ing_name = ing.ing_name

    INNER JOIN recp AS rec

    ON d.recp_name=rec.recp_name

     

    Msg 156, Level 15, State 1, Line 58

    Incorrect syntax near the keyword 'values'.

  • Does this help?

    Provide the table structure of table ing if you want help inserting into it.

    ;WITH ctevalues AS 
    (
    SELECT
    'pasta pollo' AS recp_name,
    'paprika' ing_name,
    '1' AS qty
    )
    --Provide the table structure of ing if you want help inserting
    --INSERT INTO ing(recp_id, ing_name_id,qty)
    SELECT
    *
    FROM ctevalues


  • Pls check

    create table ing(recp_id int,ing_name_id int,qty text)

  • Thanks for your response.

    It helps but it's possible for multiple values

     

    ;WITH ctevalues (recp_name, ing_name, qty) AS (

    select

    'pasta pollo' as recp_name, 'paprika' as ing_name, '2 1/2 cup' as qty

    --, ('pasta pollo', 'rice', '200 gr')

    --, ('pasta pollo', 'onion', 'half')

    --, ('pasta pollo', 'chicken', '150 gr')

    --, ('pasta pollo', 'garlic', '2 cloves')

    )

    INSERT INTO ing(recp_id, ing_name_id,qty)

    SELECT rec.recp_id, ing_name.id,d.qty

    FROM ctevalues AS d

    INNER JOIN ing_name

    ON d.ing_name = ing_name.ing_name

    INNER JOIN recp AS rec

    ON d.recp_name=rec.recp_name

  • ;WITH ctevalues(recp_name, ing_name, qty) AS 
    (
    SELECT *
    FROM (VALUES
    ('pasta pollo', 'paprika', '2 1/2 cup'),
    ('pasta pollo', 'rice', '200 gr'),
    ('pasta pollo', 'onion', 'half'),
    ('pasta pollo', 'chicken', '150 gr'),
    ('pasta pollo', 'garlic', '2 cloves')) T(A,B,C)
    )
    INSERT INTO ing(recp_id, ing_name_id, qty)
    SELECT rec.recp_id, ing_name.id, d.qty
    FROM ctevalues AS d
    INNER JOIN ing_name
    ON d.ing_name = ing_name.ing_name
    INNER JOIN recp AS rec
    ON d.recp_name=rec.recp_name;
    create table ing
    (
    recp_id int NOT NULL,
    ing_name_id int NOT NULL,
    qty varchar(MAX) NULL
    )

    text is deprecated, so better to use varchar.

    https://learn.microsoft.com/en-us/sql/t-sql/data-types/ntext-text-and-image-transact-sql?view=sql-server-ver16

  • Why bother with a CTE?

    Just declare a table variable and use an INSERT VALUES clause and use that in the main insert block.

    Declare @ing table (recp_id int,ing_name_id int,qty varchar(max))

    Insert into @ing values
    ('pasta pollo','paprika', '2 1/2 cup'), ('pasta pollo', 'rice', '200 gr')
    , ('pasta pollo', 'onion', 'half')
    , ('pasta pollo', 'chicken', '150 gr')
    , ('pasta pollo', 'garlic', '2 cloves')

    INSERT INTO ing(recp_id, ing_name_id,qty)

    SELECT rec.recp_id, ing_name.id,d.qty

    FROM @ing AS d

    INNER JOIN ing_name

    ON d.ing_name = ing_name.ing_name

    INNER JOIN recp AS rec

    ON d.recp_name=rec.recp_name
  • I don't think you can use VALUES quite like that.

    This should work:

    WITH ctevalues (recp_name, ing_name, qty) AS (
    SELECT v.*
    FROM (
    VALUES
    ('pasta pollo', 'paprika', 1)
    ) v(recp_name, ing_name, qty)
    )
  • @Ant-Green: There seems to be a mis-match between the row values and the column definitions of the table variable. You have two integers that doesn't match up with the row values.

  • Is this a parameter in a stored procedure?

    If so, then use a table valued parameter and then it's very simple.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • @ Michael L John:

    Agreed, I like a typed table valued parameter for passing a set of rows to a stored procedure or between a stored procedure and a table function, for example. Makes it so much easier to have modular approach.

    • This reply was modified 1 year, 3 months ago by  kaj.
  • Thanks for everyone. code is working if i use it individually, on the other hand I use temp table in SP it's working too but i need to insert data in two conditions. if ing id and qty exist than it should fail and if ing id exists thn qty should add on that row if both fail then add new row,

    ALTER PROCEDURE INSERT_INGREDIENTS

    @Recipes_Name VARCHAR(255),@INGREDIENTS_NAME VARCHAR(255),@QUANTITY VARCHAR(255),@UOM VARCHAR(20)

    AS

    BEGIN

    CREATE TABLE #ING (Recipes_Name VARCHAR(255),INGREDIENTS_NAME VARCHAR(255),QUANTITY VARCHAR(255),UOM VARCHAR(20))

    INSERT INTO #ING (Recipes_Name,INGREDIENTS_NAME,QUANTITY,UOM) VALUES(@Recipes_Name,@INGREDIENTS_NAME,@QUANTITY,@UOM)

    END

    ----If both the qty and ingredients will match will give error-------

    IF EXISTS (SELECT R.ID,I.INGREDIENTS_NAME_ID,I.QUANTITY

    FROM INGREDIENTS I

    INNER JOIN

    Recipes_Name R ON R.ID=I.Recipes_Name_ID

    INNER JOIN

    INGREDIENTS_NAME N ON N.ID=I.INGREDIENTS_NAME_ID

    WHERE R.NAME=@Recipes_Name AND N.INGREDIENTS_NAME=@INGREDIENTS_NAME AND I.QUANTITY=@QUANTITY )

    BEGIN

    SELECT 'INGREDIENTS_NAME_ID IS ALREADY EXISTS'

    END

    ELSE

    BEGIN

    INSERT INTO INGREDIENTS(Recipes_Name_ID,INGREDIENTS_NAME_ID,QUANTITY,UOM,CREATED_BY,CREATED_DATE)

    SELECT R.ID,N.ID,T.QUANTITY,UOM.ID,SYSTEM_USER,GETDATE() FROM

    #ING AS T

    INNER JOIN

    Recipes_Name R ON R.NAME=T.Recipes_Name

    INNER JOIN

    INGREDIENTS_NAME N ON N.INGREDIENTS_NAME=T.INGREDIENTS_NAME

    INNER JOIN

    UOM ON UOM.UOM_NAME=T.UOM

    SELECT 'SUCCESS'

    DROP TABLE #ING

    END

    /*

    select * from INGREDIENTS

    EXEC INSERT_INGREDIENTS 'Kitchen Sink Cookies','butter',1,'CUP'

    */

  • Dear All,

    My one condition is working like if data exist than it should fail only one condition is pending that is "if data exists without qty, than qty should add in existing qty"

    ALTER PROCEDURE INSERT_INGREDIENTS

    @Recipes_Name VARCHAR(255),@INGREDIENTS_NAME VARCHAR(255),@QUANTITY VARCHAR(255),@UOM VARCHAR(20)

    AS

    BEGIN

    CREATE TABLE #ING (Recipes_Name VARCHAR(255),INGREDIENTS_NAME VARCHAR(255),QUANTITY VARCHAR(255),UOM VARCHAR(20))

    INSERT INTO #ING (Recipes_Name,INGREDIENTS_NAME,QUANTITY,UOM) VALUES(@Recipes_Name,@INGREDIENTS_NAME,@QUANTITY,@UOM)

    END

    IF EXISTS (SELECT R.NAME,I.INGREDIENTS_NAME FROM #ING I

    INNER JOIN

    INGREDIENTS_NAME N ON N.INGREDIENTS_NAME=I.INGREDIENTS_NAME

    INNER JOIN

    Recipes_Name R ON R.NAME=I.Recipes_Name

    LEFT OUTER JOIN

    INGREDIENTS G ON G.INGREDIENTS_NAME_ID=N.ID AND R.ID=G.Recipes_Name_ID

    WHERE I.INGREDIENTS_NAME=N.INGREDIENTS_NAME AND I.Recipes_Name=R.NAME AND I.QUANTITY=G.QUANTITY

    )

    BEGIN

    SELECT 'FAILED'

    END

    /*

    ELSE IF EXISTS (SELECT R.NAME,I.INGREDIENTS_NAME FROM #ING I

    INNER JOIN

    INGREDIENTS_NAME N ON N.INGREDIENTS_NAME=I.INGREDIENTS_NAME

    INNER JOIN

    Recipes_Name R ON R.NAME=I.Recipes_Name

    LEFT OUTER JOIN

    INGREDIENTS G ON G.INGREDIENTS_NAME_ID=N.ID AND R.ID=G.Recipes_Name_ID

    WHERE I.INGREDIENTS_NAME=N.INGREDIENTS_NAME AND I.Recipes_Name=R.NAME

    )

    BEGIN

    UPDATE INGREDIENTS SET QUANTITY=(QUANTITY+@QUANTITY),CREATED_BY=SYSTEM_USER,CREATED_DATE=GETDATE()

    WHERE Recipes_Name_ID in (SELECT R.ID FROM Recipes_Name R

    WHERE R.NAME=@Recipes_Name)

    AND

    INGREDIENTS_NAME_ID IN (SELECT N.ID FROM INGREDIENTS_NAME N

    WHERE N.INGREDIENTS_NAME=@INGREDIENTS_NAME)

    SELECT 'UPDATED'

    END

    */

    ELSE

    BEGIN

    INSERT INTO INGREDIENTS(Recipes_Name_ID,INGREDIENTS_NAME_ID,QUANTITY,UOM,CREATED_BY,CREATED_DATE)

    SELECT R.ID,N.ID,T.QUANTITY,UOM.ID,SYSTEM_USER,GETDATE() FROM

    #ING AS T

    INNER JOIN

    Recipes_Name R ON R.NAME=T.Recipes_Name

    INNER JOIN

    INGREDIENTS_NAME N ON N.INGREDIENTS_NAME=T.INGREDIENTS_NAME

    INNER JOIN

    UOM ON UOM.UOM_NAME=T.UOM

    --SELECT 'SUCCESS'

    DROP TABLE #ING

    END

  • If you want to post SQL in your reply then you should paste it into a code box.

    tb

  • ALTER PROCEDURE INSERT_INGREDIENTS
    @Recipes_Name varchar (255)
    ,@INGREDIENTS_NAME varchar (255)
    ,@QUANTITY varchar (255)
    ,@UOM varchar (20)
    AS
    BEGIN

    CREATE TABLE #ING
    (
    Recipes_Name varchar (255)
    ,INGREDIENTS_NAME varchar (255)
    ,QUANTITY varchar (255)
    ,UOM varchar (20)
    );

    INSERT INTO #ING
    (
    Recipes_Name
    ,INGREDIENTS_NAME
    ,QUANTITY
    ,UOM
    )
    VALUES
    (@Recipes_Name, @INGREDIENTS_NAME, @QUANTITY, @UOM);

    END;

    IF EXISTS
    (
    SELECT R.NAME
    ,I.INGREDIENTS_NAME
    FROM #ING I
    INNER JOIN INGREDIENTS_NAME N ON N.INGREDIENTS_NAME = I.INGREDIENTS_NAME
    INNER JOIN Recipes_Name R ON R.NAME = I.Recipes_Name
    LEFT OUTER JOIN INGREDIENTS G ON G.INGREDIENTS_NAME_ID = N.ID
    AND R.ID = G.Recipes_Name_ID
    WHERE I.INGREDIENTS_NAME = N.INGREDIENTS_NAME
    AND I.Recipes_Name = R.NAME
    AND I.QUANTITY = G.QUANTITY
    )
    BEGIN

    SELECT 'FAILED';

    END;

    /*

    ELSE IF EXISTS (SELECT R.NAME,I.INGREDIENTS_NAME FROM #ING I

    INNER JOIN

    INGREDIENTS_NAME N ON N.INGREDIENTS_NAME=I.INGREDIENTS_NAME

    INNER JOIN

    Recipes_Name R ON R.NAME=I.Recipes_Name

    LEFT OUTER JOIN

    INGREDIENTS G ON G.INGREDIENTS_NAME_ID=N.ID AND R.ID=G.Recipes_Name_ID

    WHERE I.INGREDIENTS_NAME=N.INGREDIENTS_NAME AND I.Recipes_Name=R.NAME

    )

    BEGIN

    UPDATE INGREDIENTS SET QUANTITY=(QUANTITY+@QUANTITY),CREATED_BY=SYSTEM_USER,CREATED_DATE=GETDATE()

    WHERE Recipes_Name_ID in (SELECT R.ID FROM Recipes_Name R

    WHERE R.NAME=@Recipes_Name)

    AND

    INGREDIENTS_NAME_ID IN (SELECT N.ID FROM INGREDIENTS_NAME N

    WHERE N.INGREDIENTS_NAME=@INGREDIENTS_NAME)

    SELECT 'UPDATED'

    END

    */

    ELSE
    BEGIN

    INSERT INTO INGREDIENTS
    (
    Recipes_Name_ID
    ,INGREDIENTS_NAME_ID
    ,QUANTITY
    ,UOM
    ,CREATED_BY
    ,CREATED_DATE
    )
    SELECT R.ID
    ,N.ID
    ,T.QUANTITY
    ,UOM.ID
    ,SYSTEM_USER
    ,GETDATE()
    FROM #ING T
    INNER JOIN Recipes_Name R ON R.NAME = T.Recipes_Name
    INNER JOIN INGREDIENTS_NAME N ON N.INGREDIENTS_NAME = T.INGREDIENTS_NAME
    INNER JOIN UOM ON UOM.UOM_NAME = T.UOM;

    --SELECT 'SUCCESS'

    DROP TABLE #ING;

    END;

    That looks better.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

    1. Can you post the table schema and sample data for the tables INGREDIENTS_NAME, Recipes_Name, INGREDIENTS?  See the link in my signature.
    2. This post originally started about multiple values in a parameter.  Now, the procedure you posted doesn't indicate that in any way. Can the four parameters contain multiple values??

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply