Using FETCH to populate a variable
Steven K
The following is in a stored procedure that I am calling from MS Access. I
am trying to loop through each record and append the email value to the
variable (@strParm11) to build one long string of email addresses that can
then be pasted into an email address box. I can't seem to use the syntax:
set @strParm11 = @strParm10 + @strParm11. Also, if I can build the string,
how would I send the value of @strParm11 to the caller? Thanks in advance.
__________
DECLARE @strParm04 varchar(2000), @strParm10 varchar(2000), @strParm11
varchar(2000), @strPrint varchar(20)
SET NOCOUNT ON -- Eliminate the "n rows affected" message
SET @strPrint = ' '
DECLARE csrPKey CURSOR FOR
SELECT Templ.Email FROM tblBusinessEmployee
OPEN csrPKey
FETCH NEXT FROM csrPKey INTO @strParm10
WHILE @@FETCH_STATUS = 0 BEGIN -- 0 = success, -1 = outside recordset, -2
= row no longer exist
-- PRINT @strParm10
set @strParm11 = @strParm10 + @strParm11
PRINT @strParm10
PRINT @strParm11
FETCH NEXT FROM csrPKey INTO @strParm10
END
CLOSE csrPKey
DEALLOCATE csrPKey
Jacco Schalkwijk
using a cursor:
DECLARE @strParm10 varchar(2000)
SET @strParm10 = '' -- otherwise it is NULL
SELECT @strParm10 = @strParm10 + TemplEmail + ';'
FROM tblBusinessEmployee
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Steven K" <sck...@lucent.com> wrote in message
news:#cSH4E3$CHA....@TK2MSFTNGP12.phx.gbl...
Anith Sen
This method is not a reliable one & can fail under various situations
depending on the indexes, data-sets etc. And hence it is not well-suited for
production mode systems.
--
- Anith
(Please respond only to newsgroups)
Scott Morris
you will need to define it more precisely. What exactly does "I can't seem
to use the syntax..." mean? Does it give you an error? Are you getting
results that are not expected. You are not separating the individual
addresses - did you mean to use something like "set var = var1 + ', ' +
var2"? Did you initialize the output variable to an empty string to avoid
issues with NULLs? Are you certain that the column selected does not allow
NULL or that no NULL data is ever selected? Are you correctly anticipating
the potential for overflowing the output variable?
Once you actually have working code, how you get that information to Access
is a question I have no experience with. Seems like simply doing a "SELECT
@strParm11 as ADDRESS_LIST" would work (assuming that you packing this code
into a stored procedure).
"Steven K" <sck...@lucent.com> wrote in message
news:#cSH4E3$CHA....@TK2MSFTNGP12.phx.gbl...
Jacco Schalkwijk
I think adding the values of all the rows in a particular column works.
Using this construct to generate values to simulate an identity column, for
which it has also been advised on this newsgroup, where it depends on the
actual order of the rows, is not reliable. But if the order doesn't matter I
think it is OK.
Maybe Itzik Ben-Gan or Tom Moreau can shed some light on this. They metion
this method in their book without disclaimer.
There is one disclaimer I would like to add, and that is that it is not
ANSI-compliant.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Anith Sen" <an...@bizdatasolutions.com> wrote in message
news:egD96V3$CHA....@TK2MSFTNGP10.phx.gbl...
Anith Sen
Well, concatenation is an 'order'-specific operation. Only prefixes
(before), postfixes (after) are allowed (which means random infixes -
between specific items - are not concatenations) that means they explicitly
rely on the position of constituent items.
As far for the method itself,
1. This method is undocumented. It can change with any changes in the SQL
Server product like patches, SPs etc.
2. This method relies on the optimizer behavior. SQL operations should be
logical and should not rely on the physical aspects like indexes, access
paths etc. Thus relying on such an operation is no different than relying on
the 'order' of rows in a table.
3. This method may fail
- for a table with certain changes in indexes.
- for a table/view with columns having duplicate values
- for a query with columns represented as certain expressions like
LEFT, SUBSTRING etc.
- for a query with certain ORDER BY clauses
- for a view with a cross join in them
- for a view/query with a derived table construct
- for a view/query with certain nested sub queries with more than one
level of nesting
- for a query with DISTINCT clause
- and many more
Are you telling that this method is suitable code for production ?
>> Maybe Itzik Ben-Gan or Tom Moreau can shed some light on this. They
metion this method in their book without disclaimer. <<
That does not mean it is correct or is recommended as a stable solution.
>> There is one disclaimer I would like to add, and that is that it is not
ANSI-compliant. <<
It is neither ANSI nor T-SQL compliant :-)
Jacco Schalkwijk
Thanks for the extensive information (can you tell me where you get that all
from ;-)) I was aware that this method is not proper SQL and relied on some
behind the scenes trickery, but I didn't know there is so much that
influences the result.
I actually thought that this method was documented, but the only thing I
could find was the somewhat similar UPDATE SET @var = column = @var +1
method.
> >> Maybe Itzik Ben-Gan or Tom Moreau can shed some light on this. They
> metion this method in their book without disclaimer. <<
>
> That does not mean it is correct or is recommended as a stable solution.
Well, if I buy a book (specially by people like Ben-Gan and Moreau) I assume
that all code in there is production quality unless specified otherwise.
They actually say it is something "you can take advantage off" (page 235),
which I think comes pretty close to a recommendation. I want my money back
;-)
> It is neither ANSI nor T-SQL compliant :-)
Well, it works, so it must by definition be T-SQL compliant I'd say ;-)
"Anith Sen" <an...@bizdatasolutions.com> wrote in message
news:emwgsL4$CHA....@TK2MSFTNGP12.phx.gbl...
Anith Sen
Here are some examples. There are many other examples you can find in this
Newsgroup archives or you can play with the queries and come up with many.
--#1
DECLARE @Str VARCHAR(8000)
SET @Str = SPACE(0)
SELECT @Str = @Str + ',' + SUBSTRING(@Str + au_id, 1, 1)
FROM Pubs..authors
PRINT @Str
--#3
DECLARE @Str VARCHAR(8000)
SET @Str = SPACE(0)
SELECT @Str = @Str + CustomerID + ','
FROM NorthWind..Orders
ORDER BY RIGHT(ShipAddress, 2)
PRINT @Str
--#2
DECLARE @Str VARCHAR(8000)
SET @Str = SPACE(0)
SELECT DISTINCT @Str = @Str + c1.column_name
FROM pubs.INFORMATION_SCHEMA.COLUMNS AS c1
WHERE c1.table_name = 'authors'
PRINT @Str
--#4
DECLARE @Str VARCHAR(8000)
SET @Str = SPACE(0)
SELECT @Str = @Str + LEFT(@Str + au_id, 1) + ','
FROM Pubs..authors
PRINT @Str
--#5
DECLARE @Str VARCHAR(8000)
SET @Str = SPACE(0)
SELECT @Str = @Str + CustomerID + ','
FROM NorthWind..Orders
ORDER BY LEFT(ShipAddress, 2)
PRINT @Str
--#6
CREATE VIEW vw (CustomerID, Address)
AS
SELECT CustomerID, RIGHT(RTRIM(Address), 1)
FROM NorthWind..Customers
GO
DECLARE @Str VARCHAR(8000)
SET @Str = SPACE(0)
SELECT @Str = @Str + CustomerID + ','
FROM vw
ORDER BY Address
PRINT @Str
>> somewhat similar UPDATE SET @var = column = @var +1 <<
For sequential row concatenation, even the T-SQL update extension is not
fool-proof either & it relies on the optimizer behaviour, but in certain
other cases, it works reasonably well.
>> Well, if I buy a book (specially by people like ... <<
I am not sure, but they may have warnings somewhere in the book regarding
it. Also I assume, the authors know about these drawbacks.
>> Well, it works,..<<
Come on! You still believe that, Jacco?
Jacco Schalkwijk
Thanks for all the examples, they are really useful.
About this being T-SQL compliant (without wanting to go in to a yes/no
debate): there is nothing to suggest in BOL that it isn't. I guess we have
to put this down as another error in BOL.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Anith Sen" <an...@bizdatasolutions.com> wrote in message
news:OfXEax6$CHA...@TK2MSFTNGP12.phx.gbl...