Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to get the desired output using Stored Procedure Expand / Collapse
Author
Message
Posted Wednesday, January 15, 2014 11:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 11, 2014 4:31 AM
Points: 18, Visits: 158
I have a table


Create table testmuljobs
(
AutoID int Identity(1,1),
JobID int not null,
AcctID int not null,
CreatedBy Varchar(100) null,
CreatedDate Datetime null,
ModifiedBy Varchar(100) null,
ModifiedDate Datetime null,
RecStatus bit null
)


and for CreatedDate and ModifiedDate i have Default Constraints

I want to insert records into that table with the help of sp as follows:
i will pass Jobid and Acctid .The Sample example stored procedure is


Create PROCEDURE [dbo].[TestMulJobsSave]
(
@JobID INT = Null,
@AcctID INT = Null,
@CreatedBy VARCHAR(100) = Null,
@ModifiedBy VARCHAR(100) = Null

)

AS

Begin

Insert into [TestMulJobs] (JobID,AcctID,CreatedBy,CreatedDate,ModifiedBy,ModifiedDate) Values

( @JobID,@AcctID,@CreatedBy,Getdate(),@ModifiedBy, Getdate() )

End


now when i execute the sp like for example TestMulJobsSave 5653,7 it is inserting the records and working fine,but my requirement is instead i want to insert as 5654-7, 5723-8, 5824-3,5654-7.All the above must be inserted as 4 records the first one is jobid and the corresponding to it is the AcctID i.e i want to insert multiple acctid and the corresponding jobiD at a time.
Post #1531411
Posted Thursday, January 16, 2014 3:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 7:36 AM
Points: 244, Visits: 114
Try this if it helps you.



CREATE FUNCTION [dbo].[fnSplit]
(
@sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN

DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1),
@sInputList=SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+DATALENGTH(@sDelimiter),DATALENGTH(@sInputList))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
ELSE IF LEN(@sItem) = 0
INSERT INTO @List SELECT NULL
END

IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END

GO
CREATE PROCEDURE [dbo].[TestMulJobsSave]
(
@JobIDANdAcctID VARCHAR(MAX),
@CreatedBy VARCHAR(100) = Null,
@ModifiedBy VARCHAR(100) = Null

)

AS

Begin

Insert into [TestMulJobs] (JobID,AcctID,CreatedBy,CreatedDate,ModifiedBy,ModifiedDate)

SELECT SUBSTRING(Item,1,CHARINDEX('-',Item)-1),SUBSTRING(Item,CHARINDEX('-',Item)+1,LEN(ITEM)),@CreatedBy,@ModifiedBy FROM TEST.dbo.fnSplit(@JobIDANdAcctID,',')
End

GO
EXEC TEST..[TestMulJobsSave] '5654-7,5723-8,5824-3,5654-7',1,1

Regards,

Vijay
Post #1531478
Posted Thursday, January 16, 2014 7:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:47 PM
Points: 13,249, Visits: 12,085
It does seem that what you need it a string splitter. The one posted above will work but the performance of that will be awful. Please take a look at the link in my signature about splitting strings. You will find a way faster string splitter in there. It even includes performance comparisons among all the various types of splitters (including the one posted above).

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1531587
Posted Thursday, January 16, 2014 10:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 11, 2014 4:31 AM
Points: 18, Visits: 158

@ vijay.singh 14112 :

Thanks a lot for the reply and the help will check it out.

Post #1531924
Posted Friday, January 17, 2014 7:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:47 PM
Points: 13,249, Visits: 12,085
iiit.raju (1/16/2014)

@ vijay.singh 14112 :

Thanks a lot for the reply and the help will check it out.



I will stress again to look at the splitter I reference in my signature. The one posted above will work but it is orders of magnitude slower.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1532051
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse