1

I created a stored proc in SQL Server 2012 using the following:

CREATE TYPE dbo.EncTypeFilter
AS TABLE
(
  EncTypeFilterID int
);
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE GetExplorerData    
@EncTypeFilterList AS dbo.EncTypeFilter READONLY
AS
BEGIN
   ...
END

I get the error "Operand type clash: varchar is incompatible with EncTypeFilter" when I call the procedure with the following command:

EXEC GetExplorerData @EncTypeFilterList='(1,2,3,4)'

So, how do I pass a list of Ints as a param to the procedure?

ihatemash
  • 1,474
  • 3
  • 21
  • 42

1 Answers1

1

You can't auto-cast from varchar to table contents. Use this:

DECLARE @EncTypeFilterList dbo.EncTypeFilter
INSERT INTO @EncTypeFilterList VALUES (1), (2), (3), (4);
EXEC GetExplorerData @EncTypeFilterList
Jaaz Cole
  • 3,119
  • 1
  • 15
  • 20
  • I need to pass an array of indexes from a C# app. The array contains the indexes of filter options. So maybe I don't need the EncTypeFilterList table at all. I just need to be able to pass in the list of indexes of the filter options selected by the user and use that list in my where clause like this: Where ec.EncTypeID IN ( [List of Indexes passed into the stored proc] ) – ihatemash Jun 23 '14 at 20:28
  • However you want to do it. The Array can't be passed directly, but you might be able to have a parameter of a query be replaced by a concatenated string. Not familiar with C3's options on that, however. – Jaaz Cole Jun 23 '14 at 20:42
  • 1
    I found my answer on this post. http://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure Thanks for your help. – ihatemash Jun 24 '14 at 14:40