November 11, 2013 at 12:47 pm
Can anyone help, I have the following stored procedure
create procedure TEST_procedure
@exampleid
as
update tablea
set text_field = 'Y'
where
example_id in (@exampleid)
I would like to be able to pass multiple values to the @example id parameter so the procedure will update the relevant fields.
I'm not sure how to do this without creating more parameters.
November 11, 2013 at 5:30 pm
Using DelimitedSplit8k (link in my signature), you could accept a delimited list like so:
use tempdb;
-- create tablea for demo
IF OBJECT_ID('tempdb..tablea') IS NOT NULL drop table tablea;
CREATE TABLE tablea(id int identity primary key, text_field CHAR(1));
INSERT INTO tablea VALUES ('N'),('N'),('N'),('N'),('N');
-- before
SELECT * FROM tablea
GO
CREATE PROCEDURE dbo.Test_procedure (@exampleid varchar(1000))
AS
WITH t_update AS
(
SELECT id, text_field
FROM tablea t
CROSS APPLY reports.dbo.DelimitedSplit8K(@exampleid,',') s
WHERE s.Item=t.id
)
UPDATE t_update
SET text_field = 'Y'
GO
EXEC dbo.Test_procedure '1,2,3';
--after
SELECT * FROM tablea
-- Itzik Ben-Gan 2001
November 13, 2013 at 5:37 am
hi,
first can u create one function to use below code.
CREATE FUNCTION SPLIT(@VAL VARCHAR(MAX))
RETURNS @T1 TABLE(COL1 VARCHAR(MAX))
AS
BEGIN
WHILE CHARINDEX(',',@VAL)>0
BEGIN
INSERT INTO @T1 VALUES(SUBSTRING(@VAL,1,(CHARINDEX(',',@VAL))-1))
SET @val=SUBSTRING(@VAL,(CHARINDEX(',',@VAL))+1,LEN(@VAL))
END
INSERT INTO @T1 VALUES(@VAL)
RETURN
END
after that use that function in your code :
CREATE TABLE TEMP_TEST (ID INT,AMOUNT MONEY)
INSERT INTO TEMP_TEST VALUES (1,10)
INSERT INTO TEMP_TEST VALUES(2,10)
INSERT INTO TEMP_TEST VALUES(3,10)
CREATE PROC UPDATE_TEST(@ID VARCHAR(MAX))
AS
BEGIN
UPDATE TEMP_TEST SET AMOUNT=300
WHERE ID IN( SELECT * FROM DBO.SPLIT(@ID))
END
EXEC UPDATE_TEST '2,3'
November 13, 2013 at 5:46 am
subbareddy542 (11/13/2013)
hi,first can u create one function to use below code.
CREATE FUNCTION SPLIT(@VAL VARCHAR(MAX))
RETURNS @T1 TABLE(COL1 VARCHAR(MAX))
AS
BEGIN
WHILE CHARINDEX(',',@VAL)>0
BEGIN
INSERT INTO @T1 VALUES(SUBSTRING(@VAL,1,(CHARINDEX(',',@VAL))-1))
SET @val=SUBSTRING(@VAL,(CHARINDEX(',',@VAL))+1,LEN(@VAL))
END
INSERT INTO @T1 VALUES(@VAL)
RETURN
END
after that use that function in your code :
CREATE TABLE TEMP_TEST (ID INT,AMOUNT MONEY)
INSERT INTO TEMP_TEST VALUES (1,10)
INSERT INTO TEMP_TEST VALUES(2,10)
INSERT INTO TEMP_TEST VALUES(3,10)
CREATE PROC UPDATE_TEST(@ID VARCHAR(MAX))
AS
BEGIN
UPDATE TEMP_TEST SET AMOUNT=300
WHERE ID IN( SELECT * FROM DBO.SPLIT(@ID))
END
EXEC UPDATE_TEST '2,3'
The character-by-character approach to splitting strings is the slowest way to approach the problem. I would avoid it completely if you're interested in performance at all.
See Jeff Moden's article on string splitting at http://www.sqlservercentral.com/articles/Tally+Table/72993/. It has great performance. By all means, test the two approaches against one another against a 100K-row or 1M-row table and see for yourself.
Edited: Corrected the URL.
November 13, 2013 at 7:33 am
subbareddy542 (11/13/2013)
hi,first can u create one function to use below code.
CREATE FUNCTION SPLIT(@VAL VARCHAR(MAX))
RETURNS @T1 TABLE(COL1 VARCHAR(MAX))
AS
BEGIN
WHILE CHARINDEX(',',@VAL)>0
BEGIN
INSERT INTO @T1 VALUES(SUBSTRING(@VAL,1,(CHARINDEX(',',@VAL))-1))
SET @val=SUBSTRING(@VAL,(CHARINDEX(',',@VAL))+1,LEN(@VAL))
END
INSERT INTO @T1 VALUES(@VAL)
RETURN
END
after that use that function in your code :
CREATE TABLE TEMP_TEST (ID INT,AMOUNT MONEY)
INSERT INTO TEMP_TEST VALUES (1,10)
INSERT INTO TEMP_TEST VALUES(2,10)
INSERT INTO TEMP_TEST VALUES(3,10)
CREATE PROC UPDATE_TEST(@ID VARCHAR(MAX))
AS
BEGIN
UPDATE TEMP_TEST SET AMOUNT=300
WHERE ID IN( SELECT * FROM DBO.SPLIT(@ID))
END
EXEC UPDATE_TEST '2,3'
A few things...
First, I would add that you don't need a loop to perform the update (or pretty much anywhere for that matter). Take a look at this article: The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url]. Loops and cursors slow your queries dramatically and lead to bloated/less elegant code.
Avoid using (max) datatypes unless you have a requirement that specifically calls for a (max) datatype. Last, you should avoid multi-statement table valued functions (iTVFs) whenever possible and use inline table value functions instead (another reason to avoid loops: you can't create an iTVF with a loop).
Edit: Fixed formatting in my url
-- Itzik Ben-Gan 2001
November 14, 2013 at 6:21 am
If OBJECT_ID('TEST_procedure','P') is not Null Drop procedure TEST_procedure
Go
create procedure TEST_procedure
@exampleid XML
as
update tablea
set text_field = 'Y'
where
example_id in (Select x.value('(.)','varchar(100)') from @exampleid.nodes('/List/Field/text()') as x(x))
Go
If object_id('tablea') is Not Null Drop Table tablea
Create table tablea(example_id varchar(100),text_field varchar(10))
Insert tablea select 'abc','N'
Declare @exampleid XML
Select @exampleid=
(
Select
Field
From(Values('abc'),('def'),('ghi')) a(field)
For XML path(''),Root('List'),Type
)
Exec TEST_procedure @exampleid
Select * from tablea
November 14, 2013 at 8:53 am
Edward Boyle-478467 (11/14/2013)
If OBJECT_ID('TEST_procedure','P') is not Null Drop procedure TEST_procedureGo
create procedure TEST_procedure
@exampleid XML
as
update tablea
set text_field = 'Y'
where
example_id in (Select x.value('(.)','varchar(100)') from @exampleid.nodes('/List/Field/text()') as x(x))
Go
If object_id('tablea') is Not Null Drop Table tablea
Create table tablea(example_id varchar(100),text_field varchar(10))
Insert tablea select 'abc','N'
Declare @exampleid XML
Select @exampleid=
(
Select
Field
From(Values('abc'),('def'),('ghi')) a(field)
For XML path(''),Root('List'),Type
)
Exec TEST_procedure @exampleid
Select * from tablea
This does not work and is probably not the best way to split a string.
-- Itzik Ben-Gan 2001
November 14, 2013 at 8:57 am
Alan.B (11/14/2013)
Edward Boyle-478467 (11/14/2013)
If OBJECT_ID('TEST_procedure','P') is not Null Drop procedure TEST_procedureGo
create procedure TEST_procedure
@exampleid XML
as
update tablea
set text_field = 'Y'
where
example_id in (Select x.value('(.)','varchar(100)') from @exampleid.nodes('/List/Field/text()') as x(x))
Go
If object_id('tablea') is Not Null Drop Table tablea
Create table tablea(example_id varchar(100),text_field varchar(10))
Insert tablea select 'abc','N'
Declare @exampleid XML
Select @exampleid=
(
Select
Field
From(Values('abc'),('def'),('ghi')) a(field)
For XML path(''),Root('List'),Type
)
Exec TEST_procedure @exampleid
Select * from tablea
This does not work and is probably not the best way to split a string.
Agreed. Why make it more complicated than it has to be by converting it to XML? A CSV string is easy to split with DelimitedSplit8K. Last time I checked, a CSV is also a bit smaller than an XML strucure.
November 14, 2013 at 9:03 am
BTW, I checked and the code does work.
While I agree that a csv and delimitedSpilt8K are OK for this simple case, I often deal with more complex structures that are better represented in XML
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply