Stored Procedure to query data using list parameter

Working on this task required me to create a stored procedure which will get a list of strings, do some processing on them and then return some data. Since SQL Server 2000 don't support arrays object so I had to find a different approach. After a bit of googling i figured out I can send list by building a string out of it and then in SQL Server use function "IN" to do processing as required.

Here'e the Stored Procedure,

USE [DataBaseName]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


Create PROCEDURE [dbo].[StoredProcedureName]
(
@listParameter varchar(8000)   -- this is because SQL Server don't support "Max" --
)
AS

exec('SELECT  *
FROM TableName
WHERE ColumnaName IN (' +@PasswordPolicyGroups+ ')   -- checks for list of values --
and CoumnaNameB= 1')

Now I am providing list as

List<String> myListData = GetListData(); // or your logic
string tempListData = string.Empty;

foreach(string data in myListData)
     data += "'" + myListData + "',";

// now just pass data as your parameter and call stored procedure

How to return Primary Key of just Inserted record into SQL Datatable using Strongly Typed DataSet

Inserting data directly into SQL data table isn't recommended because of security reasons, which is why most of us use Strongly Typed data-sets, it provides our solution with a secured layer to interact with database, enhancing security.

Saying that strongly data-sets are better way of interacting with database but they can sometime become hectic and consume time while debugging them specially when you use TableAdapter Query Configuration Wizard as it doesn't always configures your data-sets the way they should be.

Back to problem SCOPE_IDENTITY() not returning right value, as we use it to return the primary key generated by table when we try to insert some records into the specified table. Most of us simply use return SCOPE_IDENTITY() as it works sometimes and on some servers but it doesn't work other times (and that's when debugging takes ages).

Best way I recommend of getting back primary key of inserted record is by using OUTPUT Parameter in your stored procedure as it will make sure your strongly typed data-set get's the right value.

Here's an example of how your Stored Procedure for inserting a record should look like,

USE [DataBaseName]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[StoredProcedureName]
(
          @ColumnA int,
          @ColumnB int,
          @NewID int
 )
AS

INSERT Into tableName
Values
(
          @ColumnA,
          @ColumnB
 )
SELECT @NewID = SCOPE_IDENTITY()

In this article I will assume you already know how to create a data-set or have it ready, but if you don't know then you can find instructions at How to Create a DataSet ?.

Once your data-set is ready just drag and drop table or stored procedure into designer class from Server Explorer. Now if you drag and drop table which I would recommended you  can add a query and select your stored procedure using TableAdapter Query Configuration Wizard. Thing that you need to take care of is to select

"A Single Value - A typed function will be...."

Then name your stored procedure as you desire and finish the wizard. Your method will look something like this,

public int InsertDataMethod(solution.myRow details)
{
      int? output = 0;
      TableName_TableAdapter tableAdapter = new TableName_TableAdapter();
      using (tableAdapter.Connection = new SqlConnection(ConnectionString))
      {
         tableAdapter.StoredProcedureDataSet(details.ColumnA, details.ColumnB, ref output);
       }
       return Convert.ToInt32(output);
}

Make sure dataset's ExecuteMode property is Scalar otherwise it might doesn't return right ID.