August 10, 2010 at 7:06 am
Be careful with the code in the article. Be aware that it doesn't do full validation. It only evaluates what's right and give no indication as to what is wrong. If that's good for you, so be it (although I believe there are better ways). Just make sure it's good for you. 😉
The following example has been copied from the article code. "XX" has been added to the parameter. See for yourself that "XX" produces no error and no alert because "XX" isn't even considered in the validation.
-- Imagine this table exists previously in your database ----
Declare @T_STATES table ( state char(2) )
Insert @T_STATES Values ( 'AZ' )
Insert @T_STATES Values ( 'CA' )
Insert @T_STATES Values ( 'MT' )
Insert @T_STATES Values ( 'TX' )
Insert @T_STATES Values ( 'WA' )
Insert @T_STATES Values ( 'TS' )
-- Suppose this is the parameter ----
Declare @sParameter varchar(8000)
Set @sParameter = 'WA,AZ,CA,TS,XX' --<<< Code doesn't catch the invalid parameter
Declare @tStates table ( state char(2) )
Insert @tStates
Select state
From @T_STATES
Where CharIndex( state, @sParameter ) > 0
Select *
From @tStates
Even the final bit of code doesn't catch the fact that an invalid element was passed...
-- Imagine this table exists previously in your database ----
Declare @T_STATES table ( state char(2) )
Insert @T_STATES Values ( 'AZ' )
Insert @T_STATES Values ( 'CA' )
Insert @T_STATES Values ( 'MT' )
Insert @T_STATES Values ( 'TX' )
Insert @T_STATES Values ( 'WA' )
Insert @T_STATES Values ( 'TS' )
-- Suppose this is the parameter ----
Declare @sParameter varchar(8000)
Set @sParameter = 'WA,AZ,CA,TS,XX' --<<< Code doesn't catch the invalid parameter
Declare @tStates table ( state char(2) )
Insert @tStates
Select state
From @T_STATES
Where CharIndex( state, @sParameter ) > 0
If @@RowCount < 1
Begin
Select 'No matches. Valid values are:' As InfoMessage
Union All
Select state
From @T_STATES
Return
End
--Jeff Moden
Change is inevitable... Change for the better is not.
August 10, 2010 at 7:21 am
something like:
set @XMLParameters =
'<OrderGroup>
<Item TargetPrinterType="DIAMOND_X" GroupStatus="ACTIVE_cc" ReferenceNo="Joe" />
</OrderGroup>'
August 10, 2010 at 7:22 am
With SQL2008 you do have the option of a table variable. I was using XML and found that the table variable provided all the flexibility but was much faster.
August 10, 2010 at 7:24 am
tom.groszko (8/10/2010)
With SQL2008 you do have the option of a table variable. I was using XML and found that the table variable provided all the flexibility but was much faster.
Any chance of you posting some code that others can run to demonstrate that?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 10, 2010 at 7:26 am
jcboyer-1091017 (8/10/2010)
something like:set @XMLParameters =
'<OrderGroup>
<Item TargetPrinterType="DIAMOND_X" GroupStatus="ACTIVE_cc" ReferenceNo="Joe" />
</OrderGroup>'
Thanks, Jerome...
--Jeff Moden
Change is inevitable... Change for the better is not.
August 10, 2010 at 7:30 am
TheSQLGuru (8/10/2010)
Didn't we have a MASSIVE thread here on SSC.com on string parsing, with amazing code samples and wonderful benchmarks??
We did. I tried to voice an objection about using all the same data for all the rows in any given test table (delimiters ALL in the same position causes lightning speed due to the way things are cached) but no one listened. All is not as it seems on that thread because of the test data that was used. As a result, I'd recommend retesting any code found in that thread before you use it.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 10, 2010 at 7:38 am
I see what you are saying Jeff, but would you want to do validation if the application did not allow the end user to send invalid data? perhaps using a drop down list.
Kevin G. Boles
Did you find the link you were thinking about, I would love read the massive discussion .
August 10, 2010 at 7:41 am
Jeff Moden (8/10/2010)
...For all of you good folks using XML to split things... I suppose that XML either does something special for you or that your consider its performance to be good enough. Just be aware that there are faster methods than XML... sometimes a lot faster.
...
Using the value() and nodes() method of the XML datatype is on par with other techniques performance wise, assuming you parse it only once, but it probably depends on the complexity of the xml string. Of course, regardless of the method used to pass the data to the stored procedure, the first step should be to get the data into a table variable. Joining an XML variable or UDF in a SQL SELECT, in such a way that it gets called interatively, is where the big performance hit occurs.
In many cases, the data being passed to the stored procedure parameter orginates as an XML document in the application, so passing the XML as is eliminates the need to parse and transform on the application tier. The comma seperated string method simply doesn't allow for any complexity.
Another thing to consider about the XML variable method is that it's simple, clean, and standard. You can parse your parameter using a single line of code using SQL Server supplied methods, and the next developer who inherits your code won't have to study or tweak the programming in whatever UDF or CRL function would otherwise have been used to parse comma seperated data.
declare @customers xml; select @customers = '<id>120</id><id>98</id><id>157</id>';
select customers.id.value('.','int') as customer_id from @customers.nodes('id') as customers(id);
-----------
120
98
157
I've seen some ugly and buggy examples of multiple valued list programming. What's the worst is when a UDF is not even employed but rather a block of LBAL stuff is copied and customized in 20 different stored procedures.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 10, 2010 at 7:53 am
I would like to add my $0.02 - XML data type parameter looks even better considering the fact that it could be STRONGLY TYPED (bound to a particular XML schema). Plus in SQL 2008 you could pass a table as parameter!
August 10, 2010 at 8:04 am
I like you solution 🙂 simply and easy 🙂
But I've a question.
How can I get those values those are not in State table?
August 10, 2010 at 8:16 am
I still like the xml version it's so easy even in SSRS
August 10, 2010 at 8:22 am
I prefer to use XML for this type of problem. However, i would only do this when the number of parameter values is relatively small.
Think great, be great!
August 10, 2010 at 8:30 am
I do agree, the XML are simpler, and I use them for my new system.
but we also maintain old systems and replace XML instead comma delimited parameters means:
1.- Fix code for every Web Services at .Net
2.- Recompile
3.- Fix every java function at Client Side
4.- Reset the IIS
5.- Fix Stored Procedures at SQL
Implementing this kind solution means only to fix code in the Stored Procedures
August 10, 2010 at 9:21 am
A simple example of using a table valued parameter from C#.
USE AdventureWorks;
go
CREATE SCHEMA Demo;
GO
IF EXISTS(SELECT 1 FROM sys.types WHERE name = 'PersonTable' AND is_table_type = 1 AND SCHEMA_ID('Demo') = schema_id)
DROP TYPE Demo.PersonTable;
go
CREATE TYPE Demo.PersonTable AS TABLE
( FirstNameVARCHAR(255)
,MiddleNameVARCHAR(255)
,LastNameVARCHAR(255)
);
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID('Demo.SearchForPeople') AND TYPE = 'P ')
DROP PROCEDURE Demo.SearchForPeople;
GO
CREATE PROCEDURE Demo.SearchForPeople
( @Person Demo.PersonTable READONLY
)
AS
SET NOCOUNT ON;
SELECT ContactID
,EmailAddress
FROM Person.ContactContact
JOIN @PersonPersonON Contact.FirstName =Person.FirstName
AND Contact.MiddleName= Person.MiddleName
AND Contact.LastName= Person.LastName;
RETURN 0;
GO
{try
{System.Data.SqlClient.SqlConnectionConnection=null;
System.Data.SqlClient.SqlConnectionStringBuilderConnectionStringBuilder=null;
System.Data.SqlClient.SqlCommandSQLCommand=null;
System.Data.SqlClient.SqlDataReaderSQLReader=null;
DataTablePersonTable=null;
ConnectionStringBuilder = new SqlConnectionStringBuilder();
ConnectionStringBuilder.DataSource= @"TOMGROSZKO-PC\SQL2008R2";
ConnectionStringBuilder.InitialCatalog= "AdventureWorks";
ConnectionStringBuilder.IntegratedSecurity=true;
Connection = new SqlConnection(ConnectionStringBuilder.ConnectionString);
PersonTable = new DataTable("PersonTable");
PersonTable.Columns.AddRange(new System.Data.DataColumn[]
{ new System.Data.DataColumn("FirstName",typeof(String))
,new System.Data.DataColumn("MiddleName",typeof(String))
,new System.Data.DataColumn("LastName",typeof(String))
}
);
PersonTable.Rows.Add(new object[] {"Catherine", "R.", "Abel"});
PersonTable.Rows.Add(new object[] {"Frances", "B.", "Adams"});
PersonTable.Rows.Add(new object[] {"Gregory", "F.", "Alderson"});
PersonTable.Rows.Add(new object[] {"Ramona", "J.", "Antrim"});
PersonTable.Rows.Add(new object[] {"Robert", "A.", "Avalos"});
Connection.Open();
SQLCommand = Connection.CreateCommand();
SQLCommand.CommandText = "Demo.SearchForPeople";
SQLCommand.CommandType = CommandType.StoredProcedure;
SQLCommand.Parameters.Add ("@Person", System.Data.SqlDbType.Structured);
intPARMPerson= SQLCommand.Parameters.Count -1;
SQLCommand.Parameters[PARMPerson].Direction= System.Data.ParameterDirection.Input;
SQLCommand.Parameters[PARMPerson].IsNullable= false;
SQLCommand.Parameters[PARMPerson].Value= PersonTable;
intCOLContactID =System.Int32.MinValue;
intCOLEmailAddress =System.Int32.MinValue;
SQLReader = SQLCommand.ExecuteReader();
boolReaderState = SQLReader.Read();
while(ReaderState == true)
{if(COLContactID==System.Int32.MinValue)
{COLContactID =SQLReader.GetOrdinal("ContactID");
COLEmailAddress =SQLReader.GetOrdinal("EmailAddress");
}
Int32?ContactID =(Int32?) SQLReader.GetSqlInt32 (COLContactID);
StringEmailAddress=(String) SQLReader.GetSqlString (COLEmailAddress);
Console.WriteLine (ContactID.ToString() + " " + EmailAddress);
ReaderState = SQLReader.Read();
}
Connection.Close();
}
catch (Exception OOPS)
{Console.WriteLine(OOPS.Message);
throw;
}
finally
{
}
}
August 10, 2010 at 9:41 am
Appreciate the post - but solution is much too limiting (and not worth implementing).
Table valued parameters are a good alternative.
XML is a good alternative
Viewing 15 posts - 16 through 30 (of 124 total)
You must be logged in to reply to this topic. Login to reply