January 27, 2012 at 12:53 am
I need some help. I want to include chapterindex in the fields that are returned by running the stored procedure below.
Note: I will pass the parameter(ChapterIndex,name,abtract,descrition,duration) with n number of values as a single parameter each one.
So u have to split that values and then insert that in a separate row in tblChapters table.
U need to write a function for split that values by specified delimiter so make aware of functions.
it was a store procedure to insert vlaues in the three table tblcourses,tblchapters,tblFiles
which is having primary key and foregn key
Output should like below format ,
tblcourses
----------------
CourseId Name Abstract Description Duration
------------------------------------------------------------------------------
1 a ffds sdgf 2months
tblchapters
------------------
ChapterId CourseId ChapterIndex Name Abstract Description Duration
-----------------------------------------------------------------------------------------------------------------
1 1 1 c1 dfgf fgfd 20 days
2 1 2 c2 dfgf fgfd 20 days
3 1 3 c3 dfgf fgfd 20 days
tblFiles
--------------------
FileId ChapterId FileTypeId FileIndex FileName Extension Description
-----------------------------------------------------------------------------------------------------------
1 1 1 1 df .doc fgdsf
2 2 2 1 df .jpg fgdsf
Can you help?
and i created store procedure like this
create PROCEDURE sp_add_course
(
@CourseName varchar(200),
@CourseAbstract varchar(2000),
@CourseDescription text,
@CourseDuration int,
@ChapterIndex int,
@ChapterName varchar(200),
@ChapterAbstract varchar(200),
@ChapterDescription text,
@ChapterDuration int,
@FileTypeId int,
@FileIndex int,
@FileName varchar(200),
@Extension varchar(200),
@Description text
)
AS
BEGIN
Set Nocount On
DECLARE @CourseId int
insert into dbo.tblCourses
( Name, Abstract,Description, Duration )
values ( @CourseName , @CourseAbstract , @CourseDescription , @CourseDuration )
select @CourseId=@@IDENTITY
DECLARE @ChapterId int
insert into dbo.tblChapters ( Courseid,ChapterIndex,Name, Abstract, Description, Duration )
values ( @CourseId, @ChapterIndex, @ChapterName, @ChapterAbstract, @ChapterDescription, @ChapterDuration )
select @ChapterId=@@IDENTITY
insert into dbo.tblFiles ( ChapterId, FileTypeId, FileIndex, FileName, Extension,Description )
values ( @ChapterId,@FileTypeId, @FileIndex,@FileName,@Extension,@Description)
END
January 27, 2012 at 1:32 am
January 27, 2012 at 2:04 am
i just want split function
for tblchapters in a course there will be lot of chatpters
chatpter 1
chapter 2 that
split function will be called inside the store procedure
January 27, 2012 at 2:18 am
That's exactly what I gave to you. Did you read the article?
Beyond that, I would have to do the work for you.
BTW, CAPS and bold is an unpolite way to post on the forums: looks like you are shouting at us.
-- Gianluca Sartori
January 27, 2012 at 2:26 am
can u plz help me
January 27, 2012 at 2:36 am
er.sivaganesh (1/27/2012)
can u plz help me
Gianluca tried. Twice. You're not bothering to help yourself.
The article he linked to Jeff Moden's splitter function is a walkthrough on how to use it. It's near the bottom if you want to skip the entire discussion, third code piece from the bottom, but that'll skip how to use it.
We're not going to spoon feed you. There's no sample schema and data for us to work from. There's an expected output but nothing we can directly work from. If you want custom code, see the first link in my signature for how to set that up.
We can't help you if you won't help you.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 27, 2012 at 3:01 am
er.sivaganesh (1/27/2012)
i just want split functionfor tblchapters in a course there will be lot of chatpters
chatpter 1
chapter 2 that
split function will be called inside the store procedure
Read the article that you were pointed at. Towards the end you'll of the discussion on how it works and why, you'll find the best T-SQL string splitter so far. You could write a CLR string splitter that will just about out-perform it, but if T-SQL is your preference then this is what you want.
If you're too lazy to read the article that you've been pointed at, then you could instead just copy and paste the below code and hope that it does what you want. Or you could read the article and make sure. Your choice.[/url]
CREATE FUNCTION dbo.DelimitedSplit8K
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
--===== Jeff Moden's 8K String Splitter function
-- Article containing usage and discussion of how it works can be found
-- here --> http://qa.sqlservercentral.com/articles/Tally+Table/72993/
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT t.N+1
FROM cteTally t
WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0))
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT
ItemNumber = ROW_NUMBER() OVER (ORDER BY s.N1),
Item = SUBSTRING(@pString, s.N1, ISNULL(NULLIF(CHARINDEX(@pDelimiter, @pString, s.N1), 0) - s.N1, 8000))
FROM cteStart s;
January 27, 2012 at 6:45 am
er.sivaganesh (1/27/2012)
can u plz help me
Yes. Post an example of the actual data you want split and tell us how you want it split.
--Jeff Moden
January 29, 2012 at 12:30 am
in a single store procedure iam just inserting values for 3 table in that among the 3 table the seond table tbl.chapter must have to provide another row to add another chapter for that only iam asking function for the second table which has to be called inside the store procedure did you get my point
sorry for the inconvinence
iam extremly sorry to distrub all of U
January 29, 2012 at 5:05 am
er.sivaganesh (1/29/2012)
in a single store procedure iam just inserting values for 3 table in that among the 3 table the seond table tbl.chapter must have to provide another row to add another chapter for that only iam asking function for the second table which has to be called inside the store procedure did you get my pointsorry for the inconvinence
iam extremly sorry to distrub all of U
There's no reason to be sorry.
A "verbal description" usually is based on the business case one is faced with but is not included in the description to keep the post short. Also, a language barrier may also play a bigger role than "talking in a common language". And the "common language" on this forum is MS SQL Server code.
Therefore, instead of trying verbally to describe what you're looking for, describe the scenario in our "common language" (= post some sample code in a ready to use format and the expected result based on those data).
January 29, 2012 at 5:49 am
May I ask why you are passing values to the database (stored procedure) in such a horrible way? Passing table objects would make your SP much cleaner and also you can do set based operation on it in SP, if required. Giving one example below, you need to modify it per your need.
USE AdventureWorks2008R2;
GO
/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate INT );
GO
/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE usp_InsertProductionLocation
@TVP LocationTableType READONLY
AS
SET NOCOUNT ON
INSERT INTO [AdventureWorks2008R2].[Production].[Location]
([Name]
,[CostRate]
,[Availability]
,[ModifiedDate])
SELECT *, 0, GETDATE()
FROM @TVP;
GO
/* Declare a variable that references the type. */
DECLARE @LocationTVP
AS LocationTableType;
/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
SELECT [Name], 0.00
FROM
[AdventureWorks2008R2].[Person].[StateProvince];
/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO
More: search for 'Table-Valued Parameter'
January 29, 2012 at 10:46 am
er.sivaganesh (1/29/2012)
in a single store procedure iam just inserting values for 3 table in that among the 3 table the seond table tbl.chapter must have to provide another row to add another chapter for that only iam asking function for the second table which has to be called inside the store procedure did you get my pointsorry for the inconvinence
iam extremly sorry to distrub all of U
I kind of get your point but to really help, I need an actual example of the actual data that you're trying to pass. I'm not trying to be difficult. I'm just trying to help but you're not giving us much to go on.
--Jeff Moden
January 29, 2012 at 11:13 pm
table1
(
FileTypeId int ,
FileIndex int,
filename varchar(20),
Extension varchar(200)
Description text
)
here iam having the 4 column
the output should be in one insert function i want to insert data more thant one like this
FileTypeId FileIndex FileName Extension Description
1 1 df .doc fgdsf
2 1 df .jpg fgdsf
January 29, 2012 at 11:14 pm
table1
(
FileTypeId int ,
FileIndex int,
filename varchar(20),
Extension varchar(200)
Description text
)
here iam having the 4 column
the output should be in one insert function i want to insert data more thant one like this
FileTypeId FileIndex FileName Extension Description
1 1 df .doc fgdsf
2 1 df .jpg fgdsf
January 30, 2012 at 12:39 am
i posted the details
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply