In my stored procedure, I want to parse @ArrayOfDays into @d1 through
@d5.

@ArrayOfDays is a varchar input parameter containing,
for example, "1.7.21.25.60." - five elements.

Most active vars:
@i - loop counter
@char - current char in string
@tempVal - contains the current element as it is being built
@tempValExecString - contains SELECT stmt for EXEC()

I'm using EXEC() to execute a dynamically built SELECT.

The error I get when calling from vb.net is:
Must declare the variable '@tempVal'.

Two manual traces indicate the logic is ok.

I suspect my assignment statement for @tempValExecString.

Any help would be appreciated. - BobC

----------------------------------------------------------
DECLARE
@d1 varchar(3),
@d2 varchar(3),
@d3 varchar(3),
@d4 varchar(3),
@d5 varchar(3),
@i int,
@char char(1),
@tempVal varchar(3),
@tempValExecString varchar(30)

SELECT @tempVal = ''
SELECT @i = 1

WHILE @i < LEN(@ArrayOfDays)
BEGIN
SELECT @char = SUBSTRING(@ArrayOfDays, @i, 1)
WHILE @char <> '.'
BEGIN
SELECT @tempVal = @tempVal + @char
SELECT @char = SUBSTRING(@ArrayOfDays, @i+1, 1)
IF @char = '.'
BEGIN
/* the following should produce "SELECT @d1 = 1" when it reads the
first period(.) */
SELECT @tempValExecString = 'SELECT @d' + LTRIM(RTRIM(STR(@i))) + '
= @tempVal'
EXEC(@tempValExecString)
SELECT @tempVal = ''
SELECT @i = @i + 1
END
SELECT @i = @i + 1
END
END
----------------------------------------------------------

bobc (bcanavan (AT) fmbnewhomes (DOT) com) writes:
Wait a minute. You are in a relational database now, not in a C++ program.

I didn't ask why you returned a delimited string in the procedure in
your first post, but if you intend on unpack the string in the
calling procedure, you are on the wrong track altogther. Pass the
data in a table, and perform your operations on the whole set.

No, it's not. A batch of dynamic SQL is a scope of its own, and you
cannot access variables in outer scope. If you want to assign
variables @d1 to @d5, that's five SELECT statements.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

You are doing almost everything wrong. SQL does not work this way.
Just pass a simple five parameter list. Then clean up your data in the
procedure body. Try this for a skeleton

CREATE PROCEDURE Foobar
(@d1 INTEGER, @d2 INTEGER, @d3 INTEGER, @d4 INTEGER, @d5 INTEGER)
AS
SELECT ..
FROM Floob
WHERE Floob.x IN (@d1, @d2, @d3, @d4, @d5)
AND ..;

YOU can use COALESCE in the IN() list to handle NULLs or whatever.

On Sep 28, 8:52 pm, --CELKO-- <jcelko... (AT) earthlink (DOT) net> wrote:
Thanks, all. Both posts (yesterday and today) have been part of my
experiments to find the most efficient method of querying a ton of
data a ton of different ways to populate a "dashboard" page on
our .net intranet. You've both discovered that I am more an
applications programmer than a sql programmer, so I know you'll
forgive me. Meanwhile, your input has been very helpful. Thanks for
your time. -BobC

ps: es, the output string "array" was intended to be parsed by my
vb.net app. I just got a little curious about how arrays could be
implemented in t-sql, and possibly save some calls to the db server by
my app, or at least reduce the number of batches. I'm sure it's all
been done before, but I had to try and fail for myself. A learning
experience if nothing else. Thanks again.

Amazing, you just don't learn do you.

What is the risk and resource assessment of adding value number 6?

Resource assessment...

1) Change the stored procedure to accept an extra parameter
2) Change the query IN to accept an extra parameter
3) Change all the applications that call the stored procedure to accept
the extra parameter - that can be '1' to 'n' in a real environment where
applications share common logic (stored procedures).

Risk assessment...

1) Database changes - requires application to be taken offline while the
release to add the extra parameter is done
2) Application changes - each application binary needs to be updated to
use the new parameter; for fat clients that would be quite an involved task
for a couple of thousand clients even with SMS.
3) Testing - did you capture all applications using the procedure, each
application requires a test plan and testing.

Now, if you had used CSV instead - you'd pass a single parameter to the
stored procedure containing 1 to 'n' values then you wouldn't have any of
the above, it would just work; there would be no requirement to take the
application offline, there would be no risk that some clients didn't get
updated properly so weren't using the correct version of the executable
etc...

I really do wish you'd start listening to people who actually do this type
of thing day in day out and have done so for 20 + years, sitting writing
books for 30+ years and teaching people is no replacement for solid
industrial experience.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]


"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote

The proc below will take your CSV input and put it into a table #csv_split
which you can then extract each @d variable for.

Is there a specific reason you need @d1, @d2 etc... what are you trying to
do? It might be possible to do it set wise.

Tony.

CREATE PROC array_parse
@csv varchar(8000) = ',1,7,21,25,60'
AS
BEGIN

SET @csv = ltrim(rtrim(@csv))

IF PATINDEX( '%[^,0-9]%', @csv ) > 0 -- Checks to make sure input is
digits or comma to prevent SQL injection
BEGIN
RAISERROR( 'Injection attempt or invalid data.', 16, 1 )

RETURN

END

DECLARE @d1 varchar(3)
DECLARE @d2 varchar(3)
DECLARE @d3 varchar(3)
DECLARE @d4 varchar(3)
DECLARE @d5 varchar(3)
DECLARE @d6 varchar(3)
DECLARE @d7 varchar(3)

DECLARE @sql nvarchar(4000)

CREATE TABLE #csv_split (
pos int not null PRIMARY KEY IDENTITY,
data int not null
)

SET @sql = REPLACE( @csv, ',', CHAR(13) + CHAR(10) + 'insert #csv_split
( data ) values( ' )

SET @sql = REPLACE( @sql, CHAR(13), ' );' + CHAR(13) )

SET @sql = RIGHT( @sql, LEN( @sql ) - 5 ) + ' );'

EXEC( @sql )

SET @d1 = ( SELECT data FROM #csv_split WHERE pos = 1 )
SET @d2 = ( SELECT data FROM #csv_split WHERE pos = 2 )
SET @d3 = ( SELECT data FROM #csv_split WHERE pos = 3 )
SET @d4 = ( SELECT data FROM #csv_split WHERE pos = 4 )
SET @d5 = ( SELECT data FROM #csv_split WHERE pos = 5 )
SET @d6 = ( SELECT data FROM #csv_split WHERE pos = 6 )
SET @d7 = ( SELECT data FROM #csv_split WHERE pos = 7 )

SELECT @d1, @d2, @d3, @d4, @d5, @d6, @d7

end

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]


"bobc" <bcanavan (AT) fmbnewhomes (DOT) com> wrote

The concept of arrays, linked lists, etc. do not exist in SQL -- just
tables. Ever work with LISP? No arrays, and it uses recursion
instead of loops. And LISP only has lists.

SQL is very much a foreign language to the procedural programmer. For
example, in Japanese, there are no articles or plurals, the pronoun
system is totally different (no direct equivalent to first person
singular, etc), the verb tenses are totally different and sentences
have a topic, but not a subject, etc.). But millions of people still
use Japanese.

You can fake a matrix with this skeleton:

CREATE TABLE Array
(i INTEGER NOT NULL CHECK (i BETWEEN 1 and 10),
j INTEGER NOT NULL CHECK (i BETWEEN 1 and 10),
vali INTEGER NOT NULL);

For INTEGER ARRAY A[1:10, 1:10] in a procedural language, but then you
have to write your or own library functions, loop constructs, etc.
And performance will stink.

bobc (bcanavan (AT) fmbnewhomes (DOT) com) writes:
Also in that case it would be better to return a result set and receive
that in a datatable in VB .Net. The code for composing the list is
dependent on the number of elements being fixed. Add one more value,
and you have a maintenance job to do. With result set + datatable you have
not.

You may be interested in this link for a rainy day:
http://www.sommarskog.se/arrays-in-sql.html.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thank you all for your help. I think my biggest mistake was to not
start at the beginning. Please accept my apology for any lack of
clarity in my posts. I'm still learning how to communicate about
these issues.

And yes, Tony... I'm after engineering, not quick and dirty code. Not
sure who your criticism is aimed at, but I have been referring to an
input array string since the first post, where the pseudo-code
condition on the loop indicated that the actual array length could
vary. I thought I could simplify things(only 5 elements) and not
burden others with a full explanation, but I probably need to rethink
that. Nevertheless, thanks for your time and help.

An overview of my project follows, but to review...
1. I posted to get help with my method of parsing an input string
2. The parsing code would be in the sub_proc that actually does the
work(not the wrapper)
3. I've been writing simple stored procedures and functions for years,
but I'm new at this level of complexity
4. My ultimate goal is an engineered solution that reflects the best
sql programming practices

Here is an example of what I'm trying to do: Populate a grid(below)
on a .net dashboard web page with counts of various types of
activity(y axis) that would be distributed into bins(x axis) . The
datasource would be a single table or simple view(no aggregates). The
bins would represent increments in specific criteria, which would NOT
necessarily involve sequential values such as days of the month. (The
actual page will contain several grids, each having a different number
of bins. I would like to use the same code for all grids, if possible
-- sending the datasource, number of bins, and bin criteria as input
parameters.)

1 2 3 4 5 6 7 8 9 10 11 12 13 14
15...
activity1 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0
activity2 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0
activity3 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0
activity4 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0
....
activity20 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0

I could call 20 different stored procedures, each having n SELECT
statements, but we all know a whole list of reasons why that's a bad
idea.

I could build a temporary table on the db server and return it as a
dataset to the page, but don't know the best way to build the
temporaty table. Ideally, the process would be flexible enough to
handle the whole job, and not require a set of 20+ procs that do the
same thing in slightly different ways -- costing more time, resources,
and maintenance. But I envision an enormous SELECT statement for each
activity. Maybe that's what it shoud be, and I'm just underestimating
sql server.

Where this post started:

My thought was to call a wrapper from the vb.net page, and the wrapper
would contain a set of EXECs calling the sub proc I sketched for you
earlier. One EXEC for each type of activity(activity.1 to
activity.n).

So, the vb.net page would call the wrapper, sending (for example) 20
"array" string parameters in which to return the results. This would
require opening only one connection to the db only one time -- one of
my goals.

Then the wrapper would sequentially call the sub-proc 20 times -- once
for each activity type. The sub-proc would parse the input array to
acquire the bin criteria, loop through the input array for each bin
and execute a dynamically built SELECT in an EXEC() statement, and
finally return an array for the current activity type. (Although it
would simplify maintenance, everyone has made it abundantly clear that
efficiency would be lost, and I would burn resources on the server as
well.)

When the wrapper finished, it would return 20 array strings to the
vb.net page. The page would parse the array strings and display the
results through some type of asp.net data control.

The consensus of returning a set makes more sense all the time. I
could just populate datalists from .net tables or datasets. But, I
feel like I'm back at square one, looking for an effecient way to
build the set on the db server when both my input arrays and bin
criteria can vary. Maybe my illustration of the final product above
will be more useful to anyone who has the time and interest to take
another look.

With much respect and gratitude,

BobC

110% @ celko and his proposed solution - the guy is an idiot with little
real industrial experience based on his solutions....

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]