Multiple values in a parameter

Question

Post reply

Multiple values in a parameter

vijay.verma2

SSC Enthusiast

Points: 182
More actions
January 31, 2023 at 6:07 pm

#4143839

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'.

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

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

Chrissy321 SSCoach Points: 15763 More actions · Answer 1

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

vijay.verma2 SSC Enthusiast Points: 182 More actions · Answer 2

Pls check

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

vijay.verma2 SSC Enthusiast Points: 182 More actions · Answer 3

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

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 4

;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

This reply was modified 1 year, 9 months ago by Jonathan AC Roberts.

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

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

kaj SSCommitted Points: 1605 More actions · Answer 6

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)
)

kaj SSCommitted Points: 1605 More actions · Answer 7

@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.

Michael L John One Orange Chip Points: 27055 More actions · Answer 8

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/

kaj SSCommitted Points: 1605 More actions · Answer 9

@ 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, 9 months ago by kaj.

vijay.verma2 SSC Enthusiast Points: 182 More actions · Answer 10

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'

*/

vijay.verma2 SSC Enthusiast Points: 182 More actions · Answer 11

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

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 12

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

Michael L John One Orange Chip Points: 27055 More actions · Answer 13

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/

Michael L John One Orange Chip Points: 27055 More actions · Answer 14

Can you post the table schema and sample data for the tables INGREDIENTS_NAME, Recipes_Name, INGREDIENTS? See the link in my signature.
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/