Passing in an NULLABLE array of IDs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Passing in an NULLABLE array of IDs

Author

Topic

SQLGeno
Starting Member

13 Posts

Posted - 2011-03-24 : 00:18:01

I had have a search query that allows you to pass in as many paamters as you like as NULLS and it will get all records where a parameter is NOT NULL. I now need to pass in an array of IDs and get those records where an ID from the array is IN thre set. However, if no IDs are passed in then I want to get all records.

I have tried to simply pass the IDs in as a string and use them in the IN clause. This works for the case where NULL is passed in and when a single ID is passed in but NOT when a string like '1, 3, 5' is passed in. Error returned is Conversion failed when converting the varchar value

Any ideas appreciated.

ALTER PROCEDURE [dbo].[sptWarrantSearchMk2]
(
@WarrantNumber VARCHAR(50) = NULL,
@PersonsOfInterest VARCHAR(1000) = NULL,
@OffenceTypeId INT = NULL,
@OffenceIds VARCHAR(100) = NULL -- Array of OffenceIds
)
AS
SELECT DISTINCT
WarrantNumber AS 'Warrant',
REPLACE(PersonsOfInterest, CHAR(10), CHAR(59) + ' ') AS 'Persons Of Interest',
WarrantOffence.OffenceId,
Offence.OffenceDescription
FROM Warrant
INNER JOIN WarrantOffence
ON Warrant.WarrantId = WarrantOffence.WarrantId
INNER JOIN Offence
ON WarrantOffence.OffenceId = Offence.OffenceId
INNER JOIN OffenceType
ON Offence.OffenceTypeId = OffenceType.OffenceTypeId
WHERE (WarrantNumber = COALESCE(@WarrantNumber, WarrantNumber)) AND
(PersonsOfInterest LIKE COALESCE(@PersonsOfInterest, PersonsOfInterest)) AND
(OffenceType.OffenceTypeId = COALESCE(@OffenceTypeId, OffenceType.OffenceTypeId)) AND
WarrantOffence.OffenceId IN (COALESCE(@OffenceIds, WarrantOffence.OffenceId))

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-24 : 01:00:01

take a look here http://www.sommarskog.se/arrays-in-sql.html

KH
[spoiler]Time is always against us[/spoiler]

SQLGeno
Starting Member

13 Posts

Posted - 2011-03-24 : 17:18:55

I can't see how this covers the case of when my list\array is NULL then the IN has to be ignored and everything gets returned.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-24 : 18:10:17

In SQL, the following does not work, even though, to many people it would seem like it should work. I count myself among them, and the first time I encountered this I was elated that I found a serious bug in Microsoft SQL. Of course, the elation didn't last long.

declare @id_list varchar(255);
set @id_list = '1,2,3,4';
select * from MyTable where id in (@id_list);

It's a feature, so you have to find another way to make it work. One way is to do rewrite the where clause - I am using one of the conditions in your where clause. Replace this:

PersonsOfInterest LIKE COALESCE(@PersonsOfInterest, PersonsOfInterest)

with this:

(@PersonsOfInterest is null or ','+@PersonsOfInterest+',' like '%,'+PersonsOfInterest+',%')

Another possibility is to parse the comma-separated @PersonsOfInterest into a table variable or temp table and then join on that table.

There are other approaches, for example passing in the parameter as xml and shredding it etc. khtan's link discusses some of these alternatives.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-24 : 18:59:49

>> I now need to pass in an array of IDs and get those records [sic] where an ID from the array [sic] is IN the set. However, if no IDs are passed in then I want to get all records [sic]. <<

Rows are nothing like records. SQL has tables as its only data structure, which are nothing like arrays.

Do this with a long parameter list. You can pass up to 2000+ parameters in T-SQL, which is more than you probably will ever need. The compiler will do all that error checking that the query version and the procedural code simply do not have unless you write a full parser with the standard error codes. You can now pass local variables to your procedure; you can pass other data types and get automatic conversions, etc. In short, this is just good software engineering.

CREATE PROCEDURE LongList
(@p1 INTEGER = NULL,
@p2 INTEGER = NULL,
@p3 INTEGER = NULL,
@p4 INTEGER = NULL,
@p5 INTEGER = NULL)

x IN (SELECT parm
FROM (VALUES (@p1), (@p2), (@p3), (@p4), (@p5)) AS X(parm)
WHERE parm IS NOT NULL;

You get all the advantages of the real compiler and can do all kinds of things with the values.

Thre is no such silliness as a "type_id"; an attribute can be either a type or an identifier, but not some hybrid meta-data nightmare. Why not go all the way and make it a "type_id_value" or worse with more attribute properties stuck on the end?

We do not put affixes like "spt" on procedure names; read ISO-11179 or any book on basic data modeling. A procedure should be named "<verb>_<object>", you would have "Search_Mk2_Warrants". Your code is full of basic design problems that people who are still write COBOL, BASIC, etc. in SQL

Do you really have a warrant number that is fifty characters long? You will! Usually those numbers are fixed length and might have a check digit in them. Why do you have both a warrant_id and warrant_nbr? Which is the key?

Why do you have so many extra joins? Having worked for a prison system, I would guess that offense_type is an attribute of a warranty, such as "Class-A Felony", "Class-B felony", etc. and that it would be a column with a CHECK() constraint in the Offenses table table. This looks like a lot of attribute splitting.

Oh, those singular table names are another design error. Tables are sets, so they are always collective or plural names.

SQL programmers would use a long parameter list and not write a homemade parser. Here is a skeleton.

CREATE PROCEDURE LongList
(@p1 INTEGER = NULL,
@p2 INTEGER = NULL,
@p3 INTEGER = NULL,
@p4 INTEGER = NULL,
@p5 INTEGER = NULL)

x IN (SELECT parm
FROM (VALUES (@p1), (@p2), (@p3), (@p4), (@p5)) AS X(parm)
WHERE parm IS NOT NULL;

You get all the advantages of the real compiler and can do all kinds of things with the values that a re now in a derived table.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-03-25 : 12:56:24

Why a long parameter list and not a table_valued parameter? (assuming they are using 2008)

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources