Assistance with Stored Procedure

[Tod Thames]

I am running SQL Server 7.0 and using a web interface. I would like
for a user to be able to input multiple values into a single field
with some sort of delimiter (such as a comma). I want to pass this
field into a Stored Procedure and have the stored procedure use the
data to generate the resutls.

Example:

Web page would ask for ID number into a field called IDNum. User
could input one or many ID numbers separated by a comma or some other
delemiter - could even be just a space (113, 114, 145).

SQL statement in Stored Procedure is something like this:

Select * from tblEmployess where IDNumber = @IDNum


I need the SQL statement to somehow use an "or" or a "loop" to get all
of the numbers passed and use the delimiter to distinguish when the
"loop" stops.

I obtained a module from a friend that allows me to do this in access,
but have recently converted everything to SQL server and web
interface. Now, everyone in the office expects to be able to
accomplish the same results via the web.

Any help is appreciated. If you need any additional information to
provide me some assistance, please email me at
tod.thames@nc.n gb.army.mil.

Thanks in advance.

Tod

[Dan Guzman]

Re: Assistance with Stored Procedure

Take a look at http://www.algonet.se/~sommar/arrays-in-sql.html.

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800

http://www.sqlserverfaq.com

http://www.mssqlserver.com/faq

-----------------------

"Tod Thames" <tod.thames@nc. ngb.army.mil> wrote in message
news:5ed144f0.0 310050454.369f4 994@posting.goo gle.com...[color=blue]
> I am running SQL Server 7.0 and using a web interface. I would like
> for a user to be able to input multiple values into a single field
> with some sort of delimiter (such as a comma). I want to pass this
> field into a Stored Procedure and have the stored procedure use the
> data to generate the resutls.
>
> Example:
>
> Web page would ask for ID number into a field called IDNum. User
> could input one or many ID numbers separated by a comma or some other
> delemiter - could even be just a space (113, 114, 145).
>
> SQL statement in Stored Procedure is something like this:
>
> Select * from tblEmployess where IDNumber = @IDNum
>
>
> I need the SQL statement to somehow use an "or" or a "loop" to get all
> of the numbers passed and use the delimiter to distinguish when the
> "loop" stops.
>
> I obtained a module from a friend that allows me to do this in access,
> but have recently converted everything to SQL server and web
> interface. Now, everyone in the office expects to be able to
> accomplish the same results via the web.
>
> Any help is appreciated. If you need any additional information to
> provide me some assistance, please email me at
> tod.thames@nc.n gb.army.mil.
>
> Thanks in advance.
>
> Tod[/color]

[Tod Thames]

Re: Assistance with Stored Procedure

Everytime I try to get to the website you refrenced, I get TCP_ERROR.
Some sort of communication problem. Is there any other sites that have
the same sort of information?

Thanks for the response,

Tod



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Dan Guzman]

Re: Assistance with Stored Procedure

I don't know if the content is mirrored elsewhere. The author, Erland
Sommarskog, frequents this newsgroup so maybe he'll jump in.

BTW, I don't have any problems accessing the site.

--
Hope this helps.

Dan Guzman
SQL Server MVP


"Tod Thames" <tod.thames@nc. ngb.army.mil> wrote in message
news:3f803253$0 $195$75868355@n ews.frii.net...[color=blue]
> Everytime I try to get to the website you refrenced, I get TCP_ERROR.
> Some sort of communication problem. Is there any other sites that[/color]
have[color=blue]
> the same sort of information?
>
> Thanks for the response,
>
> Tod
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

[Erland Sommarskog]

Re: Assistance with Stored Procedure

[posted and mailed]

Tod Thames (tod.thames@nc. ngb.army.mil) writes:[color=blue]
> Everytime I try to get to the website you refrenced, I get TCP_ERROR.
> Some sort of communication problem. Is there any other sites that have
> the same sort of information?[/color]

Too bad. If you have the complete error message, I'm interested. I'm
inclined to suspect that this might be some firewall problem at your
side, but I might get carried away of the .mil in your address.

Anyway, here is an excerpt of the part which is most relevant to
you. If you want to read the entire article, just drop me a line.

An Extravagant List-of-integers Procedure

The technique in the previous section can of course be applied to a list
of integers as well, so what comes here is not a true port of the
iter_intlist_to _table function, but a version that goes head over heels
to validate that the list items are valid numbers to avoid a conversion
error. And to be extra ambitious, the procedure permits for signed
numbers such as +98 or -83. If a list item is not a legal number, the
procedure produces a warning. The procedure fills in a temp table that
has a listpos column; this column will show a gap if there is an illegal
item in the input.

CREATE PROCEDURE intlist_to_tabl e_sp @list ntext AS

DECLARE @pos int,
@textpos int,
@listpos int,
@chunklen smallint,
@str nvarchar(4000),
@tmpstr nvarchar(4000),
@leftover nvarchar(4000)

SET NOCOUNT ON

SELECT @textpos = 1, @listpos = 1, @leftover = ''
WHILE @textpos <= datalength(@lis t) / 2
BEGIN
SELECT @chunklen = 4000 - datalength(@lef tover) / 2
SELECT @tmpstr = ltrim(@leftover + substring(@list , @textpos, @chunklen))
SELECT @textpos = @textpos + @chunklen

