Solved

IN with @variable

Posted on 2016-11-28
5
32 Views
Last Modified: 2016-11-28
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)
0
Comment
Question by:enrique_aeo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 21

Expert Comment

by:Tapan Pattanaik
ID: 41904251
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41904254
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
 
LVL 21

Expert Comment

by:Tapan Pattanaik
ID: 41904258
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
 

Author Comment

by:enrique_aeo
ID: 41904289
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
 
LVL 21

Accepted Solution

by:
Tapan Pattanaik earned 500 total points
ID: 41904326
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

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Stored Proc - Rewrite 42 77
Parsing this XML works but the other one doesn't 9 35
How do I partition this table on date? 5 54
Database Availability Group Distribution 9 51
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question