How to pass resultset as param to SP  
Author Message
(fl)joe





PostPosted: Tue Nov 07 00:26:58 CST 2006 Top

SQL Server Developer >> How to pass resultset as param to SP

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.

--


SQL Server200  
 
 
Uri





PostPosted: Tue Nov 07 00:26:58 CST 2006 Top

SQL Server Developer >> How to pass resultset as param to SP 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))

AS
BEGIN


BEGIN





END
RETURN
END
GO

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





select od.* from [order details] od
INNER JOIN
(SELECT Item

ON od.orderid = t.Item








> 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.
>
> --

>


 
 
changliw





PostPosted: Tue Nov 07 04:41:13 CST 2006 Top

SQL Server Developer >> How to pass resultset as param to SP 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 Shippers_Cursor CURSOR FOR
SELECT ShipperID
FROM Shippers

OPEN Shippers_Cursor

FETCH NEXT FROM Shippers_Cursor


BEGIN

begin

end

END

CLOSE Shippers_Cursor
DEALLOCATE Shippers_Cursor


================================================

CREATE PROCEDURE proc_queryShipper
(
@strCondition varchar(100)
)
AS



===============================================

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.
======================================================




 
 
Uri





PostPosted: Tue Nov 07 04:50:23 CST 2006 Top

SQL Server Developer >> How to pass resultset as param to SP 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)















> 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 Shippers_Cursor CURSOR FOR
> SELECT ShipperID
> FROM Shippers
>
> OPEN Shippers_Cursor
>
> FETCH NEXT FROM Shippers_Cursor


> BEGIN

> begin

> end

> END

> CLOSE Shippers_Cursor
> DEALLOCATE Shippers_Cursor


> ================================================
>
> CREATE PROCEDURE proc_queryShipper
> (

> )
> AS



> ===============================================
>
> 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.
> ======================================================
>
>
>
>


 
 
Erland





PostPosted: Tue Nov 07 05:31:59 CST 2006 Top

SQL Server Developer >> How to pass resultset as param to SP
> 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?

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.


--


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
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 
 
changliw





PostPosted: Tue Nov 07 07:02:20 CST 2006 Top

SQL Server Developer >> How to pass resultset as param to SP Hi Urid,
Great! Your suggestion is wonderful and succincter than mine.

Cheers,
Charles Wang
Microsoft Online Community Support

 
 
changliw





PostPosted: Thu Nov 09 03:50:23 CST 2006 Top

SQL Server Developer >> How to pass resultset as param to SP 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