March 10, 2012 at 11:27 pm
how to write an insert store for a input like 1_2|3_4 it should get seprated and stored in a table
EXEC [dbo].[SP_InsertGrabberDetails]
@RowColumn = '1_2|2_3|1_3',
@AdSpaceId = 1
create PROCEDURE [SP_Insert]
(
@RowColumn int,
@AdSpaceId int
)
AS
BEGIN
Set Nocount On
begin
Insert into tblAdBl( AdSpaceId ,Row ,[Column])
select @AdSpaceId , Row,[Column]
from [dbo].[Udf_tblsplit_Split](@RowColumn,',')
declare @TotalBlocks int
set @TotalBlocks=count (row)from tblAdBl where AdSpaceId = @AdSpaceId
declare @indexnumber int
set @indexnumber=count (row)from tblAdBl where AdSpaceId = @AdSpaceId
declare @test-2 int
set @test-2=@indexnumber+1
declare @id1 int
set @id1=scopeidentity
update tblAdBl set indexnumber=@test,TotalBlocks=@TotalBlocks where adbid=@id1
end
iam trying to get output in the table like this
adbid AdSpaceId row column indexnumber TotalBlocks
1 1 1 2 1 3
2 1 2 3 2 3
3 1 1 3 3 3
March 11, 2012 at 12:56 am
Please describe how the input data need to be separated to return the result set you're looking for. Even though we could figure most of it by reverse-engineer your undocumented sproc (including guessing what the UDF would do and return), but I don't think there'll be many of us willing to do so. So, please help us help you.
Furthermore, I recommend to replace the UDF split function with the DelimitedSplit8K (see the link in my signature for details) and call it with a separatpr that's actually part of the string (there's no comma in @RowColumn ...).
March 11, 2012 at 1:57 am
the input data will be like this
EXEC [dbo].[SP_InsertGrabberDetails]
@RowColumn = '1_2|2_3|1_3',
@AdSpaceId = 1
and iam trying output in the table like this
adbid AdSpaceId row column indexnumber TotalBlocks
1 1 1 2 null null
2 1 2 3 null null
3 1 1 3 null null
after getting the input from the table i just want to update index number column and total block depend upon row and where AdSpaceId
then the output will be like this
adbid AdSpaceId row column indexnumber TotalBlocks
1 1 1 2 1 3
2 1 2 3 2 3
3 1 1 3 3 3
March 11, 2012 at 3:12 am
I've seen the expected output before.
But what rules do apply?
Are the values of indexnumber identical to adbid or how are those values calculated? What does the value in TotalBlocks represent?
March 11, 2012 at 3:28 am
a member is selecting space which i represent as space id
for example if two member selecting two space id will be represented the (1,2) is first row second column
adbid AdSpaceId row column indexnumber TotalBlocks
1 1 1 2 1 2
2 1 2 3 2 2
3 2 3 3 1 1
here depend upon the count row in where AdSpaceId= will provide total blocks, and indexnumber
here i gave for example how the table look
March 11, 2012 at 3:38 am
March 11, 2012 at 3:52 am
tell me up to this its enough for me than i can update that remaining column
the input data will be like this
EXEC [dbo].[SP_InsertGrabberDetails]
@RowColumn = '1_2|2_3|1_3',
@AdSpaceId = 1
and iam trying output in the table like this
adbid AdSpaceId row column indexnumber TotalBlocks
1 1 1 2 null null
2 1 2 3 null null
3 1 1 3 null null
March 11, 2012 at 4:08 am
Using the DelimitedSplit8K function refernced in my signature I would start with
DECLARE @RowColumn VARCHAR(30)
SET @RowColumn ='1_2|2_3|1_3'
SELECT
Itemnumber AS adbid ,
LEFT(item,CHARINDEX('_',item,1)-1) AS [row] ,
STUFF(item,1,CHARINDEX('_',item,1),'') AS [column]
FROM dbo.DelimitedSplit8K (@RowColumn,'|')
March 11, 2012 at 4:38 am
can u plz help me up to ths secession
in my store proc i am not getting correctly
EXEC [dbo].[SP_InsertGrabberDetails]
@RowColumn = '1_2|2_3|1_3',
@AdSpaceId = 1
and iam trying output in the table like this
adbid AdSpaceId row column indexnumber TotalBlocks
1 1 1 2 null null
2 1 2 3 null null
3 1 1 3 null null
March 11, 2012 at 4:44 am
I already did. See the code snippet above. It returns almost exactly what you're looking for.
It shouldn't be too hard to adjust it to your needs.
March 11, 2012 at 5:08 am
i used that inside my store proc but iam not getting clear output
alter PROCEDURE [dbo].[SP_InsertGrabberDetails1]
(
--@TotalBlocks int,
@AdSpaceId int,
@RowColumn int
)
AS
BEGIN
Set Nocount On
begin
Insert into tblAdBlock( AdSpaceId ,Row ,[Column])
SELECT
Itemnumber AS AdSpaceId ,
LEFT(item,CHARINDEX('_',item,1)-1) AS [row] ,
STUFF(item,1,CHARINDEX('_',item,1),'') AS [column]
FROM dbo.DelimitedSplit8K (@RowColumn,'|')
end
Error converting data type varchar to int
begin
DECLARE @TotalBlocks INT
set @TotalBlocks=(SELECT COUNT(Row) FROM dbo.TBLADBL where AdSpaceId=@AdSpaceId)
UPDATE [TBLADBL1]
set
totalblock=@TotalBlocks
where AdSpaceId=@AdSpaceId
end
END
March 12, 2012 at 8:14 am
hi
you can use XML data_type, like this:
DECLARE @RowColumn VARCHAR(MAX)
, @xml XML
SET @RowColumn ='1_2|2_3|1_3'
SET @RowColumn =
'<r><c><row>'
+ REPLACE(REPLACE(@RowColumn, '|', '</col></c><c><row>'), '_', '</row><col>')
+ '</col></c></r>'
set @xml = @RowColumn
insert into #your_table
SELECT1
, t.l.value('row[1]', 'int')
, t.l.value('col[1]', 'int')
, null
, null
FROM@xml.nodes('/r/c') t(l)
does it solve it to you?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply