-1

I'm trying to write an application in C# that will pass a variable that contains multiple value separated by a comma, to a stored procedure

string state = "AZ, CA, VA"

I want my stored procedure to return all data for each state in the variable

CREATE PROCEDURE usp_GetStateInfo
@state varchar(2)

SELECT * FROM StateTable WHERE state IN (@state)

But when I do the code above I get an error saying 'Incorrect syntax' What am I doing wrong and how can I fix this?

Thank you

Newit
  • 3
  • 6
  • 1
    Use single quotes: "'AZ', 'CA', 'VA'" – Odrai Dec 08 '17 at 19:35
  • 1
    I assume you're talking SQL-server (T-SQL)? What's your current code for calling the procedure? – Dylan Nicholson Dec 08 '17 at 19:36
  • 1
    there are examples on the internet as well in regards to how to use the reserved word `IN` in sql have you tried a google search..? – MethodMan Dec 08 '17 at 19:37
  • The issue I'm having right now is when I'm creating the stored procedure. Whenever I try to execute the stored procedure it gives me the error – Newit Dec 08 '17 at 19:50
  • 1
    The possible duplicate from @DylanNicholson is the one you want. `IN` looks for a value in list. A string isn't a list. You can send a table valued parameter - a list of values - and then join to it. – Scott Hannen Dec 08 '17 at 20:14
  • varchar(2) only gives you 2 characters...Try varchar(MAX) to be optimistic :) – Grantly Dec 08 '17 at 20:14

2 Answers2

1

You cannot use multiple inputs as parameter for IN statement. You will have to split those comma separated values in SQL to create a table variable and join with that table variable.

You can create a function for splitting input string :-

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_PutStringtoTableUnique]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udf_PutStringtoTableUnique]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE function [dbo].[udf_PutStringtoTableUnique]  
(         
      @inputStr as varchar(max),  
      @delim nchar(1) = N','    
)    
returns @tbl table(ID int primary key)    
as    
BEGIN    

if @inputStr = '' or @inputStr is null
return 

declare @tags nchar(2)
set @tags = @delim + @delim;

with tbl_for_csv as    
(    
select left(@inputStr + @tags,charindex(@delim,@inputStr + @tags) -1)as Col,     
right(@inputStr + @tags,len(@inputStr + @tags) - charindex(@delim,@inputStr + @tags)) as Str    
union all    
select left(Str,charindex(@delim,Str) - 1)as Col,     
right(Str,len(Str) - charindex(@delim,Str)) from tbl_for_csv    
where len(right(Str,len(Str) - charindex(@delim,Str))) > 0    
)    
insert into @tbl    
select distinct Col from tbl_for_csv    
option (maxrecursion 0)    

return 
END

GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

And Then write something like this in procedure :-

DECLARE @TempStates Table (StateName Varchar(2))

INSERT INTO @TempStates
Select id from [dbo].[udf_PutStringtoTableUnique](@state, ',')

    SELECT * FROM StateTable 
    INNER JOIN @TempStates temp ON state = temp.StateName
Raska
  • 209
  • 2
  • 7
  • Nice, but cumbersome solution. I'm sure this would work but UDF are awfully slow – Grantly Dec 08 '17 at 20:21
  • THat's a simple udf and won't take much time... I have tried the same on bulky data as well. – Raska Dec 08 '17 at 20:28
  • Yeah I know what you mean, if they are not used inline then they are acceptable...When used inline (in a select) they can really bog down speed. – Grantly Dec 08 '17 at 20:29
  • 1
    Yeah...Agreed Grantly..Better to have a table variable to store function's result n use it. Editing that query... – Raska Dec 08 '17 at 20:35
  • thats a good idea :) I'll try to remember that – Grantly Dec 08 '17 at 20:37
1

The IN operator accepts an inline list of values, or a select statement. There is no native list type of variable in SQL. And certainly varchar(2) will not work, as you have it.

As you are using C#, it makes sense to loop through a list of states, calling the stored procedure for each element. The procedure would use = instead of IN to filter the states.

If you insist that the procedure accept a string of comma-separated values, then you would have to use SQL commands to split the string and insert each value into a temp table or table variable, to then have a select statement for the INto consume.

In T-SQL, you can do this with STRING_SPLIT() for SQL Server 2016 https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql

SELECT *
FROM StateTable
WHERE state IN(
    SELECT value
    FROM STRING_SPLIT(@states, ',') )
DarkSigma
  • 416
  • 2
  • 9