I have 2 related tables I code a SP that inserts the master table and a trigger on it when inserting master table that calls second SP
but ı must insert detail table multiple rows.So ı want to send ARRAY into secondary SP How can I do that ? Or is it possible ? If yes can anybody send me a code snippet ? Thanks
-
-
I'm sure someone else will tell you the correct way to do this, or at least, a more sensible way but when I have needed to do it in the past I have sent the array in as a delimited string e.g. 1|2|3|4 and then split it into a temp table using a custom function call in the stored proc. I'm sure this performs terribly but it wasn't so important in the app that I used it in and it worked.
I can post the split function if you need it but as I say I am sure that there will be a better way with a custom data type or something that I am unaware of...
Good luck...
-
Another_Darren
... than you can shake a stick at
MSDN Array Parameter Sample
Found via this Google Search.
-
Matthew van Eerde
AKA Maurits
There's a couple of ugly ways that I've used before.
Let's put a concrete face on the problem. Suppose you have a Products table and a Carts table. You have a page where a user can check boxes of various products to add them to their cart. You want to call a single stored procedure with a list of the product IDs that were checked, and have them all added to the users cart.
Your SP prototype looks like this:
EXECUTE AddProducts(
@UserID int,
@ProductIDs nvarchar(512) -- comma delimited: N'5, 13, 12'
)
The stored procedure is supposed to insert rows into the Carts table. The Carts table has two important columns: ProductID and UserID. The UserID for each new row should be the passed @UserID; each of the passed @ProductIDs should be represented in one new row.
FIRST METHOD: use LIKE
INSERT INTO
Carts (UserID, ProductID)
SELECT
@UserID,
ProductID
FROM
Products
WHERE
N', ' + @ProductIDs + N', ' LIKE N'%, ' + CAST(ProductID AS NVARCHAR(20)) + N', %'
One disadvantage of this method is it won't be able to make use of the index on ProductID. So it will be slow, if the Products table is large.
If the table is large, and the index is there, I revert to the following method:
SECOND METHOD: use sp_executesql
-- check for SQL injection attacks
IF @ProductIDs LIKE N'%[^0-9, ]%'
BEGIN
RAISERROR(...) -- raise an appropriate error
RETURN (1) -- under no circumstances continue
END
DECLARE @SQL nvarchar(4000)
SELECT @SQL =
N'INSERT INTO Carts(UserID, ProductID)
SELECT
' + CAST(@UserID AS NVARCHAR(20)) + ',
ProductID
FROM
Products
WHERE
ProductID IN (' + @ProductIDs + ')'
'
EXECUTE sp_executesql @SQL -
I think I HAVE TO USE XML and IT will be better.But ITS SO COMPLICETED
-
You're definitely approaching the problem in the wrong manner if you think you need to use an array in SQL. Perhaps you can explain exactly what it is you need to do and we'll be able to suggest a better approach.
-
figuerres
???
Yeah I read the first post and I am not sure what you are doing...
some thing about a trigger during an insert ??
you do know about the virtual inserted table??
that an insert trigger can fire for one row or n rows to insert ??
perhaps a clear example of what you need to do might lead to a simple way to solve it. -
JuNK64 wrote:
I think I HAVE TO USE XML and IT will be better.But ITS SO COMPLICETED
It really isn't complicated at all.
You just need to get comfortable with it.
Using Edge Tables with XMl in SQL Server is efficient and also very fast.
CREATE Procedure spc_myXMLProc(@Xml nText)
AS
CREATE TABLE #myTable (
tempID INT IDENTITY(1,1) NOT FOR REPLICATION,
SomeBit bit,
SomeAmount float,
SomeDate varchar(50)
)
-- Prepare XML using SQL's XML Preprocessor
-- In this Case the namespace is from a DataSet
EXEC sp_xml_preparedocument @iDoc OUTPUT, @Xml, '<MyDataSet xmlns:fn="http://tempuri.org/MyDataSet.xsd"/>'
INSERT INTO #myTable (
SomeBit,
SomeAmount,
SomeDate
) SELECT
Case SomeBit
WHEN 'True' Then 1
ELSE 0
END,
Cast(SomeAmount as Float),
SomeDate
FROM
OPENXML(@iDoc, '//fn:MyDataSet', 3)
WITH
(
SomeBit varchar(5) 'fn:SomeBit', -- SomeBit is a Node in the XML
SomeAmount varchar(50) 'fn:Amount',
SomeDate varchar(50) 'fn:MyDate'
)
-- Free Preprocessor memory
EXEC sp_xml_removedocument @iDoc
SELECT * FROM #myTable -
Well, you can send an "array" as a delimeted list into your stored procedure. Then, convert the array into a temporary table which you can then navigate through. I find this useful for sending "arrays" of checkbox values from a web form into a stored procedure.
Function to convert list into temp table:
CREATE FUNCTION dbo.iter_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
USAGE Example:
DECLARE curDept CURSOR FOR
SELECT nstr FROM iter_charlist_to_table(@in_accounting_department_id, ',')
*The function is not mine....not sure where I got it from...but it is really handy.
Thread Closed
This thread is kinda stale and has been closed but if you'd like to continue the conversation, please create a new thread in our Forums,
or Contact Us and let us know.