Stored Procedure with "Variable Clause"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Stored Procedure with "Variable Clause"

Author

Topic

IMGrumpy
Starting Member

11 Posts

Posted - 2011-01-14 : 10:55:23

Greetings:

I'll try to keep this simple:

I've got a database table containing many records. The records come from different "Devices" which are identified with primary key "Device_ID". The records also contain a DateTime type timestamp.

What I am trying to do is create a stored procedure that will return all of the records, bounded by a date and time range, sorted by date and time AND (here's the tough part) associated with ONE OR MORE device ID's. That is, the return from this function or procedure can contain records from one, two or many device ID's.

I have already solved the problem of passing the input device ID's TO the procedure. I am using an INT for the number of devices and then a BINARY input variable to contain the array of one or more device ID's. This part works fine.

What I can't figure out how to do is construct the select statement in which the WHERE clause contains more than one Device ID. I'm not even sure that this is how to do it. Perhaps there is a way to loop through the ID's and accumulate records into a final result.

I've been trying to figure this out, on and off, for a few days now.

Could somebody just point me in the right direction?
Thank you,
Mark

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-01-14 : 11:44:33

It would be normal to pass the IDs as a sting of numbers and split them using a SplitSting Function.
(Google one and create it with any supporting number/tally table.)

You code would then be something like:


SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.YourDeviceProc
	@DateFrom datetime
	,@DateTo datetime
	,@DeviceIDs varchar(MAX)
AS

SELECT *
FROM Devices D
WHERE [timestamp] BETWEEN @DateFrom AND @DateTo
	AND EXISTS
	(
		SELECT *
		FROM dbo.SplitString(@DeviceIDs) I
		WHERE I.IntValue = D.Device_ID
	)
ORDER BY [timestamp];
GO

-- Example of use
EXEC dbo.YourDeviceProc '20101201', '20110110', '1,2,56';

If you only ever need to call the procedure from SQL, or a recent version of .Net, then a table of IDs can be passed directly.
You code would then look something like:


CREATE TYPE IntTypeTable AS TABLE 
(
	IntValue int NOT NULL
);
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.YourDeviceProc
	@DateFrom datetime
	,@DateTo datetime
	,@DeviceIDs IntTypeTable READONLY
AS

SELECT *
FROM Devices D
WHERE [timestamp] BETWEEN @DateFrom AND @DateTo
	AND EXISTS
	(
		SELECT *
		FROM @DeviceIDs I
		WHERE I.IntValue = D.Device_ID
	)
ORDER BY [timestamp];
GO

-- Example of use
DECLARE @t IntTypeTable

INSERT INTO @t
VALUES (1),(2),(56)

EXEC dbo.YourDeviceProc '20101201', '20110110', @t;

-- The SP can be called from apps using later versions of .Net by using code found at:
-- http://www.sqlservercentral.com/articles/SQL+Server+2008/66554/

You can also pass the IDs as XML.

IMGrumpy
Starting Member

11 Posts

Posted - 2011-01-14 : 13:20:04

Hey! Thank you very much.

It looks like you're first suggestion will work best for me. I'm going to try it now.

Thanks again!
Mark

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-16 : 05:56:40

you can use below function for parsing delimited string

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

IMGrumpy
Starting Member

11 Posts

Posted - 2011-01-17 : 13:30:20

Thanks Everybody!

After studying all of the examples that you provided, I came up with the following:

CREATE FUNCTION SeparateIntArray(@ArrSize int, @IntArray BINAR(100))
RETURNS @ReturnArray TABLE (ArrElement int)
AS
BEGIN
-- Fill the table variable with the rows for your result set
DECLARE @Element INT, @Offset INT, @Index INT

SET @Index = 0
SET @Offset = 1

WHILE (@Index < @ArrSize)
BEGIN
SET @Element = CONVERT(INT, SUBSTRING(@IntArray,@Offset,4))
SET @Index = @Index + 1
SET @Offset = @Offset + 4
INSERT INTO @ReturnArray VALUES (@Element)
END
RETURN
END
GO

CREATE PROCEDURE FieldData.DeviceRecordsQuery( @NumDev INT, @DeviceIDBuf BINARY(100), @StartTime DATETIME, @EndTime DATETIME )
AS
BEGIN
DECLARE @Offset INT, @Index INT

SELECT * FROM FieldData.DeviceRecordsTable PT
WHERE TimeStamp BETWEEN @StartTime AND @EndTime
AND EXISTS
(
SELECT * FROM SeparateIntArray (@NumDev, @DeviceIDBuf) I
WHERE I.ArrElement = PT.Device_ID
)
ORDER BY TimeStamp
END
GO

I wanted to be able to pass an array of integers to the function, for a variety of reasons, and use that to specify one or more device ID's.

Nevertheless, it works the way I want it to now and I thank you both for your input.

Cheers,
Mark

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 11:29:32

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-24 : 12:01:06

I'm a little late to the party, but if you can you should really be using a Table Valued Parameter to pass in mutiple values. Not a binary string or deleimited string.

Also, be aware there can be pretty signifigant performance issues using a tabled valued function as suggested above.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources