Wednesday, 6 January 2016

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
Visit Our New Site for HR Interview Questions Answers http://www.interviewboat.com/Company/GetCompany
Sponsored Ads

0 comments:

Post a Comment