Stored Procedure - Pass-Through SQL?

[cLFlaVA]

Hi all,

I'm creating a stored procedure that will return a number of records to the calling agent - in this case an ASP application.

In the ASP page, I have a comma-delimited set of project numbers. I'd like the SP to only return data for those project numbers.

Do I need pass-through SQL? If so, what is the correct way to write it? A simple select as an example would be great.

Also, something tells me this is inefficient - any other methods I should consider?

Thanks,

Cory



*cLFlaVA
----------------------------
[tt]somebody set up us the bomb![/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[SQLDenis]

this is my way
create number table (only once of course) then split string and dump into temp table
join the temp table on your table done
you can also use a UDF but I prefer this method since I already have a number table ready

-- Create our Number table ** do this only once-- populate it with 1000 rows
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)
DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0
WHILE @intLoopCounter <=999 BEGIN
INSERT INTO NumberPivot
VALUES (@intLoopCounter)
SELECT @intLoopCounter = @intLoopCounter +1
END
GO 

--String manipulation with a pivot table
DECLARE @chvGroupNumbers VARCHAR(1000)
SELECT @chvGroupNumbers ='1,4,77,88,4546,234,2,3,54,87,9,6,4,36,6,9,9,6,4,4,68,9,0,5,3,2,'

SELECT SUBSTRING(',' + @chvGroupNumbers + ',', NumberID + 1,
CHARINDEX(',', ',' + @chvGroupNumbers + ',', NumberID + 1) - NumberID -1)AS Value
INto #tempTable
FROM NumberPivot
WHERE NumberID <= LEN(',' + @chvGroupNumbers + ',') - 1
AND SUBSTRING(',' + @chvGroupNumbers + ',', NumberID, 1) = ','


select * from #tempTable t join YourTable y on t.value =y.YourColumn

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[cLFlaVA]

can you explain what this is doing? i get 1 row with a blank value when i try to incorporate it into my test:

drop table #tempTable

declare @chvGroupNumbers nvarchar(1000)
SELECT @chvGroupNumbers ='1292,1356,54,125,624,'

SELECT SUBSTRING(',' + @chvGroupNumbers + ',', sub_id + 1,
CHARINDEX(',', ',' + @chvGroupNumbers + ',', sub_id + 1) - sub_id -1)AS Value
INto #tempTable
FROM sub_project
WHERE sub_id <= LEN(',' + @chvGroupNumbers + ',') - 1
AND SUBSTRING(',' + @chvGroupNumbers + ',', sub_id, 1) = ','

select * from #tempTable

thanks...



*cLFlaVA
----------------------------
[tt]somebody set up us the bomb![/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[SQLDenis]

try this

drop table #tempTable

declare @chvGroupNumbers nvarchar(1000)
SELECT @chvGroupNumbers ='1292,1356,54,125,624,'

SELECT SUBSTRING(',' + @chvGroupNumbers + ',', sub_id + 1,
CHARINDEX(',', ',' + @chvGroupNumbers + ',', sub_id + 1) - sub_id -1)AS Value
INto #tempTable
FROM NumberPivot
WHERE sub_id <= LEN(',' + @chvGroupNumbers + ',') - 1
AND SUBSTRING(',' + @chvGroupNumbers + ',', sub_id, 1) = ','

select * from #tempTable t join sub_project y on t.value =y.sub_id

btw you first need to run this for it to work

-- Create our Number table ** do this only once-- populate it with 1000 rows
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)
DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0
WHILE @intLoopCounter <=999 BEGIN
INSERT INTO NumberPivot
VALUES (@intLoopCounter)
SELECT @intLoopCounter = @intLoopCounter +1
END
GO

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[cLFlaVA]

what is the significance of the 1000 rows in the NumberPivot table?



*cLFlaVA
----------------------------
[tt]somebody set up us the bomb![/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[SQLDenis]

>>what is the significance of the 1000 rows in the NumberPivot table?

nothing I use that table for a lot of things, for example this-->(

http://sqlservercode.blogspot.com/2005/09/fast-date-ranges-without-loops-in-sql.html)

when i have 1000 rows I know I won't run out of space

In your case 20 might be enough

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[cLFlaVA]

Thanks Denis. I ended up using a different concept:

- perform insert of project ids into a handshaking table
- join project table on handshaking table
- retrive all rows with specified handshaking id

similar concept, i think, but more dynamic. the project table grows, and i don't want to have to add additional numbers to the NumberPivot table after the fact.

Thanks for your insight though, it pointed me in the right direction.

-Cory



*cLFlaVA
----------------------------
[tt]somebody set up us the bomb![/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]