February 21, 2006 at 5:34 am
Hi,
I am using vb.Net and stored procedure to update a table. I have a table with a list of IDs; I have a list of IDs that I want to delete in a sortedlist in vb.Net. I want to delete the rows from the table so I could build the Querystring dynamically from my sortedlist to end up with:
DELETE FROM tblTest WHERE ID IN (2,5,23,46)
but instead of using dynamic SQL I want to use a stored proc. I know how to create stored procs and use through vb.net but I don't know how to specify a Parameter that contains a list. How do I do this? e.g.
Create PRocedure dbo.Test1
(
@ListOfIDs int
)
DELETE tblTest
WHERE ID IN @ListOFIDs
Return
February 21, 2006 at 5:51 am
Create Your Procedure Like this :--
Create PRocedure dbo.ListOfValues
(
/* You Have the value like 1,3,5,90
from your front end which ID'S You
want to delete*/
@ListOfIDs varchar(200)
)
as
Declare @STR varchar(200)
set @STR='DELETE tblTest WHERE ID IN (' + @ListOFIDs + ')'
Exec (@str)
--------------------------------------------
Your work will be done
Thanks.
February 21, 2006 at 6:12 am
Looks good. I will give it a go.
Thanks for the reply.
February 21, 2006 at 6:49 am
However you will have to add delete permissions to the underlying table and potentially open yourself to an injection attack.
2 suggestions.
1) Which I prefer these days
Write a standard Stored Procedure but instead of in use = and pass only 1 value at a time.
On your application side create a command object and set command type to CommandType.StoredProcedure
Then set the Command.Text = to your stored procedure name (I usually use dbo.SPName)
Now if you only have one variable then create like so
cmdSQL.Parameters.Add(new SqlParameter("@EmVarName",SqlDbType.Int,0,ParameterDirection.Input,false,0,0,"VarName",DataRowVersion.Default,null));
And create a loop thru go thru the entire list of entries setting Parameter = to value and performing
CommandObjectname.ExecuteNonQuery();
untill all items handled.
This provides maximum flexability in that you can know exactly which items fail and why plus you don't have a limit to how many you can pass in a single go.
or
2) Another option
Create a Procedure with a large varchar field to pass your complete string in.
Create a table variable in the procedure for storage of the items as rows.
Use a while loop and charindex to parse thru each item (, as you delimiter but don't forget the last item will not have 1) and place each item into the table variable as a new row.
The run delete where value in (select colname from @table variable) or any other valid syntax for matching.
There have been many posts in the past on this method including a few by me in the postings. Sorry no example code right where I can just paste here.
February 21, 2006 at 7:24 am
I didn't think of the Injection attack! I am just wondering though, will it be quite slow if I have to cmd.ExecuteNonQuery() for each value?
Would it be quicker if I checked that the passed parameter doesn't contain any nasty characters i.e. anything other than numbers and commas.
If I grant no permission on the table and only permission to the stored procedure, is this safe?
Just one more thing, when I create my params in .net code, they look like this:
'Add parameter for returning the AreasInfoID: @AreasInfoID
Dim prmAreasInfoID As New SqlParameter("@AreasInfoID", DbType.Int64)
prmAreasInfoID.Direction = ParameterDirection.Output
cmdUpdate.Parameters.Add(prmAreasInfoID)
'Add the required parameters: @WorkAreaID
Dim prmWorkAreaID As New SqlParameter("@WorkAreaID", ArInfo.WorkAreaID)
cmdUpdate.Parameters.Add(prmWorkAreaID)
They work fine for both input and output. Do I need to include any of your other arguments for safety related reasons?
February 23, 2006 at 11:04 am
1) Overall because you are doing the ExecuteNonQuery() it isn't trying to return a recordset and it would be similar as to writing multiple insert statements. There is no significant amount of more time used (quite possibly less since you can split the items on the application side more effeciently), and the bennifits you gain are far better (you can see which exact item failed and why, you can determine how to handle the failure on a case by case basis if not a whole).
2) No, I just like to be more precise in my definitions when I define my parameters although just because I am that way.
February 23, 2006 at 12:50 pm
Antares second option is what I frequently use. Here is a sample
DECLARE @paramlist NVARCHAR(100)
SET @paramlist = '7|2|8|65' -- test data; string can be any number of indices
SET @paramlist = '|' + @paramlist + '|' -- put delimiters at start and end
SELECT p.ParameterID
into #A
FROM appData p
WHERE CHARINDEX('|' + CAST(ParameterID AS nvarchar) + '|', @paramlist) > 0
DELETE d
FROM appData d
inner join #a
on d.parameterID = #a.parameterID
drop table #a
February 23, 2006 at 3:09 pm
Create TABLE UDF converting delimited string to set of values.
Supply you string to this UDF as a parameter.
Delete ...
WHERE IdColumn in (select ReturnValue from dbo.<UDFName>(@String))
_____________
Code for TallyGenerator
February 24, 2006 at 1:38 am
Hi Guys,
Thanks for the examples. I am going to use Option 1 for now and I will try Option 2 as soon as I can.
Again, all help much appreciated!!
February 24, 2006 at 9:00 am
I usually find that the use of an IN is slower than doing an inner join to the function or table.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply