Array as parameter to Stored procedure
-
Hi,
I have a VB application where in I would like to pass an array as parameter to a SQL stored procedure ( SQL 2000). I am not sure if it can be done. I am using ADO to interact with SQL Server.
Can anyone let me know the procedure to pass an array, if it is possible.
Thanks,
Gopal
-
Hi,
Two threads with very similar topics......
You can also use a string with delimiters. I found this on here about a year ago and have used it for allsorts ever since......
Just create the sproc and pass it the required variables. Quite self explanatory really
/*###########################################################################################
PURPOSE
Takes in a list of values in a string, a delimiting character, a table name and a column name and seperates the values
based around the delimiting character
PARAMETERS
@vcList - List of values, delimited by @vcDelimiter
@vcDelimiter - Delimiting character
@TableName - Name of Table to pass seperated values into
@ColumnName - Names of Column to pass seperated values into
NOTES
############################################################################################*/
CREATE PROCEDURE [sprocListToTable]
@vcList VARCHAR(8000),
@vcDelimiter VARCHAR(8000),
@TableName SYSNAME,
@ColumnName SYSNAME
AS
SET NOCOUNT ON
DECLARE @iPosStart INT,
@iPosEnd INT,
@iLenDelim INT,
@iExit INT,
@vcStr varchar(8000),
@vcSql varchar(8000)
SET @iPosStart = 1
SET @iPosEnd = 1
SET @iLenDelim = LEN(@vcDelimiter)
SET @vcSql = 'INSERT ' + @TableName + ' (' + @ColumnName + ') VALUES ('''
SET @iExit = 0
WHILE @iExit = 0
BEGIN
SET @iPosEnd = CHARINDEX(@vcDelimiter, @vcList, @iPosStart)
IF @iPosEnd <= 0
BEGIN
SET @iPosEnd = LEN(@vcList) + 1
SET @iExit = 1
END
SET @vcStr = SUBSTRING(@vcList, @iPosStart, @iPosEnd - @iPosStart)
EXEC(@vcSql + @vcStr + ''')')
SET @iPosStart = @iPosEnd + @iLenDelim
END
RETURN 0
GO
Have fun
Steve
We need men who can dream of things that never were.
-
There are better ways to do this. I suggest a look at the article I linked to above.
-
On top of my head
: IF Object_id('fnSplit_Set') > 0
DROP FUNCTION dbo.fnSplit_Set
GO
IF Object_id('Numbers') > 0
DROP TABLE dbo.Numbers
GO
CREATE TABLE dbo.Numbers (PkNumber int identity(1,1), dude bit null, CONSTRAINT Pk_Number PRIMARY KEY CLUSTERED (PkNumber))
GO
INSERT INTO dbo.Numbers (dude)
SELECT NULL FROM (SELECT TOP 100 NULL AS A FROM master.dbo.spt_values) dt100 cross join (SELECT TOP 80 null AS A FROM master.dbo.spt_values) dt80
GO
ALTER TABLE dbo.Numbers
DROP COLUMN dude
GO
--Select min(PkNumber) as MinA, max(PkNumber) as MaxA from dbo.Numbers
--1, 8000
GO
CREATE FUNCTION [dbo].[fnSplit_Set] (@IDS as varchar(8000), @vcDelimiter varchar(3))
RETURNS TABLE
WITH SCHEMABINDING
AS
Return
Select dtSplitted.EachID, dtSplitted.Rank from (
SELECT SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter),
CHARINDEX(@vcDelimiter, @vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter)) - N.PkNumber - len(@vcDelimiter)) as EachID
, (LEN(SUBSTRING(@IDs, 1, N.PkNumber)) - LEN (REPLACE (SUBSTRING(@IDs, 1, N.PkNumber), ',', ''))) + 1 AS Rank
FROM dbo.Numbers N
WHERE SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber, len(@vcDelimiter)) = @vcDelimiter
AND PkNumber 0
GO
DECLARE @Ids as varchar(8000)
SET @IDS = ''
Select @IDS = @IDS + CAST(id as varchar(10)) + ',' from (Select TOP 10 id from dbo.SysObjects order by NEWID()) dtIds
SET @IDS = left(@IDS, ABS(len(@IDS) - 1))
PRINT @IDS
Select O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by O.Name
Select O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by dtIDS.Rank
--Dont't ever forget to cast the eachid column to the same datatype as in the join to avoid table scans.
-
That will kill this thread - it will take us mere mortals two weeks to decipher it If thats off the top of your head Remi, I need to do something else - like driving HGV's.....
Have fun
Steve
We need men who can dream of things that never were.
-
Give yourself a chance... 2 days at most
. 2 hours if you're smart 
. -
Perhaps this one is simpler. It's more or less the same thing, only it does not return rank and uses a virtual numbers table instead of a real one.
CREATE FUNCTION dbo.fnListToSet (@list VARCHAR(8000), @delimiter VARCHAR(3))
RETURNS TABLE
AS
RETURN
SELECT s.tuple
FROM (
SELECT SUBSTRING(
@delimiter + @list + @delimiter
, numbers.n + LEN(@delimiter)
, CHARINDEX(@delimiter, @delimiter + @list + @delimiter, numbers.n + LEN(@delimiter)) - numbers.n - LEN(@delimiter)
) AS tuple
FROM (
SELECT D0.d*1 + D1.d*10 + D2.d*100 + D3.d*1000
FROM (SELECT 0
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS D0 (d)
, (SELECT 0
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS D1 (d)
, (SELECT 0
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS D2 (d)
, (SELECT 0
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS D3 (d)
) AS numbers (n)
WHERE SUBSTRING(
@delimiter + @list + @delimiter
, numbers.n
, LEN(@delimiter)
) = @delimiter
AND n 0
GO
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply