Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select from stored proc for many records where single record ID being passed in Expand / Collapse
Author
Message
Posted Wednesday, August 1, 2012 4:27 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:05 PM
Points: 220, Visits: 261
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




Post #1338885
Posted Wednesday, August 1, 2012 4:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 5, 2012 1:09 PM
Points: 3, Visits: 10
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
Post #1338891
Posted Thursday, August 2, 2012 9:30 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:40 PM
Points: 20,687, Visits: 32,295
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1339290
Posted Thursday, August 2, 2012 9:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 12,946, Visits: 12,355
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1339299
Posted Thursday, August 2, 2012 9:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:05 PM
Points: 220, Visits: 261
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...



Post #1339310
Posted Thursday, August 2, 2012 3:35 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:03 AM
Points: 2,116, Visits: 6,438
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.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #1339546
Posted Thursday, August 2, 2012 4:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:53 PM
Points: 5,383, Visits: 7,456
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.



- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1339559
Posted Friday, August 3, 2012 1:34 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:05 PM
Points: 220, Visits: 261
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..


Post #1340081
Posted Friday, August 3, 2012 1:39 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 12,946, Visits: 12,355
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1340089
Posted Wednesday, August 8, 2012 4:09 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:12 PM
Points: 3,418, Visits: 5,336
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1341770
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse