|
Home > Archive > MS SQL Server > October 2006 > While loop return values
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
While loop return values
|
|
| jumblesale 2006-10-24, 6:29 pm |
| Hello,
This is probably a simple question and i'm amazed i've never come
across it before, but i have a stored procedure which uses a while loop
to split a CSV list and want to return the values as a datatable to an
asp.net app.
Currently it looks like this:
....
WHILE @Position <> 0
BEGIN
SET @Value = LEFT(@CSVList, @Position - 1)
PRINT CAST(@Value as varchar(500))
SET @CSVList = STUFF(@CSVList, 1, @Position, '')
SET @Position = PATINDEX('%'+@Delimi
ter+'%' , @CSVList)
END
when run in Query Analyser this indeed prints a list of the values,
nicely split, but if I consume this in my app, the datatable is empty.
What do i need to change 'PRINT' to, to make it return?
Thanks in advance,
Max
| |
|
| Insert @Value into a temp table and return recordset of temp table.
| |
| jumblesale 2006-10-24, 6:29 pm |
| Cheers - that works a treat,
My only question is, if two users run this query at the same time,
wouldn't the SP try to create the temp table exist twice and throw an
error? Or could it potentially give the users each others' results? Or
does the temptable only exist for the stored proc's scope?
Cheers again,
Max
Ken wrote:
> Insert @Value into a temp table and return recordset of temp table.
| |
|
|
jumblesale wrote:[color=darkred
]
> Cheers - that works a treat,
> My only question is, if two users run this query at the same time,
> wouldn't the SP try to create the temp table exist twice and throw an
> error? Or could it potentially give the users each others' results? Or
> does the temptable only exist for the stored proc's scope?
>
> Cheers again,
> Max
>
> Ken wrote:
>
If you create a temp table using the # in front of it like create table
#Name (Value datatype). The table exists only in the scope of the
procedure.
If you create a temporary table it is actually created mulitple times
with random generated extension to the name. As an example create a
temporary table and go to the tempdb database in enterprise manager and
select tables and view the name.
| |
|
| Rephrase the previous posting
> If you create a temporary table it can actually be created mulitple times
> with random generated extension to the name. The session knows which of the instances of the tables it is available to access.
As an example create a temporary table and go to the tempdb database in
enterprise manager and select tables and view the name. Open a new
connection in query analyzer and create the same #temp table again and
then view the tables in tempdb again. You will see two intances of the
table.
Note: Temp tables are automatically dropped after the stored procedure
executes.
|
|
|
|
|