One "big" Parameter -- Please Help
-
How can I allow users to paste a list of customer numbers into one big parameter ?
Example:
1. user goes to http://www.somesite.com/report
2. ASP page displays "Enter Customer Number" and provides a 14 row "textbox" to insert 14 customer numbers, 1 number on each line
3. user clicks "Submit" and a SQL statement splits each row into a seperate "WHERE" statement.
I have ASP/SQL code that will do one parameter, I just need to know how to "split each row into a seperate WHERE statement" automatically since users want to "paste" their info without having to rekey each number into a seperate box.
Please Help !
-
This sounds like the Fedex package tracking page, where you can enter multiple tracking numbers in an input. The way I've done it is to define the parameter to the stored proc as varchar, with the length = (number digits in customer number + 1) * (max allowed count of customer numbers). So if your customer number is five digits long, the parm is (5+1)*14 or varchar(84). Then in the page, in Javascript, I substring one customer number at a time and append a delimiter. So if the user enters three customer numbers, 12345, 44444, and 33333, my input box has "123454444433333" in it. I loop thru that five characters at a time and come up with "12345,44444,33333,". My stored procedure then loops through that to get each customer number, and maybe stashes them in a table variable to operate on elsewhere in the procedure. Or, instead of breaking up the big string into separate, delimited strings on your page, you can do that in the stored proc.
There is no "i" in team, but idiot has two. -
Yes, that is what I am trying to accomplish.
Please post some sample code if possible.
It does not have to be perfect, just something to get me started.
Thanks for your help ..............
-
Hm, here are some ideas if you want to do on the server.
http://www.sommarskog.se/arrays-in-sql.html
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi,
I came across the same thing recently and found this excellent piece of code stashed away in one of the forums. Unfortunately I cant find it now
otherwise I would have just posted a link:CREATE PROCEDURE [ListToTable]
@vcList VARCHAR(8000),
@vcDelimiter VARCHAR(8000),
@TableName SYSNAME,
@ColumnName SYSNAME
AS
SET NOCOUNT ON
DECLARE @iPosStart INT,
@iPosEnd INT,
@iLenDelim INT,
@iExit INT,
@vcStr varchar(8000),
@vcSql varchar(8000)
SET @iPosStart = 1
SET @iPosEnd = 1
SET @iLenDelim = LEN(@vcDelimiter)
SET @vcSql = 'INSERT ' + @TableName + ' (' + @ColumnName + ') VALUES ('''
SET @iExit = 0
WHILE @iExit = 0
BEGIN
SET @iPosEnd = CHARINDEX(@vcDelimiter, @vcList, @iPosStart)
IF @iPosEnd <= 0
BEGIN
SET @iPosEnd = LEN(@vcList) + 1
SET @iExit = 1
END
SET @vcStr = SUBSTRING(@vcList, @iPosStart, @iPosEnd - @iPosStart)
EXEC(@vcSql + @vcStr + ''')')
SET @iPosStart = @iPosEnd + @iLenDelim
END
RETURN 0
GO
This will create the ListToTable sproc, then use in this manner:
CREATE TABLE #values ([PK] [int] IDENTITY (1, 1) NOT NULL, colName varchar(255) NOT NULL)
EXEC ListToTable @ColumnNames, '|', '#values', 'colName'
Obviously you will need a pipe delimited list passed into @ColumnNames but you can change the delimiter to whatever you want to use. Use the hash table #values to do whatever processing you need to do in the rest of your sproc.
Excellent article from Frank, requires some reading but should give you all the understanding you need.
Have fun
Steve
We need men who can dream of things that never were.
-
Just as a totally off-the-wall idea, have you thought about building your your argument string into an XML stream from your ASP page (which you'd pass to your stored proc as e.g. an NVARCHAR(8000) ) and reading it using OPENXML.
Then you have effectively a temp table with your parameter values in and you could do a simple join.
Just a thought
Sam
-
how about using a udf ?
read : http://www.sqlservercentral.com/scripts/contributions/592.asp
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply