CODEWITHSUNDEEP
sqlsql-server-2005t-sql
Kobojunkie
Kobojunkie

Reputation: 6545

SQL INSERT INTO calling Stored Procedure instead

I would like to do something like this in SQL

Insert Into storedproc2 SELECT column1, column2 from Tablename

My goal is to have each row of data in tablename processed using the storedproc2 stored procedure, which itself handles any insertion necessary in the logic flow.

Upvotes: 1

Views: 1529

Answers (5)

Gabriel Guimarães
Gabriel Guimarães

Reputation: 2744

Well as others said, you can't do that on a single statement. (that's just the way things work)

If what you wan't is to call a proc with the results you can first select and then call the proc using a cursor. The cursor would perform a row by row code and you would be able to call the proc passing the correct values. But beware cursors are slow use Flags like FAST_FORWARD.

The other way would be to change your proc to accept a whole table, as a table valued parameter, if that is possiblel, that would perform really better.

Hope this helps.

DECLARE CallingProcCursor CURSOR
FAST_FORWARD
FOR 
SELECT database_id,name from sys.databases
DECLARE @database_id int, @name sysname
OPEN CallingProcCursor

FETCH NEXT FROM CallingProcCursor INTO @database_id, @name
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        EXEC PROCX @database_id, @name
    END
    FETCH NEXT FROM CallingProcCursor INTO @database_id, @name
END

CLOSE CallingProcCursor
DEALLOCATE CallingProcCursor
GO

Upvotes: 2

David
David

Reputation: 73554

You can't insert into a stored procedure. You can only insert into a table (and in some cases a view, depending on the DB platform and whether the view is updateable.)

You can use a stored procedure to insert data as shown here: http://www.codeproject.com/KB/cs/tariqaziz.aspx

This is NOT meant to be insulting, but rather helpful...

It sounds like you need to go read up on stored procedures, since your question shows that you don't get the basics.

http://databases.about.com/od/sqlserver/a/storedprocedure.htm

Upvotes: 1

Alberto Martinez
Alberto Martinez

Reputation: 2670

You can't pass data sets to stored procedures, only parameters. You could:

  • Pass the table name to the stored procedure and then construct the select.
  • Pass the whole query as a parameter.

And then execute it with sp_executesql. If you use this method you should read The Curse and Blessings of Dynamic SQL.

Upvotes: 0

zish
zish

Reputation: 1152

you can put insert statement inside stored proc and pass the values as parameters e.g.

following is just an example, on each row you can call your stored proc and it should be fine 

 CREATE PROC BDD_AddMessageLogItem(
            @BusinessDevelopmentItemId INT,
            @MessageLog NVARCHAR(MAX), 
            @PostedBy SMALLINT,
            @PostedOfficeId TINYINT,
            @PostedDepartmentId TINYINT,
            @PostedMessageLogType TINYINT)
          AS
     BEGIN

       DECLARE @BusinessDevelopmentMessageLogId SMALLINT





   INSERT INTO dbo.BusinessDevelopmentItemMessageLogs
    ( BusinessDevelopmentItemId ,
      MessageLog , 
      DatePosted,         
      PostedBy,
      PostedOfficeId,
      PostedDepartmentId,
      PostedMessageLogType,
      BusinessDevelopmentMessageLogId
    )
   VALUES  ( @BusinessDevelopmentItemId , -- BusinessDevelopmentItemId - int
      @MessageLog , -- Message - nvarchar(100)          
      GETDATE(),
      @PostedBy, 
      @PostedOfficeId,
      @PostedDepartmentId,
      @PostedMessageLogType,
      @BusinessDevelopmentMessageLogId          
     )
    END

Upvotes: 0

JNK
JNK

Reputation: 65147

As David said, you can't do this the way you want.

What you CAN do is feed the stored proc a table as a parameter, and have it perform it's logic on each row in that table. It will involve some Dynamic SQL but it is doable.

Upvotes: 0

Related Questions