Re: passing an array to stored procedure

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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)
  • Dynamic Tables vs EAV
    ... Links for SQL Server Books Online: ... can essentially query and manipulate the nodes of the XML column sets using ...
    (microsoft.public.sqlserver.programming)
  • 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)
  • thank you
    ... Unfortunately short of a clr proc or function you are unable to ... Change the maximum characters retrieved value for xml data. ... 'path provided by the stored procedure. ... 'If an error occurs catch the message and pipe it back to SQL ...
    (microsoft.public.sqlserver.programming)
  • 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)