0

I have a SP which looks up a product (only one) by its productid just like this (it's a very simplified version of the query)

ALTER PROCEDURE [dbo].[MYSP]
@productid int
AS BEGIN
    SET NOCOUNT ON
    select * from products where productid = @productid
END

EXEC MYSP 10

In some special cases I need to provide two or more productid but I am NOT allowed to pass more than ONE parameter to the SP so I tried to change the parameter from INT to VARCHAR and do:

ALTER PROCEDURE [dbo].[MYSP]
@productid varchar(10)
AS BEGIN
    SET NOCOUNT ON
    select * from products where productid IN (@productid)
END

EXEC MYSP '10,20,30'

However it will return me a conversion error because the productid column in the table is INT.

There is a workaround for this?

Thanks!

Yan Kleber
  • 407
  • 2
  • 4
  • 11
  • 2
    Use a table valued parameter (pass a populated table type as a parameter) https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-ver15 – Alex K. Feb 08 '21 at 16:24
  • 2
    `productid IN (@productid)` would be equivlent to `productid = @productid`, which for your parameter would be `productid = '10,20,30'`; and that is clearly *not* a valid `int`. Scalar values act like scalar values; SQL isn't a script language where a parameter can be used to replace keywords or objects. – Thom A Feb 08 '21 at 16:27
  • 1
    I would suggest against that dupe candidate, @PeterB. This [answer](https://stackoverflow.com/a/4740486/2029983) is wide open to injection, and the accepted answer is basically a comment. – Thom A Feb 08 '21 at 16:28
  • 1
    I found a way of doing it by stripping the string into a table and performing the WHERE IN check against the table. Thanks for helping! – Yan Kleber Feb 08 '21 at 19:56

0 Answers0