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: 212, 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 #1338887

Lynn Pettis

Posted Wednesday, August 01, 2012 4:32 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 9:53 PM
Points: 21,588, Visits: 27,382

Please don't double post. Please post replies here.

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 #1338889

chella.subramanian

Posted Wednesday, August 01, 2012 4:40 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 #1338890

Robin Riversong

Posted Thursday, August 02, 2012 9:02 AM

SSC Veteran

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

Hi Chella,

Please excuse me if I appear dense here...

It looks like you end up with a table variable, I don't think TVs can be passed in to a Sproc, at least not in SQL2005.. Am I missing something?

I need to solve for this

INSERT INTO #tempTable (recordID, attribute1, attribute2, attribute3 etc...)
exec mysproc x

Where x is a single existing non sequential record ID passed in. If I specify x explicitly like - exec mysproc 1234 - I will get one row in the temp table.. which is great, except that I need rows in the temp table for thousands of records.

So the problem is how to pass those thousands of existing record IDs in to myexistingsproc for the insert given that the sproc can not consume more than one record ID parameter passed in at a time..

Thanks to anyone who can help me understand how to get from here to there.. Blink

Post #1339256

chella.subramanian

Posted Thursday, August 02, 2012 10:58 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, September 05, 2012 1:09 PM
Points: 3, Visits: 10

exec [dbo].[utl_OrganizationByAttribute_s] @OrganizationAttributeTypeID = '1, 2' --A type and B type clients

The parameter can accept any number of ids, you have to pass it as comma seperated!
In the above example there are two ids 1 & 2

I have tested this with thousands of ids as a comma seperated and it just works fine.

Post #1339378

« Prev Topic | Next Topic »

Permissions