Friday, March 16, 2007

Stored Procedure Parameters - String limitation

In some scenario, we need to pass long string to the stored procedure.
My case is that we need to pass selected checkbox options value to the stored procedure.
Sometimes when the list of checkbox is very long, the parameter string value can exceed varchar(8000 characters).

Example of the parameter : '(''chkvalue1'',''chkvalue2'',''chkvalue3''.......)'

My solution :
1. Instead of constructing the values for the query, we change the strategy to use xml string format for the parameter.
Example of the new parameter : '<root><param value="chkvalue1" /><param value="chkvalue2" /><param value="chkvalue3" />.....</root>'
2. Change the data type for the parameter from varchar(8000) to text
3. Use OPENXML to get the values from the xml :
SELECT value
INTO ##temptable
FROM OPENXML (@idoc, '/root/param',1)
WITH (value varchar(100))
4. Use this temp table to achieve the same result


Post a Comment