Allow repeat selections WITH SELECT

Question

Post reply

Allow repeat selections WITH SELECT

midan1

SSCertifiable

Points: 5100
More actions
October 26, 2006 at 3:42 am

#115238

i need halp to solved my problem
---------
i have a multi list thet the user can select the same product again
and after i wont to use
----------
@val=(1, 1, 1, 1, 2, 2, 2, 3, 3, 3)
INSERT INTO Table_part_all
SELECT * FROM [Table_part]
WHERE (sn IN (@val))
-----------------------------------------------
and Allow repeat selections WITH SELECT
how to do it
TNX

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

IF EXISTS (Select * from dbo.SysObjects where name = 'Numbers' and XType = 'U' and user_name(uid) = 'dbo')

DROP TABLE Numbers

GO

CREATE TABLE dbo.Numbers (PkNumber int identity(1,1) primary key clustered, dude bit null)

GO

INSERT INTO dbo.Numbers (dude)

SELECT NULL FROM (SELECT TOP 100 NULL AS A FROM master.dbo.spt_values) dt100 cross join (SELECT TOP 80 null AS A FROM master.dbo.spt_values) dt80

GO

ALTER TABLE dbo.Numbers

DROP COLUMN dude

GO

CREATE FUNCTION [dbo].[fnSplit_Set] (@IDS as varchar(8000), @vcDelimiter varchar(3))

RETURNS TABLE

WITH SCHEMABINDING

AS

Return

Select dtSplitted.EachID, dtSplitted.Rank from (

SELECT SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter),

CHARINDEX(@vcDelimiter, @vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter)) - N.PkNumber - len(@vcDelimiter)) as EachID

, (LEN(SUBSTRING(@IDs, 1, N.PkNumber)) - LEN (REPLACE (SUBSTRING(@IDs, 1, N.PkNumber), ',', ''))) + 1 AS Rank

FROM dbo.Numbers N

WHERE SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber, len(@vcDelimiter)) = @vcDelimiter

AND PkNumber < LEN(@vcDelimiter + @IDs + @vcDelimiter)

&nbsp dtSplitted where len(dtSplitted.EachID) > 0

GO

DECLARE @Ids as varchar(8000)

SET @IDS = ''

Select @IDS = @IDS + CAST(id as varchar(10)) + ',' from (Select TOP 10 id from dbo.SysObjects order by NEWID()) dtIds

SET @IDS = left(@IDS, ABS(len(@IDS) - 1))

PRINT @IDS

Select O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by O.Name

Select O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by dtIDS.Rank

--Dont't ever forget to cast the eachid column to the same datatype as in the join to avoid table scans.

midan1 SSCertifiable Points: 5100 More actions · Answer 2

ohhhhhhhhhhhhhhh

very slowly

Is there simple way to do it ?????

TNX

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Check the final selects, doesn't get much simpler than that :

Select * from dbo.Mytable where Colid IN (Select CAST(EachID as <colid datatype&gt AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')).

The rest is just a perm table and a function that you keep on the server.

midan1 SSCertifiable Points: 5100 More actions · Answer 4

hi TNX

but i get an error whan i copy

all the big text !!!

can you fix it

---------------

Server: Msg 170, Level 15, State 1, Procedure fnSplit_Set, Line 13

Line 13: Incorrect syntax near 'dtSplitted'.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Replace the smiley face with a ")" without the quotes.