pass query/select recordset to stored procedure 1

[jordanking]

Hello,

is there a way to pass a query set to a stored procedure like you can pass a SET based query to an insert statement

example:
a simple procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[ps_MyProcedure]
	-- Add the parameters for the stored procedure here
	@intMyTable		int,
	@chrMytable		nvarchar(10),
	@dtMyTable		datetime

AS
BEGIN TRY
	SET NOCOUNT ON;
	BEGIN TRANSACTION	
    -- Insert statements for procedure here

	INSERT INTO dbo.MyTable
		(
		intMyTable,
		chrMyTable,
		dtMyTable
		)
		VALUES
		(
		@intMyTable,
		@chrMyTable,
		@dtMyTable
		)

	COMMIT TRAN	
	RETURN 0
END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0
		ROLLBACK TRANSACTION
END CATCH

can this be done:

EXECUTE [RegentSigns].[dbo].[ps_MyProcedure]
SELECT	TOP 10 dbo.Residential.intResidential, 'test' AS chrTest, dbo.Residential.dtServiceDate
FROM	dbo.Residential

pass the query results to the procedure so it runs with each of the rows in the result set?

I get an error with the above code saying that the procedure expected a parameter that was not supplied

.....
I'd rather be surfing

 

[AlexCuse]

no, but why not just write a normal insert query? You don't want to loop over a resultset to do row by row inserts (and if you do, do it in client code where it is probably handled much better than in T-SQL).

Something like this?

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[ps_MyProcedure]
    -- Add the parameters for the stored procedure here
    @intMyTable        int,
    @chrMytable        nvarchar(10),
    @dtMyTable        datetime

AS
BEGIN TRY
    SET NOCOUNT ON;
    BEGIN TRANSACTION    
    -- Insert statements for procedure here

    INSERT INTO dbo.MyTable
        (
        intMyTable,
        chrMyTable,
        dtMyTable
        )
    SELECT    TOP 10 dbo.Residential.intResidential, 'test', dbo.Residential.dtServiceDate
    FROM    dbo.Residential

    COMMIT TRAN    
    RETURN 0
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION
END CATCH

HOpe it helps,

Alex

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[jordanking]

thanks alex

the reason I asked is because I have a lengthy stored procedure that takes a bunch of parameters, and then itself executes other procedures as well as inserting/updating records into 3-5 tables based on business logic.

The reason I asked is because I did not want to code another lengthy procedure, and I wanted to avoid using a cursor, but It looks like I have to do that now.

.....
I'd rather be surfing

 

[AlexCuse]

Hm. I am not sure exactly what you have on your hands, but I would imagine that you can avoid using a cursor. For something like you've posted above, rather than using the actual values as the parameters, feed it parameters that it can use to get the set of rows that you want to insert.

If these are fresh inserts from a front end application (or inserting data that does not insert elsewhere in the database for some other reason), then I'd repeat my suggestion that you handle the looping from the front end and do row by row inserts.

Without more information on what you're doing, its' hard to give a reccomendation as to the specific approach you should take.

Hope this helps,

Alex


[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[jordanking]

thanks, I realised that set based solutions could work, assuming I did't screw myself with some bad database design. I have an odd issue with a primary key and as a result a foriegn key constraint does not allow me to do as you suggested. I have implemented the type of batch inserts you outlined in other situations and it works great, but sadly I am not able to employ that here. Hard lesson learned about databse design.

.....
I'd rather be surfing