CODEWITHSUNDEEP
sqldatabasesql-server-2008stored-proceduresimport
iims
iims

Reputation: 313

Data in the table as the set of input parameters to a stored proc in sql server

I have procedure which takes the input parameters and populates in the 5 different tables in the new database. In the old database the data is put in one single table. The execution of the stored procedure is 

exec registration ('abc company', 'abc', '1/12/2004', '1/14/2010', 
                   'active', 'www.abccompany.com')

The problem i have is ..

'abc company','abc','1/12/2004','1/14/2010','active','www.abccompany.com' the input parameters provided in the procedure is one record in a table in the database. I have 1000 records in that table. is there a way that, table data can become my input parameters to the stored procedure? If so how?

Upvotes: 1

Views: 358

Answers (2)

Andriy M
Andriy M

Reputation: 77677

If it's only one row, you could 'teach' your stored procedure to take the needed data from that table and pass only the ID as a parameter.

Something like this:

CREATE PROCEDURE PopulateTables (@SourceID int)
AS BEGIN
  INSERT INTO Table1
    (CompanyName, CompanyNameShort, DateA, DateB, Status, CompanyWebsite)
  SELECT
    CompanyName, CompanyNameShort, DateA, DateB, Status, CompanyWebsite
  FROM SourceTable
  WHERE ID = @SourceID
  ... /* same for the other 4 tables */
END;

Upvotes: 0

cmsjr
cmsjr

Reputation: 59195

As of SQL Server 2008 you can pass a table valued parameter to a stored procedure. Doing so requires the definition of a user defined type to represent the input.

http://msdn.microsoft.com/en-us/library/bb675163.aspx

Upvotes: 1

Related Questions