August 1, 2012 at 4:27 pm
I'm stumped.. I have a fairly complex sproc that is expecting a single record identifier to be passed in, and in return it will spit out all kinds of nifty data related to that record to a UI. For reasons probably not worth going in to, I need to output to a table with each value that the sproc is sending to the UI for for many records.
For the purposes of this post, let's assume that creating a query that essentially does what the sproc does is not an option in this case.
Obviously I can execute the sproc passing in a single record id value and get all the associated data values for that one record inserted in to a table. How do I pass in many record IDs so that the output represents all the field values for each of the record IDs
The solution needs to work in SQL2005. Any ideas that don't involve rocket science?
Thanks
August 1, 2012 at 4:41 pm
Here is an example:
exec [dbo].[utl_OrganizationByAttribute_s] --returns all clients
exec [dbo].[utl_OrganizationByAttribute_s] @OrganizationAttributeTypeID = NULL --returns all Clients
exec [dbo].[utl_OrganizationByAttribute_s] @OrganizationAttributeTypeID = '1' --A type Clients
exec [dbo].[utl_OrganizationByAttribute_s] @OrganizationAttributeTypeID = '1, 2' --A type and B type clients
CREATE PROCEDURE [dbo].[utl_OrganizationByAttribute_s]
@OrganizationAttributeTypeID varchar(500) = NULL
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE
@ErrorMessage AS nvarchar(250)
,@ErrorSeverity AS int
,@SprocName varchar(100) = DB_NAME(DB_ID()) + '.' + SCHEMA_NAME(SCHEMA_ID()) + '.' + OBJECT_NAME(@@PROCID,DB_ID())
BEGIN TRY
;WITH CTE_SelectOrgList
AS
(
SELECT
oa.OrganizationID
FROM
dbo.Utl_Split(',', @OrganizationAttributeTypeID) AS oatSplit
JOIN dbo.OrganizationAttrib AS oa ON oa.OrganizationAttribTypeID = CONVERT(smallint, oatSplit.SeparatedValue)
)
SELECT DISTINCT
o.OrganizationID
,o.OrgName
,o.LabAccountNumber
FROM
dbo.Organization AS o
LEFT JOIN CTE_SelectOrgList AS sol ON o.OrganizationID = sol.OrganizationID
WHERE
o.OrganizationID = CASE
WHEN @OrganizationAttributeTypeID IS NULL THEN o.OrganizationID
ELSE sol.OrganizationID
END
ORDER BY
o.OrgName
END TRY
BEGIN CATCH
SET @ErrorMessage = @SprocName + ' Error: ' + CONVERT(nvarchar(10), ERROR_NUMBER()) + ' Line: '
+ CONVERT(nvarchar(5), ERROR_LINE()) + ' - ' + ERROR_MESSAGE()
SET @ErrorSeverity = ERROR_SEVERITY()
RAISERROR(@ErrorMessage, @ErrorSeverity, 1)
END CATCH
SET NOCOUNT OFF;
END
ALTER FUNCTION [dbo].[utl_Split]
(
@Seperator char(1)
,@StringToSplit varchar(MAX)
)
RETURNS @OutputTable TABLE
(
ID int
,SeparatedValue varchar(MAX)
)
AS
BEGIN
DECLARE @String varchar(MAX),@StringInput varchar(MAX),@ID int
SET @StringInput=@StringToSplit
SET @ID=0
WHILE LEN(@StringInput) > 0
BEGIN
SET @String = LEFT(@StringInput,
ISNULL(NULLIF(CHARINDEX(@Seperator, @StringInput) - 1, -1),
LEN(@StringInput)))
SET @StringInput = SUBSTRING(@StringInput,
ISNULL(NULLIF(CHARINDEX(@Seperator, @StringInput), 0),
LEN(@StringInput)) + 1, LEN(@StringInput))
SET @ID=@ID+1
INSERT INTO @OutputTable
(
ID
,SeparatedValue
)
VALUES
(
@ID
,@String
)
END
RETURN
END
August 2, 2012 at 9:30 am
Unfortunately it is hard to help you when we can't see what you see. If you would post the DDL (CREATE TABLE statement) for the table or tables involved, some sample data (series of INSERT INTO statements) for the table or tables, the expected results (preferably in a table format, not just a word description) based on the sample data, and your current code we should be able to provide you an answer fairly quickly.
August 2, 2012 at 9:37 am
As Lynn said you didn't provide much to go on but an extremely vague shadow of an idea. I think that the gist of your question is "How can I pass an array in SQL 2005?". Short answer, you can't. You can however do some things to work around it. In 2008 they introduced table parameters which is awesome but in 2005 you don't have this.
What you can do is pass in a delimited string of primary key values. Then use the concepts discussed in the article in my footer about splitting strings to parse it out.
If need a more detailed explanation your are going to have to provide a more detailed question.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 2, 2012 at 9:44 am
You're right, this is about passing an array, and I saw and was happy to see the improvement in 2008 with table parameters... and if I had a 2008 environment up, I would be in much better shape.
Thanks for the link to the article.. I'll read through it and may ping you back with more details if I'm still in the weeds...
August 2, 2012 at 3:35 pm
You could also pass the "array" in as an XML snippet and shred it in the SP if that's an option? Just an idea.
August 2, 2012 at 4:11 pm
I'll second (third?) the delimited string splitter technique and use an inline table function for a single parameter set.
XML is an excellent alternative in 2k5 for when you have pairs (or more) of associated data that needs to be passed down and you'd be forced to multi-split the string to rows then columns.
For those curious, I've done a bit of dinking around with the table parameter passing (particularly trying to see if I can get our infuriating ORM to behave its damned self in a way the devs won't push so hard back on). I've caused serious optimization shortfalls at the same level of table variables in general. No statistics, etc. These are avoided by taking the table parameter and stuffing it into an indexed #temp. Just throwing that out there.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 3, 2012 at 1:34 pm
Thanks everyone, this gives me some great stuff to play with.. I suspect in any case I will need to wrap this sproc with another sproc that can feed the needed parameter set. Could use that to shred the xml array... I haven't used the delimited string splitting so I'm looking forward to dinking around with that..
August 3, 2012 at 1:39 pm
Robin Riversong (8/3/2012)
Thanks everyone, this gives me some great stuff to play with.. I suspect in any case I will need to wrap this sproc with another sproc that can feed the needed parameter set. Could use that to shred the xml array... I haven't used the delimited string splitting so I'm looking forward to dinking around with that..
As Craig said, unless you need to parse value pairs the delimited split is going to be a better choice performance wise. Hopefully we have pointed in the direction to get you the answers you need.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 8, 2012 at 4:09 am
Here's a link to an article that covers passing in delimited strings to substitute for an array:
http://www.sqlservercentral.com/articles/T-SQL/63003/
Another great one by Jeff Moden!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply