In a previous message you wrote:
Here is exactly what I am doing...
Stored Procedure
CREATE procedure dbo.getdata_wayne
@doc as ntext, @sql_string as ntext
AS
declare @idoc int
-- Parse XML String
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Get Data
EXEC (@sql_string)
-- Select data
-- Remove XML document
EXEC sp_xml_removedocument @idoc
GO
Call to the sp
exec getdata_wayne '99WCAL138462',
'SELECT PREMIUMHEADER.CPOLICYNUM FROM OPENXML (@@idoc, "/A/B",2)
WITH (I varchar(16)) AS JT ,
PREMIUMHEADER WHERE PREMIUMHEADER.CPOLICYNUM = JT.I'
I see no benefit to this. You're sending the query and arguments to the proc for it to be executed. The proc is just wrapping an ad-hoc query. You might as well move this behavior to a data-access component and let it construct and submit the query.
-Mike