SELECT @pos = charindex(' ', @tmpstr)
WHILE @pos > 0
BEGIN
SELECT @str = rtrim(ltrim(sub string(@tmpstr, 1, @pos - 1)))
EXEC insert_str_to_n umber @str, @listpos
SELECT @listpos = @listpos + 1
SELECT @tmpstr = ltrim(substring (@tmpstr, @pos + 1, len(@tmpstr)))
SELECT @pos = charindex(' ', @tmpstr)
END

SELECT @leftover = @tmpstr
END

IF ltrim(rtrim(@le ftover)) <> ''
EXEC insert_str_to_n umber @leftover, @listpos
go

-- This is a sub-procedure to intlist_to_tabl e_sp
CREATE PROCEDURE insert_str_to_n umber @str nvarchar(200),
@listpos int AS

DECLARE @number int,
@orgstr nvarchar(200),
@sign smallint,
@decimal decimal(10, 0)

SELECT @orgstr = @str

IF substring(@str, 1, 1) IN ('-', '+')
BEGIN
SELECT @sign = CASE substring(@str, 1, 1)
WHEN '-' THEN -1
WHEN '+' THEN 1
END
SELECT @str = substring(@str, 2, len(@str))
END
ELSE
SELECT @sign = 1

IF @str LIKE '%[0-9]%' AND @str NOT LIKE '%[^0-9]%'
BEGIN
IF len(@str) <= 9
SELECT @number = convert(int, @str)
ELSE IF len(@str) = 10
BEGIN
SELECT @decimal = convert(decimal (10, 0), @str)
IF @decimal <= convert(int, 0x7FFFFFFF)
SELECT @number = @decimal
END
END

IF @number IS NOT NULL
INSERT #numbers (listpos, number) VALUES (@listpos, @sign * @number)
ELSE
RAISERROR('Warn ing: at position %d, the string "%s" is not an legal integer',
10, -1, @listpos, @orgstr)
go

Here is how you would use it:

CREATE PROCEDURE get_product_nam es_iterproc @ids varchar(50) AS
CREATE TABLE #numbers (listpos int NOT NULL,
number int NOT NULL)
EXEC intlist_to_tabl e_sp @ids
SELECT P.ProductID, P.ProductName
FROM Northwind..Prod ucts P
JOIN #numbers n ON P.ProductID = n.number
go
EXEC get_product_nam es_iterproc '9 12 27 37'

The validation of the list item is in the sub-procedure
insert_str_to_n umber. For many purposes it would be sufficient to have
the test

@str NOT LIKE '%[^0-9]%' AND len(@str) BETWEEN 1 AND 9

which checks that @str only contain digits and is at most nine digits
long (that is, you disapprove ten-digit numbers as well as signed
numbers).

You might guess that there is a performance cost for this extravaganza,
and indeed the procedure needs about 50% more time than the corresponding
function. Still, for many situations, the execution time is acceptable.

One note about the warning produced with RAISERROR: with ADO, this
warning may be difficult or impossible to detect on client level. If you
change the severity from 10 to 11, it will be an error, and raise an
error in your client code.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Tod Thames]

Re: Assistance with Stored Procedure



Thanks for the response - it's a little above my abilities, but I plan
on studying it and trying to make it work for me project.

Tod

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Anith Sen]

Re: Assistance with Stored Procedure

See if the following link helps..

http://tinyurl.com/6iil

--
--- Anith

[Shervin]

Re: Assistance with Stored Procedure

Tod,

Try this:

create procedure ListEmployees
@IDNum char(1024)
as begin
set @IDNum = ' ' + replace(@IDNum, ',', ' ') + ' '
select *
from tblEmployess
where @IDNum like ('% ' + ltrim(str(IDNum ber)) + ' %')
end

Shervin

"Tod Thames" <tod.thames@nc. ngb.army.mil> wrote in message
news:5ed144f0.0 310050454.369f4 994@posting.goo gle.com...[color=blue]
> I am running SQL Server 7.0 and using a web interface. I would like
> for a user to be able to input multiple values into a single field
> with some sort of delimiter (such as a comma). I want to pass this
> field into a Stored Procedure and have the stored procedure use the
> data to generate the resutls.
>
> Example:
>
> Web page would ask for ID number into a field called IDNum. User
> could input one or many ID numbers separated by a comma or some other
> delemiter - could even be just a space (113, 114, 145).
>
> SQL statement in Stored Procedure is something like this:
>
> Select * from tblEmployess where IDNumber = @IDNum
>
>
> I need the SQL statement to somehow use an "or" or a "loop" to get all
> of the numbers passed and use the delimiter to distinguish when the
> "loop" stops.
>
> I obtained a module from a friend that allows me to do this in access,
> but have recently converted everything to SQL server and web
> interface. Now, everyone in the office expects to be able to
> accomplish the same results via the web.
>
> Any help is appreciated. If you need any additional information to
> provide me some assistance, please email me at
> tod.thames@nc.n gb.army.mil.
>
> Thanks in advance.
>
> Tod[/color]