March 13, 2012 at 3:05 am
Hi Friends,
I am facing a challenge. I have a column in a temp table and I have to pass that column of temp table into another SP's Parameter.
declare @newlist varchar(max);
set @newlist = 'SELECT new_list FROM #SortFinalDistinct'
EXEC (@newlist)
This Statement gives 2 rows.
new_list
27, 37
47, 48, 49, 92, 93, 94, 95, 96, 107, 108
I have the SP parameters like this.
EXEC [TEC].[SPW_MCP_MAJ_ACTIONS_LIST]
NULL,
NULL,
'DependenciesInfiniteLoop',
'Dependencies Infinite Loop detected on the sources ' +@newlist,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
0
This SP [TEC].[SPW_MCP_MAJ_ACTIONS_LIST] writes / inserts a line in a table called RESULT.
Problem here is as the result is two rows, how can pass both of them and get them inserted in two rows of the RESULT table.
Thanks in advance for the suggestion and help...
March 13, 2012 at 3:36 am
I don't think you can do it by passing more than one row in, I'm afraid. Whenever I've come across the requirement to call a sproc based on more than one row I do it with a cursor. For the record I'm not a fan of cursors but, in this situation, I don't think you've got much choice.
March 13, 2012 at 3:40 am
There are a lot of ways to do it.
I would rather pass the two rows as a string and split the strings inside the calling SP.
Jeff Moden has got a fantastic tool to split the string.
Please have a look.
http://www.sqlservercentral.com/Forums/FindPost452711.aspx
--- Babu
March 13, 2012 at 4:07 am
You're on SQL2008: why don't you use table valued parameters?
-- Gianluca Sartori
March 13, 2012 at 4:36 am
Gianluca's response is the correct one.
However if you can't or don't want to use a table variable as a parameter, there are two other choices where you would need to change the type of the parameter you're passing in to either XML or INT:
DECLARE @tbl TABLE (s VARCHAR(max))
DECLARE @my_xmlXML
,@myxmlNoINT
INSERT INTO @tbl
SELECT 'Dependencies Infinite Loop detected on the sources '+ '27, 37' as s
UNION ALL SELECT 'Dependencies Infinite Loop detected on the sources '+'47, 48, 49, 92, 93, 94, 95, 96, 107, 108'
-- Opetion 1: Construct an XML document
SET @my_xml =
(SELECT s
FROM @tbl
FOR XML RAW('str'), ROOT ('s'), TYPE)
-- Pass the document into your SP as a parameter
-- Then extract the two rows as follows
SELECT x.s.value('@s[1]', 'VARCHAR(MAX)') AS s
FROM @my_xml.nodes('//s/str') AS x(s)
-- Opetion 2: Prepare the XML document
EXEC sp_xml_preparedocument @myxmlNo OUTPUT, @my_xml
-- Pass a handle to the XML document into your SP as a parameter
-- Then extract the two rows as follows
SELECTs
FROMOPENXML (@myxmlNo, '/s/str',1)
WITH (s VARCHAR(max))
EXEC sp_xml_removedocument @myxmlNo;
In practice you may find that option 2 is more efficient (but you can't do it in a FUNCTION).
To all critics in advance: Please don't shoot me for suggesting this. It is just a suggestion. Remember I said that I think the table variable parameter is the right choice.
Not sure if "handle to the XML document" is the correct terminology but that is the way I understand it.
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
March 13, 2012 at 4:53 am
Here's an interesting set of articles from Erland Sommarskog's site:
http://www.sommarskog.se/arrays-in-sql.html
-- Gianluca Sartori
March 13, 2012 at 5:05 am
Here's an interesting set of articles from Erland Sommarskog's site:
Those are good articles. I believe that is where I got the idea for using XML as I did in my example.
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
March 13, 2012 at 8:09 am
Thanks Dwain. I will try your idea too... but for the need of the hour -- resolved it with while loop...
DECLARE @RESULT TABLE
(
ID INT IDENTITY(1,1),
ACT_TYPE varchar(100),
LIST varchar(8000),
START_DAT datetime,
ACT_UP_DAT datetime,
ACT_AUTO int
)
INSERT INTO @RESULT (ACT_TYPE,LIST,START_DAT,ACT_UP_DAT,ACT_AUTO)
SELECT 'Dependencies Infinite Loop' AS ACT_TYPE,
'Dependencies Infinite Loop detected on the sources ' + new_list AS DISTINCT_LIST,
GETDATE() AS ACT_START_DAT,
GETDATE() AS ACT_UPDATE_DAT,
0 AS ACT_AUTOfrom #SortFinalDistinct
declare @ACT_TYPE varchar(100),@LIST varchar(8000), @ID int, @RecordCnt int;
SET @ID = 1
SELECT @RecordCnt = COUNT(*) FROM @RESULT
While @ID <= @RecordCnt
BEGIN
SELECT @ACT_TYPE = ACT_TYPE,@LIST = LIST FROM @RESULTwhere ID = @ID
EXEC [TEC].[SPW_MCP_MAJ_ACTIONS_LIST]
NULL,
NULL,
@ACT_TYPE,
@LIST,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
0
SELECT @ID = @ID+1
END
March 13, 2012 at 8:35 am
You're on SQL2008: why don't you use table valued parameters?
I hadn't realised that you could pass table variables as parms. That's a huge improvement for me and definately beats repeated calls in a cursor.:-D
March 13, 2012 at 6:17 pm
Thanks Dwain. I will try your idea too... but for the need of the hour -- resolved it with while loop...
You're welcome. But read my mantra 🙂
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