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:40 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:02 am
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:
August 2, 2012 at 10:58 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply