IN with @variable

Hi experts

this is working
declare @user_panama nvarchar(55) = 'MSANCHEZ';

this is NOT working
declare @user_panama nvarchar(55) = 'MSANCHEZ' + ',' + 'VDIAZ';

this is the predicate
  where
  [Usuarios] IN (@user_panama)
enrique_aeoAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Tapan PattanaikConnect With a Mentor Senior EngineerCommented:
Hi Enrique,

User my below function  "CSVToTable" and pass the parameter to it.

CREATE FUNCTION [dbo].[CSVToTable] (@InStr VARCHAR(MAX))

RETURNS @TempTab TABLE

   (SSNSoftwareSerialNumber Varchar(50) not null)

AS

BEGIN

    ;-- Ensure input ends with comma

	SET @InStr = REPLACE(@InStr + ',', ',,', ',')

	DECLARE @SP INT

DECLARE @VALUE VARCHAR(1000)

WHILE PATINDEX('%,%', @INSTR ) <> 0 

BEGIN

   SELECT  @SP = PATINDEX('%,%',@INSTR)

   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)

   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')

   INSERT INTO @TempTab(SSNSoftwareSerialNumber) VALUES (@VALUE)

END

	RETURN

END

Open in new window




declare @user_panama nvarchar(55) = 'ATI' + ',' + 'ICHANG';
 
SELECT  distinct [Rol] + '_PG_PA' as 'ROL_PANAMA', [Usuarios]
 FROM [AdventureWorks].[dbo].[usuarios_para_panama]
 where [Usuarios] in (SELECT SSNSoftwareSerialNumber FROM dbo.CSVToTable(@user_panama))

Open in new window

SqlQuery-Screen-shot.PNG
0
 
Tapan PattanaikSenior EngineerCommented:
Hi enrique_aeo,

Create a below function to your database and pass the parameter to it.


CREATE FUNCTION [dbo].[CSVToTable] (@InStr VARCHAR(MAX))

RETURNS @TempTab TABLE

   (SSNSoftwareSerialNumber Varchar(50) not null)

AS

BEGIN

    ;-- Ensure input ends with comma

	SET @InStr = REPLACE(@InStr + ',', ',,', ',')

	DECLARE @SP INT

DECLARE @VALUE VARCHAR(1000)

WHILE PATINDEX('%,%', @INSTR ) <> 0 

BEGIN

   SELECT  @SP = PATINDEX('%,%',@INSTR)

   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)

   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')

   INSERT INTO @TempTab(SSNSoftwareSerialNumber) VALUES (@VALUE)

END

	RETURN

END

Open in new window



SELECT SSNSoftwareSerialNumber FROM dbo.CSVToTable(@user_panama)

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
you won't be able to do it like this. You will need to convert your variable into a table. Have a look at http://emoreau.com/Entries/Blogs/2015/04/Just-learned-a-nifty-t-SQL-trick-this-morning.aspx for an example
0
 
Tapan PattanaikSenior EngineerCommented:
this is NOT working:
 declare @user_panama nvarchar(55) = 'MSANCHEZ' + ',' + 'VDIAZ';

It will only work if you pass the parameter like this 'MSANCHEZ','VDIAZ'  not like MSANCHEZ,VDIAZ

declare @user_panama nvarchar(55) = '''MSANCHEZ''' + ',' + '''VDIAZ''';
select @user_panama;
0
 
enrique_aeoAuthor Commented:
this is the data
SAP_BC_BASIS_ADMIN_PG_PA      ATI
SAP_BC_BMT_WFM_ADMIN_PG_PA      ICHANG
SAP_BC_BMT_WFM_ADMIN_PG_PA      JARENAS
SAP_BC_BMT_WFM_ADMIN_PG_PA      JPILCO
SAP_BC_BMT_WFM_ADMIN_PG_PA      LENCISO

go
declare @user_panama nvarchar(55) = '''ATI''' + ',' + '''ICHANG''';
SELECT distinct [Rol] + '_PG_PA' as 'ROL_PANAMA', [Usuarios]
FROM [AdventureWorks].[dbo].[usuarios_para_panama]
where
            [Usuarios] IN (@user_panama)

ORDER BY 1

this is the results
(0 row(s) affected)
schema_data_user_pn---copia.sql
0
All Courses

From novice to tech pro — start learning today.