Summary passing the stored procedure a list of (complete zipodes in this example stored procedure) zipcodes to validate, return as XML, etc...

Additional information

sp_postalcode_lookup "'WX3 6FW','WX1 6LT'" ------------------------------------------------------------------------------------------------ CREATE PROCEDURE sp_postalcode_lookup @alist varchar(2048) AS execute('SELECT postalcode FROM Customers with (NOLOCK) WHERE postalcode IN (' + @alist + ') ORDER BY postalcode ') ------------------------------------------------------------------------------------------------ Go ahead and try this against the sample Northwind database in SQL Server... Here's sample .asp snippet... ------------------------------------------------------------------------------------------------ <% ' FileName: database_header.asp ' Purpose: global header for site ' Project: Testing ' Authors: Rob Karatzas, tutor@ebiz-developer.com ' Date: 1/6/1998 ' Revisions: 11/11/1999 Rob K. - Added GetRws parameter and functionality ' 11/30/1999 Rob K. - Added Error Handlers ' 01/24/2000 Rob K. - Added GetNumRcs parameter and functionality ' ' Notes: Use page_header.asp to Set all page level variables. ' Use page_footer.asp to Set all variables, objects, etc. to Nothing. ' Never use .inc files unless you have remapped .inc in IIS to .asp ' In IISv5, remap all .htm, .html, etc. to .asp - there's no penalty now Dim SiteDSN ' Can be assigned in a variety of ways. - preferrably global.asa ' My preference is to make the database connection at the page level. Even though every page creates and ' destroys connections, the newly created ones can execute on any thread (versus other schemes which ' inherently force thread affinity, thread-Locking and/or serialization). Also because of round-robin execution, ' resources are used sparingly and connections are never open too long. SiteDSN = "Provider=SQLOLEDB;Data Source=10.1.1.1;Initial Catalog=Northwind;User Id=sa;Password=" Dim iRecsReturned Dim sErrReturned Dim sErrDesc Dim oDB Dim oCmd Dim guid Set oDB = GetDBConnection() Set oCmd = GetDBCommand(oDB) Dim SPParams() ReDim SPParams (1) ' here's the example call to the stored procedure which validates an postalcode list... SPParams(0) = "'WX3 6FW','WX1 6LT'" Response.Write CallSP(False,True,"sp_postalcode_lookup",False,True,SPParams,"<TABLE BORDER=1>","</TABLE>","<tr>","</tr>","<td>","</td>") Function CallSP(GetNumRcs,GetRws, SPName, RtnCd, Hdngs, Param, DelimHeader, DelimTrailer, DelimRowPre, DelimRowSuf, DelimColPre, DelimColSuf) ' Populates data string and returns thru function: ' ' Should get a record count if GetNumRcs if True (True/False) ' Should perform GetRows function (True/False) ' Name of Stored Procedure being called ' Stored Procedure has Return Code being Returned (True/False) ' Return Database Field Headings for returned columns? (True/False) ' Parameters (Array you supply list -or- at least a null (0) element) ' Delimiter Header -or- null ' Delimiter Trailer -or- null ' Delimiter Row Prefix -or- null ' Delimiter Row Suffix -or- null ' Delimiter Column Prefix -or- null ' Delimiter Column Suffix -or- null ' iRecsReturned = 0 sErrReturned = "" sErrDesc = "" Dim sSql Dim oRS Dim iRow Dim iCol Dim vDataArray Dim iInc On Error Resume Next sSql = "{" If RtnCd then sSql = sSql & "? = " End if sSql = sSql & "call " & SPName If Param(0) & "" <> "" then sSql = sSql & "(?" For iRow = 1 to UBound(Param)-1 If Param(iRow) & "" <> "" then sSql = sSql & ",?" End if Next sSql = sSql & ")" End if sSql = sSql & "}" oCmd.CommandText = sSql If Param(0) & "" <> "" then oCmd.Parameters(0).Value = Param(0) For iRow = 1 to UBound(Param)-1 If Param(iRow) & "" <> "" then oCmd.Parameters(iRow).Value = Param(iRow) End if Next End If Set oRS = oCmd.Execute if Err = 0 then If GetRws Then ' Not needed if DELETE, UPDATE or INSERT if Hdngs then vDataArray = DelimHeader & vDataArray & DelimRowPre For iInc = 0 to oRS.Fields.Count - 1 vDataArray = vDataArray & DelimColPre & oRS(iInc).Name & DelimColSuf Next vDataArray = vDataArray & DelimRowSuf End If vArray=oRS.GetRows For iRow = 0 to UBound(vArray,2) vDataArray = vDataArray & DelimRowPre For iCol = 0 to UBound(vArray,1) vDataArray = vDataArray & DelimColPre & vArray(iCol,iRow) & DelimColSuf Next vDataArray = vDataArray & DelimRowSuf Next If IsEmpty(vArray) or Not GetNumRcs Then iRecsReturned = 0 Else If GetNumRcs Then iRecsReturned = iRow End If End If set CallSP = nothing CallSP = vDataArray & DelimTrailer End If Else guid = server.createobject("scriptlet.typelib").guid ErrHandler oDB,True ' generic error handler End If oRS.Close set sSql = nothing set oRS = nothing set iRow = nothing set iCol = nothing set vArray = nothing set vDataArray = nothing set iInc = nothing End Function Sub ErrHandler(ByRef oDB, DumpCollection) Dim ADOErr Dim strError For Each ADOErr In oDB.Errors strError = "Error #" & ADOErr.Number & vbCrLf & ADOErr.Description _ & vbCr & _ " (Source: " & ADOErr.Source & ")" & vbCr & _ " (SQL State: " & ADOErr.SQLState & ")" & vbCr & _ " (NativeError: " & ADOErr.NativeError & ")" & vbCr If ADOErr.HelpFile = "" Then strError = strError & " No Help file available" & vbCr & vbCr Else strError = strError & " (HelpFile: " & ADOErr.HelpFile & ")" _ & vbCr & _ " (HelpContext: " & ADOErr.HelpContext & ")" & vbCr & vbCr End If If DumpCollection Then %><p><img src=".\images\err.gif"><p><font SIZE="2"><cite>Start of Debug Collection</cite></font><p><h3> ADO Error Collection </h3><% Response.Write guid '& " " & strError Response.Write "<p>" & strError End If Response.AppendToLog guid & " " & strError Next oDB.Errors.Clear Set ADOErr = Nothing Set strError = Nothing If DumpCollection Then DebugCollection End If End Sub Sub DebugCollection ' Append to IIS log for error tracking %> <h3> Application Variable Collection </h3> <% On Error Resume Next For Each Item in Application.Contents Response.Write Item & " = " & Application.Contents(Item) & "<BR>" Response.AppendToLog guid & " " & Item & " = " & Application.Contents(Item) & "<BR>" For Each ItemKey in Application.Contents(Item) Response.Write "Sub Item: " & Item & " (" & ItemKey & ") : " & Application.Contents(Item)(ItemKey) & "/n" Response.AppendToLog guid & " Sub Item: " & Item & " (" & ItemKey & ") : " & Application.Contents(Item)(ItemKey) & "/n" Next Next ' Key if err.no > 0 Then Display "You must upgrade to IIS 4.0 in order to use the Application Collection/n" %> <!-- All collections at the code below are available at IIS 3.0 --> <h3> Session Variable Collection </h3> <% On Error Resume Next For Each Item in Session.Contents Response.write Item & " = " & Session.Contents(Item) & "<BR>" Response.AppendToLog guid & " " & Item & " = " & Session.Contents(Item) & "<BR>" For Each ItemKey in Session.Contents(Item) Response.Write "Sub Item: " & Item & " (" & ItemKey & ") : " & Session.Contents(Item)(ItemKey) & "/n" Response.AppendToLog guid & " Sub Item: " & Item & " (" & ItemKey & ") : " & Session.Contents(Item)(ItemKey) & "/n" Next Next if err.no > 0 Then Display "You must upgrade to IIS 4.0 in order to use the Session Collection/n" %> <h3> QueryString Collection </h3> <% For Each Item in Request.QueryString For iCount = 1 to Request.QueryString(Item).Count Response.Write Item & " = " & Request.QueryString(Item)(iCount) & "/n" Response.AppendToLog guid & " " & Item & " = " & Request.QueryString(Item)(iCount) & "/n" Next Next %> <h3> Form Collection </h3> <% For Each Item in Request.Form For iCount = 1 to Request.Form(Item).Count Response.Write Item & " = " & Request.Form(Item)(iCount) & "/n" Response.AppendToLog guid & " " & Item & " = " & Request.Form(Item)(iCount) & "/n" Next Next %> <h3> Cookies Collection </h3> <% For Each Item in Request.Cookies If Request.Cookies(Item).HasKeys Then 'use another For...Each to iterate all keys of dictionary For Each ItemKey in Request.Cookies(Item) Response.Write "Sub Item: " & Item & "(" & ItemKey & ") = " & Request.Cookies(Item)(ItemKey) Response.AppendToLog guid & " Sub Item: " & Item & "(" & ItemKey & ") = " & Request.Cookies(Item)(ItemKey) Next Else 'Print out the cookie string as normal Response.Write Item & " = " & Request.Cookies(Item) & "/n" Response.AppendToLog guid & " " & Item & " = " & Request.Cookies(Item) & "/n" End If Next %> <h3> Client Certificate Collection </h3> <% For Each Item in Request.ClientCertificate For iCount = 1 to Request.ClientCertificate(Item).Count Response.Write Item & " = " & Request.ClientCertificate(Item)(iCount) & "/n" Response.AppendToLog guid & " " & Item & " = " & Request.ClientCertificate(Item)(iCount) & "/n" Next Next %> <h3> Server Variables Collection </h3> <% For Each Item in Request.ServerVariables For iCount = 1 to Request.ServerVariables(Item).Count Response.Write Item & " = " & Request.ServerVariables(Item)(iCount) & "/n" Response.AppendToLog guid & " " & Item & " = " & Request.ServerVariables(Item)(iCount) & "/n" Next Next %> <p><font SIZE="2"><cite>End of Debug Collection</cite></font> <% Set guid = Nothing End Sub Function GetDBConnection() On Error Resume Next Set GetDBConnection = Nothing Dim oConn, sConnStr sConnStr = SiteDSN set oConn = Server.CreateObject ("ADODB.Connection") if Err <> 0 then set oConn = Nothing On Error Goto 0 exit function end if oConn.Open sConnStr if Err <> 0 then set oConn = Nothing On Error Goto 0 exit function end if set GetDBConnection = oConn set oConn = Nothing End Function Function GetDBCommand(ByRef oDB) On Error Resume Next Set GetDBCommand = Nothing Dim oCommand set oCommand = Server.CreateObject ("ADODB.Command") if Err <> 0 then set oCommand = Nothing On Error Goto 0 exit function end if set oCommand.ActiveConnection = oDB if Err <> 0 then set oCommand = Nothing On Error Goto 0 exit function end if set GetDBCommand = oCommand set oCommand = Nothing End Function %>

Further additional information

n/a

---

Rate this article on a scale of 1 to 10 (0 votes, average 0)

Your vote : 1 2 3 4 5 6 7 8 9 10  

Updating...

<< SEO		.NET and XML (67)Ajax (2)BizTalk (55)CSS (114)JavaScript (64)LAMP (6)Microsoft ASP (90)PHP XML (89)reBlogger (0)Schemas, xsd, xdr (45)SEO (3)SQL XML (76)System.XML (39)Uncategorized (137)WCF, WS, SOAP (55)XALAN (22)XHTML (97)XML DOM (119)XmlSerializer (34)XQuery (24)XSLT (252)	System.XML >>

Updating comments...

---

Updating comments...

Leave a comment for this article
Your name
Your email (optional)
Your comment
Optional: Upload an attachment
Enter the code shown: