How to pass an array(or a table variable) into a SQL Server stored procedure? SQL Server Interview Question

SQL Server Interview Question: How to pass an array(or a table variable) into a SQL Server stored procedure?

1. Create a Type in SQL Server
as:

CREATE TYPE dbo.MyDataTable -- you can be more speciifc here
AS TABLE
(
  Category NVARCHAR(200)
);

GO


2. Create a Stored Proc(on SQL Server) consume the above TYPE created and insert into Categories(assuming your table name is "Categories"

CREATE PROCEDURE dbo.InsertCategories
  @dt AS dbo.MyDataTable READONLY
AS
BEGIN
  SET NOCOUNT ON;

  INSERT dbo.Categories(Category)
  SELECT Category
  FROM @dt
  WHERE Category NOT IN (SELECT Category FROM dbo.Categories);

END

Click here for more than 300 Sql server Interview questions

Comments

Archive

Contact Form

Send