The Multi-valued Parameters problem in Reporting Services

Alex Grinberg-230981 — Tue, 21 Apr 2009 00:15:42 GMT

Comments posted to this topic are about the item [B]The Multi-valued Parameters problem in Reporting Services[/B]

Creating Functions for Stored Procedures

Alex Grinberg-230981 — Fri, 10 Oct 2008 00:21:07 GMT

Comments posted to this topic are about the item [B]Creating Functions for Stored Procedures[/B]

SqlServer 2000

acekn — Thu, 08 May 2008 06:01:26 GMT

how to declare array in sqlServer 2000 and we get the value of that arrayany one can help me?

The ARRAY In SQL Server 2000

Alex Grinberg-230981 — Fri, 06 May 2005 11:45:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/aGrinberg/thearrayinsqlserver2000.asp

Raid Array Design for Multiple Instances of Databases

vrijesh prajapati — Sat, 16 Feb 2008 01:05:17 GMT

We are having multiple instances of single database & single cpu with 3.00 ghz & ram with 4 gb but still the users are facing poor performance in accessing the applicaton, ultimately slow performance.CPU SPIKES ARE GOING VERY HIGH & RAM AVAILABLE IS 1 GB.HDD IS 146 * 5 .Please guide us on this topic how to design RAID for 29 intances of database. Is there anybody who can focus on it ?[/font]Vrijesh Prajapati

Arrays in SQL SERVER

Ananth-431739 — Tue, 13 Nov 2007 04:34:56 GMT

can we able to use arrays in sql server 2000 or any other option similar to array? if any provide the solution..

Working With System Tables

Alex Grinberg-230981 — Mon, 19 Dec 2005 17:01:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/aGrinberg/workingwithsystemtables.asp

Self Eliminated Parameters

Alex Grinberg-230981 — Mon, 02 Oct 2006 14:29:00 GMT

Comments posted here are about the content posted at temp

RE:

Anubis-237029 — Fri, 20 Jan 2006 05:18:00 GMT

The array function was the first thing that I always had needed and missed not having in SQL 2000. Unfortunatly as time does not permit me, I am probably the only one here who hasn't yet had the chance to explore 2005 so if what I'm posting here is now superseeded, I do apologize. However, I'm sure it will come in handy to some.

In my programming, there are many cases where I need to generate an array on the fly from a text string input. In asp, I'm able to do this by calling String("Some,String").split(",") but SQL also lacks this capability.

Through the use of UDFs I am able to simulate this functionality and make the full use of a psuedo array. I have included both a single array and a dual array. The dual array is very useful when you wish to submit a string of variables and their values to SQL either for storage and/or processing, such as "Item1=Value1&Item2=Value2"

I hope that this may prove useful to someone else out there and I thank all the authors who have made my life easier thanks to the posts/articles here!

-Anubis.

Usage:

SELECT ArrayItemFROM [dbo].[Scripts_CreateArray]('Input,String,Here', ',')

Function:

CREATE FUNCTION [dbo].[Scripts_CreateArray]

( @ItemInput [varchar] (8000), @ItemBreaker [varchar] (5)

RETURNS @ArrayTable TABLE ( ArrayItem [varchar] (200)-- (if collation required) COLLATE SQL_Latin1_General_CP1_CI_AS

BEGIN

-- DECLARE WORKING VARIABLESDECLARE @BreakerPosition [int], @BreakerLength [tinyint], @LoopCounter [int], @LoopCounterMax [int]

SET @LoopCounter = 0SET @LoopCounterMax = LEN(@ItemInput)SET @BreakerLength = LEN(@ItemBreaker)

-- BEGIN LOOP FOR ARRAY ITEMSWHILE (@LoopCounter < @LoopCounterMax AND LEN(@ItemInput) > 0) BEGIN

-- GET BREAKER POSITION SET @BreakerPosition = CHARINDEX(@ItemBreaker, @ItemInput) IF (@BreakerPosition = 0) BEGIN SET @BreakerPosition = LEN(@ItemInput) + @BreakerLength END

-- INSERT INTO TEMP TABLE INSERT INTO @ArrayTable ( ArrayItem

VALUES ( SUBSTRING(@ItemInput, 1, (@BreakerPosition - 1))

-- UPDATE LENGTHS AND POSITIONS SET @ItemInput = SUBSTRING(@ItemInput, (@BreakerPosition + @BreakerLength), LEN(@ItemInput)) SET @LoopCounter = @LoopCounter + 1

END

-- RETURN ARRAYRETURN

END

Usage:

SELECT ArrayItem, ArrayItem2FROM [dbo].[Scripts_CreateArray_Dual]('Input=Item,String=Here', '=', ',')

Function:

CREATE FUNCTION [dbo].[Scripts_CreateArray_Dual]

( @ItemInput [varchar] (8000), @ItemBreaker [varchar] (5), @ItemBreaker2 [varchar] (5)

RETURNS @ArrayTable TABLE ( ArrayItem [varchar](4000),-- COLLATE SQL_Latin1_General_CP1_CI_AS, ArrayItem2 [varchar](4000)-- COLLATE SQL_Latin1_General_CP1_CI_AS

BEGIN

-- DECLARE WORKING VARIABLESDECLARE @BreakerPosition [int], @BreakerLength [tinyint], @BreakerLength2 [tinyint], @LoopCounter [int], @LoopCounterMax [int], @Item1 [varchar](4000), @Item2 [varchar](4000)

SET @LoopCounter = 0SET @LoopCounterMax = LEN(@ItemInput)SET @BreakerLength = LEN(@ItemBreaker)SET @BreakerLength2 = LEN(@ItemBreaker2)

-- BEGIN LOOP FOR ARRAY ITEMSWHILE (@LoopCounter < @LoopCounterMax AND LEN(@ItemInput) > 0) BEGIN

-- GET BREAKER POSITION SET @BreakerPosition = CHARINDEX(@ItemBreaker, @ItemInput) IF (@BreakerPosition = 0) BEGIN SET @BreakerPosition = LEN(@ItemInput) + @BreakerLength END

-- GET ITEM 1 SET @Item1 = SUBSTRING(@ItemInput, 1, (@BreakerPosition - 1)) -- UPDATE LENGTHS AND POSITIONS SET @ItemInput = SUBSTRING(@ItemInput, (@BreakerPosition + @BreakerLength), LEN(@ItemInput)) SET @LoopCounter = @LoopCounter + 1

-- GET BREAKER POSITION SET @BreakerPosition = CHARINDEX(@ItemBreaker2, @ItemInput) IF (@BreakerPosition = 0) BEGIN SET @BreakerPosition = LEN(@ItemInput) + @BreakerLength END

-- GET ITEM 2 SET @Item2 = SUBSTRING(@ItemInput, 1, (@BreakerPosition - 1)) -- UPDATE LENGTHS AND POSITIONS SET @ItemInput = SUBSTRING(@ItemInput, (@BreakerPosition + @BreakerLength2), LEN(@ItemInput)) SET @LoopCounter = @LoopCounter + 1

-- INSERT INTO TEMP TABLE INSERT INTO @ArrayTable ( ArrayItem, ArrayItem2

VALUES ( @Item1, @Item2

END