Re: passing an array to stored procedure

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 03/25/04 

Date: Thu, 25 Mar 2004 15:18:44 -0600

Look for Joe's version of this using a sequence table. It is really sweet.
This post is from 2001, but it is still relavent:

http://tinyurl.com/2yatk

What he is saying is true, this is not at all a good way to store and deal
with proper data, but it is a good way to pass a set from a non-relational
source, like VB, and turn it into a proper set.

Another possiblility that might be good is to use XML. Just wrap your set
up in XML tags and use the XML functions for this. 

-- 
----------------------------------------------------------------------------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services.  All other replies will be ignored :)
"Joe Celko" <joe.celko@northface.edu> wrote in message
news:%230atk5pEEHA.3412@TK2MSFTNGP10.phx.gbl...
>> is it possible put this SQL statement in a storproc with two
parameters @par_1 and @par_2 that contains the two arrays of integers
(1, 2, 3) and nvarchar ('alpha', 'beta') .. <<
Short answer: No.  SQL is a set-oriented language.  The table is the
only data structure in it.  The elements of a properly designed table
are scalar (atomic) values. YOu can write this as:
SELECT col_a, col_b
  FROM My_table
 WHERE col_a IN (SELECT i FROM Numbers)
   AND col_b IN (SELECT a FROM Alphas);
Remember to add constraints to the Numbers and Alphas tables, of course.
Minor point: do you really need NVARCHAR(n)?  Most Newbies just
automatically use it without any research.
Wrong answer: you can do all kinds of string manipulations and cast()
operations to destroy first normal form instead of writing good code.  I
have posted some of those queries myself and you can Google them.  No
constraints, no error checking, etc.
--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Help with database export/import methods.
    ... export and import scheme for my databases. ... Our goal is to move completely away from MS's SQL Management Studio, ... from XML. ... It also doesn't include constraints. ...
    (comp.databases)
  • Re: Is there a way to convert ms sql 2008 tables to XML files?
    ... After looking at the code I made it work, since I removed 2 nVARCHARcolumns from the SQL statement where it is used as an RTF. ... I have the 2 XML files the bad and the good which are below: ... Verdana;}{\f1\fnil\fcharset0 Microsoft Sans ... Here is the actual string copied from the ms sql 2008 which works fine; ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: OO vs. RDB challenge
    ... in practice its outweighed by the unfortunate fact that SQL ... > The Relational Model is the best possible layer, ... constraints of the RDBMS" is an attractive idea in theory, ... RDBMS in some other language and show its as easy as using an RDBMS". ...
    (comp.object)
  • Re: XML-Speicherung beim SQL Server 2005
    ... > also wenn die XML-Dokumente geparst werden, ... das der SQL Server dafür XQuery einsetzt. ... Struktur von XML. ...
    (microsoft.public.de.sqlserver)
  • Re: loop and recordset
    ... those members whose membership lapsed. ... Use the SQL language to return only the set of required ... This table obviously needs some constraints. ... ALTER TABLE enrollment_2 ADD ...
    (microsoft.public.access.modulesdaovba)