In Statement

Posted on 2012-09-19
Last Modified: 2012-09-20
OK, So I want to use an IN Statement against a smallint field.

How can I pass in the Paramaters so they work against a smallint field?

For example I want to pass in "1,2,3" as a paramater and have the 1,2,3 go against a field in the database that is a smallint type.

FROM Table
WHERE smallint IN(@paramater)

How can I accomplish this?

Question by:smithmrk
    LVL 25

    Accepted Solution

    If a variable is used directly within a SQL - the variable is interpreted as a single string value, it does not resolve to the list.
    You can build the full SQL into a variable and execute that or use a user defined function to take the string and turn into a list of values.
    Example of a execute is
    IF OBJECT_ID('tempdb..#tmp_table') IS NOT NULL 
    	DROP TABLE #tmp_table;
    CREATE TABLE #tmp_table (
    	id		int not null identity(1,1),
    	val   int,
    	descr varchar(20)
    insert into #tmp_table (val, descr) values 
    (1,'test 1'),
    (2,'test 2'),
    (3,'test 3');
    declare @list		varchar(10);
    set @list = '1,2';
    declare @SQL_select  nvarchar(1000);
    set @SQL_select = 'select * from #tmp_table where val in ('+@list+')';
    exec (@SQL_select);

    Open in new window

    LVL 21

    Expert Comment

    by:Alpesh Patel
    SELECT *
    FROM Table
    WHERE smallint IN(@paramater)

    if smallint is int field then parameter values like  1,2,3
    and string then '1','2','3'

    Author Closing Comment

    Thanks, I'll give it a try!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the fileā€¦

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now