• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 139
  • Last Modified:

SQL Scripting - how to program input

I have a script that I run on a SQL Server 2008 and it works fine. My problem or need is that the script is a query that does a condition on the select with a where  X in (a, b, c, d, e,). Specifically I want to search a table where the rows are a, b, c, d, e, etc. (a,b,c,d,e are policy numbers).  As I state the script works when I manually input the policy numbers and my problem is that I can copy/paste the policy numbers into the In ( ) but I am limited to 8000 characters which is the max amount of characters allowed. I have 1000's of policy numbers that are all 16 characters.
Any ideas that would allow me to input all my policy numbers at once?
1 Solution
Bob BenderCommented:
If you have a list of policies that can be extracted from a table with a common theme, you can use a Nested SQL statement.   A SQL nested query is a SELECT query that is nested inside a SELECT, UPDATE, INSERT, or DELETE SQL query.

A simple example of SQL nested query

SELECT Model FROM Product
WHERE ManufacturerID
IN (SELECT ManufacturerID FROM Manufacturer WHERE Manufacturer = 'Dell')

The nested query (withinin the IN clause) will select all models from the Product table manufactured by Dell:

Inspiron B120
Inspiron B130
Inspiron E1705

SeTechAuthor Commented:
Bob -- that looks like it will work. Thanks Much
Starting SQL 2005 the varchar allows MAX size:

Declare @value_list varchar(MAX)='<values list here>'

The limit is 2GB.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Scott PletcherSenior DBACommented:
Load the policy numbers into a keyed temp table and join to it.

CREATE TABLE #policy_numbers (
    policy_number char(16) PRIMARY KEY
INSERT INTO #policy_numbers
SELECT ...list of policy#s...

FROM data_table dt
INNER JOIN #policy_numbers pn ON
    pn.policy_number = dt.policy_number
What is the volatility of the parameter? (do you change it often or infrequently or rarely)

Where do you copy/paste the policy numbers from?
Bob BenderCommented:
Thanks for the vote of confidence  :D

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now