SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Generating ADO Parameters with Information Schema Views
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 12/09/2001 :  15:53:00  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
In this article I show how to use an Information Schema View to generate ADO
Parameter code. Even if you don't use ADO, you'll want to read the section that
describes Information Schema Views. If you work with SQL Server long enough, I
am positive you will eventually find a use for them.

Article Link.

hande
Starting Member

8 Posts

Posted - 12/10/2001 :  08:33:07  Show Profile  Reply with Quote
Well, innour company there was a student who wandered how to use parameters. He got so stuck after disgussing it with the one. How to deal with the parameter with ado/asp rather than ado/vb? Rather tricky question! hEllo to Alice anyway!

Go to Top of Page

Blastrix
Posting Yak Master

208 Posts

Posted - 12/11/2001 :  10:35:11  Show Profile  Reply with Quote
Sweet deal! That will save me loads of time! It works great for SQL Server 2000, but there doesn't appear to be a PARAMETERS view on either of our SQL 7 servers. Does anyone know how to create the PARAMETERS view for SQL Server 7? I tried just copying the one from 2000 but, 7 doesn't support CASE it appears.

Go to Top of Page

Argyle
Yak Posting Veteran

53 Posts

Posted - 12/11/2001 :  18:10:00  Show Profile  Reply with Quote
Really useful script. A minor bug though when you use for example output parameters using varchar. No comma will be added before the varchar size. But I guess it's not to hard to add manually :)

Go to Top of Page

M@rco
Starting Member

United Kingdom
3 Posts

Posted - 04/15/2004 :  07:31:09  Show Profile  Visit M@rco's Homepage  Reply with Quote
BTW, just thought I'd point out that if you use the Parameters.Refresh approach (which I do), you can access each parameter's name (as defined in the SP) by reading the .Name property of each Param object.

