T sql select int value as casting varchar

Question

I have stored procedured called GetReport

customers.branches_ID is int type

however @Branches parameter is varchar

@Branches is "10,13534,554,776,767"

I want to search 10,13534,554,776,767 in branches_ID however if i cast/convert to varchar it is not working for me.

ALTER PROCEDURE [dbo].[GetReport] 

@fromDate datetime,
@toDate datetime,
@Branches varchar (500) = null

AS
BEGIN

SET NOCOUNT ON; 

select * from customers where

(customers.CreatedDate between @fromDate and @toDate) and

(@Branches is null or CONVERT(varchar(500), customers.branches_ID) in(@Branches )) -- This part is not working for me


END

how can i solve this problem how can i search multiple comma varcvhar inside of int column ?

Thanks.

If possible, change the design to use a data type *designed* for containing multiple data items, such as xml or table-valued parameters. If you can't change the design, a quick search should find plenty of results for `sql split commas`. T-SQL, in common with *most* languages doesn't attempt to inspect the contents of the single string parameter you're providing it and, on discovering commas within that string, suddenly decide to treat it instead as multiple parameters. — Damien_The_Unbeliever, May 15 '15 at 12:13
thanks for help may you please share simple example after that i will accept best answer if works — Richard, May 15 '15 at 12:17
http://www.aspsnippets.com/Articles/Pass-comma-separated-delimited-values-as-Parameter-to-Stored-Procedure-in-SQL-Server.aspx — mohan111, May 15 '15 at 12:17
Just search for table valued parameters. Check [this tutorial](https://msdn.microsoft.com/en-us/library/bb675163%28v=vs.110%29.aspx) on MSDN — Panagiotis Kanavos, May 15 '15 at 12:18
@GarethD there's no need to split anything if the OP passes the correct type, ie a TVP with the values to search — Panagiotis Kanavos, May 15 '15 at 12:18
@PanagiotisKanavos Presumably you did not read the articles, as they focus around the need to split strings for this reason, then benchmark the performance of techniques such as `',' + @ParamList + ',' LIKE '%,' + Column + ',%'`, then finally goes on to say that where possible the design should be adapted to use table valued parameters. This is specifically mentioned in the last article "Splitting strings: Now with T-SQL". I agree that TVP is the best way, but given the version of SQL Server is not tagged, it may not be possible. — GarethD, May 15 '15 at 12:21
Although I have marked as a duplicate of [this question](http://stackoverflow.com/questions/337704/), I don't believe however any of the answers are as definitive as a series of articles written on the subject by Aaron Bertrand. - [Split strings the right way – or the next best way](http://sqlperformance.com/2012/07/t-sql-queries/split-strings), [Splitting Strings : A Follow-Up](http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up), and [Splitting Strings : Now with less T-SQL](http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql). — GarethD, May 15 '15 at 12:31

score 1 · Accepted Answer · answered May 15 '15 at 12:24

There is at least 4 methods for this. Here is one:

select * from customers where
customers.CreatedDate between @fromDate and @toDate and 
(@Branches is null or branches_ID
    IN(
         SELECT  y.i.value('(./text())[1]', 'nvarchar(4000)')
         FROM    ( SELECT    x = CONVERT(XML, '<i>' + REPLACE(@Branches, ',', '</i><i>')
                    + '</i>').query('.')
                 ) AS a
         CROSS APPLY x.nodes('i') AS y ( i )))

Look for other methods here http://blogs.msdn.com/b/amitjet/archive/2009/12/11/sql-server-comma-separated-string-to-table.aspx

But you can use table valued types for this type of work if you use Sql Server 2008 or higher.

T sql select int value as casting varchar

1 Answers1