sql server programming: How to pass resultset as param to SP -- sql server programming

You're in the

sql server programming

group:

How to pass resultset as param to SP

How to pass resultset as param to SP

moondaddy
11/6/2006 11:53:07 PM

sql server programming: from a trigger I want to pass all of the ID values from the deleted table or
inserted table to another stored procedure for additional processing.

for example, if I use this select statement:

SELECT ID from inserted

I would get
ID
--
1
2
3
4

I want to pass these into an sp to use in it's where clause something like:

select aa,bb,cc
from SomeTable
WHERE ID IN (1,2,3,4)

What would be the best way to approach this problem?

Im using sql 2005.

Thanks.

--
moondaddy@noemail.noemail

Re: How to pass resultset as param to SP

Uri Dimant
11/7/2006 12:00:00 AM

Hi

--inside the trigger

select aa,bb,cc
from SomeTable
WHERE ID IN (select id from deleted where SomeTable.id=deleted.id)

Also take a look at Dejan's script

IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
DROP FUNCTION dbo.TsqlSplit
GO

CREATE FUNCTION dbo.TsqlSplit
(@List As varchar(8000))
RETURNS @Items table (Item varchar(8000) Not Null)
AS
BEGIN
DECLARE @Item As varchar(8000), @Pos As int
WHILE DATALENGTH(@List)>0
BEGIN
SET @Pos=CHARINDEX(',',@List)
IF @Pos=0 SET @Pos=DATALENGTH(@List)+1
SET @Item = LTRIM(RTRIM(LEFT(@List,@Pos-1)))
IF @Item<>'' INSERT INTO @Items SELECT @Item
SET @List=SUBSTRING(@List,@Pos+DATALENGTH(','),8000)
END
RETURN
END
GO

/* Usage example */
SELECT t1.*
FROM TsqlSplit('10428,10429') AS t1

declare @inList varchar(50)
set @inList='10428,10429'

select od.* from [order details] od
INNER JOIN
(SELECT Item
FROM dbo.TsqlSplit(@InList)) As t
ON od.orderid = t.Item

[quoted text, click to view]

Re: How to pass resultset as param to SP

changliw NO[at]SPAM online.microsoft.com
11/7/2006 12:00:00 AM

Hi,
Thanks for using Microsoft Managed Newsgroup.

I understand that you wanted to have the query result appended to a query
in a stored procedure as a condition.
I recommend that you use CURSOR to fetch the row item vaules into a string,
here is an example for your reference:
=============================================
declare @shipperID varchar(10)
declare @shipperIDs varchar(100)
set @shipperID=''
set @shipperIDs=''
DECLARE Shippers_Cursor CURSOR FOR
SELECT ShipperID
FROM Shippers

OPEN Shippers_Cursor

FETCH NEXT FROM Shippers_Cursor
INTO @shipperID
WHILE @@FETCH_STATUS = 0
BEGIN
if @shipperID<>''
begin
set @shipperIDs = @shipperIDs + @shipperID +','
end
FETCH NEXT FROM Shippers_Cursor INTO @shipperID
END
SET @shipperIDs = SUBSTRING(@shipperIDs,1,LEN(@shipperIDs)-1)
CLOSE Shippers_Cursor
DEALLOCATE Shippers_Cursor
SELECT @shipperIDs
exec proc_queryShipper @shipperIDs
================================================

CREATE PROCEDURE proc_queryShipper
(
@strCondition varchar(100)
)
AS
declare @strSQL varchar(200)
SET @strSQL= 'SELECT * FROM SHIPPERS WHERE ID IN (' + @strCondition +')'
exec @strSQL
===============================================

Hope this helpful.

If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.

Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Re: How to pass resultset as param to SP

Uri Dimant
11/7/2006 12:00:00 AM

Charles

Actually ,there is no need a cursor

CREATE TABLE #t (c INT NOT NULL )---I specify NOT NULL , otherwise we need
to use COALESCE to handle NULLS

INSERT INTO #t VALUES (50)
INSERT INTO #t VALUES (10)
INSERT INTO #t VALUES (40)

DECLARE @st VARCHAR(50)
SET @st=''
SELECT @st=@st+CAST(c AS VARCHAR(50)) +',' FROM #t

SELECT LEFT(@st,LEN(@st)-1)

[quoted text, click to view]

Re: How to pass resultset as param to SP

Erland Sommarskog
11/7/2006 12:00:00 AM

moondaddy (moondaddy@noemail.noemail) writes:
[quoted text, click to view]

Insert the data into a temp-table. I have an article on my web site that
discusses the technique more in detail. See
http://www.sommarskog.se/share_data.html.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Re: How to pass resultset as param to SP

changliw NO[at]SPAM online.microsoft.com
11/7/2006 12:00:00 AM

Hi Urid,
Great! Your suggestion is wonderful and succincter than mine.

Cheers,
Charles Wang
Microsoft Online Community Support

RE: How to pass resultset as param to SP

changliw NO[at]SPAM online.microsoft.com
11/9/2006 12:00:00 AM

Hi,
How about this issue? Could you please let me know the issue status?
If you could see that Urid and I had replied you at the newsgroup and Urid
provided a better resolution for you, please post back here at your
convenience and let us know if you need further assistance on this issue.
Your confirmation will be absolutely a great encouragement for our support.

Sincerely yours,
Charles Wang
Microsoft Online Partner Support

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.

View Other Months
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008

home · blog · groups

Questions? Comments? Contact the dn