Here's a handy SP executing function that I wrote a while ago which exploits this technique (it will require a little work to make it usable, such as removing my debugging code and replacing calls to Kill() with your own object disposal code if you don't use the code that I posted on my [url=http://marcustucker.com/blog/]blog[/url]):
'Created:	12/12/2002
'Author:	Marcus Tucker
'Function:	Executes a stored procedure (parameters are passed as two 1D arrays)
'Modified:	17/12/2002	Marcus Tucker	Improved parameter adding code + added some error checking
'			29/04/2003	Marcus Tucker	Parameter passing changed to two separate arrays (eliminates dictionary creation & destruction)
Function ExecuteSP(ByRef Conn, ByVal SPName, ByVal ParamNames, ByVal ParamValues)
	Dim Command, RecordSet
	Dim ParamCount
	Dim SPParamCount, Param, Count, Str
	Dim ParamName, ParamValue, SkipParams
	
	'Debug.Print "ExecuteSP() - Attempting to execute '" & SPName & "'"
	SkipParams = False
	
	'Set up the Command object
	Set Command = Server.CreateObject("ADODB.Command")
	Set Command.ActiveConnection = Conn
	Command.CommandType = adCmdStoredProc
	Command.CommandText = SPName
	
	'Populate the parameters collection
	Command.Parameters.Refresh
	
	Select Case True
		Case IsBlank(ParamNames) And IsBlank(ParamValues)
			SkipParams = True
			
		Case (IsArray(ParamNames) And IsArray(ParamValues))
			'both are arrays, that's perfect, do nothing!
		
		Case Not (IsArray(ParamNames) And IsArray(ParamValues))
			'neither are arrays, but they aren't blank,
			'so assume they are single values expressed as strings and convert to arrays
			ParamNames = Array(ParamNames)
			ParamValues = Array(ParamValues)
		
		Case Else
			'something else - throw an error
			Debug.Die "ExecuteSP() - " & SPName & " - " & Parameters 'ParamNames' and 'ParamValues' must either both be arrays or strings"
	End Select

	If Not SkipParams Then
		ParamCount = UBound(ParamNames) + 1
		SPParamCount = Command.Parameters.Count
	
		Select Case True
			'SP doesn't exist
			Case SPParamCount < 1
				Debug.Die "ExecuteSP() - " & SPName & " - Invalid stored procedure name """ & SPName & """"
			
			'Number of parameters don't match
			Case (SPParamCount - 1) <> ParamCount
				Str = "ExecuteSP() - " & SPName & " - Number of parameters do not match! Required parameters are:<br />"
				For Each Param in Command.Parameters
					Str = Str & " - " & Param.Name & "<br />"
				Next
				Debug.Die Str
			
			'Fine, carry on
			Case Else
				'Loop through and set all the parameters that were supplied
				For Count = 0 to (ParamCount - 1)
					ParamName = ParamNames(Count)
					ParamValue = ParamValues(Count)
					Set Param = Command.Parameters("@" & ParamName)
					If Param.Size < Len(ParamValue) Then
						'Die with an explanation
						Debug.Die "ExecuteSP() - " & SPName & " - Parameter '" & ParamName & "' has maximum field length of " & Param.Size & ", value '" & ParamValue & "' has length of " & Len(ParamValue)
					End If
					Param.Value = ParamValues(Count)
				Next
		End Select
	End If

	'Set up the RecordSet object
	Set RecordSet = Server.CreateObject("ADODB.RecordSet")
	RecordSet.CursorLocation = adUseClient
	RecordSet.CursorType = adOpenStatic
	RecordSet.LockType = adLockReadOnly
	
	'Execute the Query
	RecordSet.Open Command
	
	'Dispose of the Command object
	Set Command.ActiveConnection = Nothing
	Kill Command
	
	'Return the RecordSet
	Set ExecuteSP = RecordSet
End Function


:D

Marcus Tucker - http://marcustucker.com/blog
Analyst Programmer / SPF Mentor / voted SPF ASP Guru 02/03 & 03/04
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 04/15/2004 :  07:52:44  Show Profile  Reply with Quote
Just remember the extra Network/SQL traffic generated!
this methodology should be used in development phase only..........

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=16533
Go to Top of Page

M@rco
Starting Member

United Kingdom
3 Posts

Posted - 04/15/2004 :  13:23:31  Show Profile  Visit M@rco's Homepage  Reply with Quote
Oh, absolutely, nothing comes free, and a cost/benefit analysis shouldn't be overlooked...

But surely 99% of the time the extra traffic that this generates is insignificant compared to the volume of real data returned when the SP is called?!

Marcus Tucker - http://marcustucker.com/blog
Analyst Programmer / SPF Mentor / voted SPF ASP Guru 02/03 & 03/04
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 04/16/2004 :  05:22:19  Show Profile  Reply with Quote
The real benefit is only achieved during a development/code modification phase....by including the above, you can debug your code faster...by proving you have the right type/length/order of parameters....but once the code is 'fixed/launched', then there is no benefit to the end user in having the VB/ASP code scanning the SP looking for a full list of parameters....at that stage they are already known and coded for.....all the end user gets is an unecessary performance hit.


Maybe you could put the 'parameters.refresh' inside...a 'to be executed during testing only' IF construct, so that you could get the best of both worlds....a consistant coding style that helps the coding process...while not affecting adversely the end-user when in production.


Remember if this code is execute 10000 times a day, then that's 10000 unnecessary calls to scan the SP for parameters.....and that performance hit all adds up.
Go to Top of Page

M@rco
Starting Member

United Kingdom
3 Posts

Posted - 04/16/2004 :  05:46:18  Show Profile  Visit M@rco's Homepage  Reply with Quote
quote:
Originally posted by AndrewMurphy

Remember if this code is execute 10000 times a day, then that's 10000 unnecessary calls to scan the SP for parameters.....and that performance hit all adds up.
Yes, very true. Unfortunately I'm working in an environment where I don't have the luxury of squeezing every drop of performance out of an application, as much as I'd like to. So for my purposes it's an acceptable compromise between ease of development and performance. But for others like yourself, it may well be one performance hit too many.

The hardcoded approach that is the subject of the article requires that you create a separate COM object for each parameter, set property values, then add them to the Parameters collection and only THEN call the SP on the SQL Server. This therefore incurs some performance hit on the web server, but then minimal extra cost (i.e. only the cost of the actual query) on the SQL Server itself.

The code I posted above creates fewer objects and populates fewer properties, so I would have thought that the web server performance hit is less, but then the extra roundtrip to the SQL Server incurs an extra cost there.

Finally, the other alternative is to avoid using the Parameters collection completely and to use the "EXEC SPName Param1, Param2 Param3" syntax. I would have thought that this would incur a minimal performance hit on the web server, but some on the the SQL Server, as it has to parse the string to obtain the parameters. I suspect that this extra SQL Server-side cost would still be less than that caused by the Parameters.Refresh roundtrip.

IMHO all are perfectly valid approaches - it simply all depends on what you're factors you're trying to maxmise: ease of development, web server performance, db performance, or network performance! You can't win them all! :D

Marcus Tucker - http://marcustucker.com/blog
Analyst Programmer / SPF Mentor / voted SPF ASP Guru 02/03 & 03/04
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000