Select from stored proc for many records where single record ID being passed in

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Select from stored proc for many records...

Select from stored proc for many records where single record ID being passed in

Rate Topic

Display Mode

Topic Options

Author

Message

Robin Riversong

Posted Wednesday, August 01, 2012 4:27 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:19 AM
Points: 213, Visits: 211

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

chella.subramanian

Posted Wednesday, August 01, 2012 4:41 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, September 05, 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

Lynn Pettis

Posted Thursday, August 02, 2012 9:30 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 10:00 PM
Points: 21,635, Visits: 27,493

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.

Cool

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

Sean Lange

Posted Thursday, August 02, 2012 9:37 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 1:17 PM
Points: 8,641, Visits: 8,273

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

Post #1339299

Robin Riversong

Posted Thursday, August 02, 2012 9:44 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:19 AM
Points: 213, Visits: 211

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

Jan Van der Eecken

Posted Thursday, August 02, 2012 3:35 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:12 AM
Points: 2,269, Visits: 5,960

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.

--------------------------------------------------------------------------

The function of good software is to make the complex appear to be simple. (Grady Booch)

Post #1339546

Evil Kraig F

Posted Thursday, August 02, 2012 4:11 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:05 AM
Points: 5,688, Visits: 6,142

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

Robin Riversong

Posted Friday, August 03, 2012 1:34 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:19 AM
Points: 213, Visits: 211

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

Sean Lange

Posted Friday, August 03, 2012 1:39 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 1:17 PM
Points: 8,641, Visits: 8,273

Robin Riversong (8/3/2012)

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

Post #1340089

dwain.c

Posted Wednesday, August 08, 2012 4:09 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:24 PM
Points: 2,346, Visits: 3,192

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!

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1341770

« Prev Topic | Next Topic »

Permissions