Sending multivalue from SSRS to a stored procedure.

One, of the many, features that I wish they would add to SSRS; is an easier way to send in multivalue selected parameters to a stored procedure.

The best solution to this problem, is to use a function that converts a string of delimited values into a table.

 Here are the quick low down steps:

1) Go to your SSRS dataset properties -> Parameters -> (select the multivalue parameter function [fx]) and change the parameter to send in a sting of delimited values. For example,

=Join(Parameters!MeasurementID.Value,",")

2) Create the function that splits and creates a table:

CREATE FUNCTION dbo.fn_charlist_to_table
(
@list ntext,
@delimiter nchar(1) = N','
)
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,

str varchar(4000),
nstr nvarchar(2000)) AS

BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)

SET @textpos = 1
SET @leftover = ''

WHILE @textpos <= datalength(@list) / 2
  BEGIN
    SET @chunklen = 4000 - datalength(@leftover) / 2
    SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
    SET @textpos = @textpos + @chunklen
    SET @pos = charindex(@delimiter, @tmpstr)

    WHILE @pos > 0
      BEGIN
        SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
        INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
        SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
        SET @pos = charindex(@delimiter, @tmpstr)
       END
      
    SET @leftover = @tmpstr
  END

INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)),
ltrim(rtrim(@leftover)))

RETURN
END
GO

3) In your stored procedure, call the function. For example:

Select * from measurment where
measurement.MeasurementID in (select [str] from fn_charlist_to_table(@MeasurementID,Default))

Quick Reminder: Parsing in T-Sql

When a set of values are sent in as a varchar and need to be parsed. Ex: '1,2,3,4'
In this example the values are sent into the stored procedure's variable @atInvNum.

DECLARE @aInvNum varchar(4000)

SET @aInvNum = LTRIM(RTRIM(@atInvNum))
DECLARE @pos INT
DECLARE @piece varchar(500)

DECLARE @tInvNum TABLE (ID int)

IF right(rtrim(@aInvNum),1) <> ','
SET @aInvNum = @aInvNum + ','

SET @pos = patindex('%,%' , @aInvNum)
WHILE @pos <> 0
BEGIN
SET @piece = left(@aInvNum, @pos - 1)

INSERT INTO @tInvNum SELECT cast(@piece as int)

SET @aInvNum = stuff(@aInvNum, 1, @pos, '')
SET @pos = patindex('%,%' , @aInvNum)
END


---------------------A better way---------------------

IF (LEN(RTRIM(LTRIM(@atTaskIDs))) > 0)
SET @atTaskIDs = ',' + @atTaskIDs + ','

CHARINDEX(',' + Convert(varchar, SRT.TASK_ID) + ',', @atTaskIDs) > 0 )

Reminder: Bulk insert through a store procedure

Ok, my problem was I had a large collection of item; where each item is suppose to be inserted in a database and I didn't want to do a bulkcopy.

This is accomplished by using XML in the store procedure and sending it in through the ADO.Net

In the C# code:

//Make xml
using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["connectionString"]))
{
   SqlCommand cmd = new SqlCommand("insBulkFTSEDCFileDetail", conn);
   cmd.CommandType = CommandType.StoredProcedure;
   StringBuilder sb = new StringBuilder();
   sb.Append("\r\n");
   foreach (oEDCItem item in edc)
   {
      sb.AppendFormat("\r\n",
      item.ModuleName, item.PartNumber, item.SerialNumber, item.TestPosition, item.SupplierName, item.EDC, item.Opt);
}
   sb.Append("");

   cmd.Parameters.AddRange(new SqlParameter[] {
   new SqlParameter("@EDCFileID", EDCFileID),
   new SqlParameter("@LastUpdateUserID", UserID),
   new SqlParameter("@XMLDOC", sb.ToString())});

   conn.Open();
   cmd.ExecuteNonQuery();
   conn.Close();
}



The stored procedure:

Create Procedure insBulkFTSEDCFileDetail
{
   @EDCFileID int,
   @LastUpdateUserID int,
   @XMLDOC varchar(MAX)
}
AS

DECLARE @xml_handle int

EXEC sp_XML_preparedocument @xml_handle OUTPUT, @XMLDOC

INSERT INTO [TABLE]
(EDCFileID,
ItemType,
Model,
SerialNumber,
EDCPosition,
SupplierName,
EDC,
Options,
LastUpdateDate,
LastUpdateUserID)

SELECT @EDCFileID,
EDCXML.ItemType,
EDCXML.Model,
EDCXML.SerialNumber,
EDCXML.EDCPosition,
EDCXML.SupplierName,
EDCXML.EDC,
EDCXML.Options,
getUTCDate(),
@LastUpdateUserID
FROM OPENXML( @xml_handle, '/EDCItems/EDCItem')
WITH ( ItemType varchar(8),
Model varchar(50),
SerialNumber varchar(50),
EDCPosition varchar(50),
SupplierName varchar(50),
EDC varchar(50),
Options varchar(80)) AS EDCXML

EXEC sp_XML_removedocument @xml_handle

SQL Interview Questions:

Questions:
1) What is the name of the SQL Server query language?
2) What is the difference between DML and DDL?
3) Name the four main types of DML Query operations.
4) Name three reasons to use a stored procedure.
5) What is the difference between a stored procedure and an extended stored procedure? Where is an extended stored procedure stored?
6) What is the difference between "Truncate" and "Delete From"?
7) What is a SQL Server Page, and how is it used? How big is a page? How much data space is available on each SQL Server Page?


Answers:
1) Transact-SQL (used by Microsoft and Sybase)
2)
DDL is Data Definition Language statements. Some examples:

CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
GRANT - gives user's access privileges to database
REVOKE - withdraw access privileges given with the GRANT command

DML is Data Manipulation Language statements. Some examples:

SELECT - retrieve data from the/a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency

3) From above most popular are Select, Insert, Update and Delete.

4)
Encapsulation - changing the inner implementation as often you like.
Security - secured at a database level, and help prevent SQL Injection attacks.
Performance - Improving network traffic, etc..

5)Extended store procedure is accessed as though it was a compiled SQL program. They are not written in Transact-SQL, they reside in DLLs.

6)
Truncate is DDL and drops all rows no ROLLBACK possible, no WHERE clauses possible. Truncate drops blocks created for the table without erasing the definition of the table, delete just the content of those blocks. Aditional to this, clustered tables cannot be truncated. DELETE is DML and uses various memory structures allowing ROLLBACK and WHERE clauses.

DELETE from table_name where... ;
TRUNCATE table table_name;

7) Pages exist to store records. A database page is an 8192-byte (8KB) chunk of a database data file. They are aligned on 8KB boundaries within the data files, starting at byte-offset 0 in the file. Pages are the basic unit of IO that SQL server uses. 8060-bytes are used for storing data.