help with parameter - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help with parameter

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2009-06-03 : 16:02:23

this is my sp:

-- Add the parameters for the stored procedure here
@sectionID int, @PageCount int output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT @PageCount = SUM(PageCount)
FROM tblSectionPages
WHERE SectionID IN (@sectionID)

The param that is giving me a problem is the @sectionID. I need to be able to pass in multiple numbers. This sp used to be an inline query in the code and worked fine when @sectionID was 163,164, but does not work in this sp when I pass in @sectionID as 163,164

Can anyone help me with this? Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-03 : 16:09:49

I hope this will help:
http://www.sommarskog.se/arrays-in-sql-2005.html

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

ann
Posting Yak Master

220 Posts

Posted - 2009-06-03 : 16:31:14

ok, so I read the article and want to use this function:

CREATE FUNCTION iter_intlist_to_tbl (@list nvarchar(MAX))
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
number int NOT NULL) AS
BEGIN
DECLARE @startpos int,
@endpos int,
@textpos int,
@chunklen smallint,
@str nvarchar(4000),
@tmpstr nvarchar(4000),
@leftover nvarchar(4000)

SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = ltrim(@leftover +
substring(@list, @textpos, @chunklen))
SET @textpos = @textpos + @chunklen

SET @startpos = 0
SET @endpos = charindex(' ' COLLATE Slovenian_BIN2, @tmpstr)

WHILE @endpos > 0
BEGIN
SET @str = substring(@tmpstr, @startpos + 1,
@endpos - @startpos - 1)
IF @str <> ''
INSERT @tbl (number) VALUES(convert(int, @str))
SET @startpos = @endpos
SET @endpos = charindex(' ' COLLATE Slovenian_BIN2,
@tmpstr, @startpos + 1)
END

SET @leftover = right(@tmpstr, datalength(@tmpstr) / 2 - @startpos)
END

IF ltrim(rtrim(@leftover)) <> ''
INSERT @tbl (number) VALUES(convert(int, @leftover))

RETURN
END

But I can't figure out based on the example how I would use it in my stored procedure? I've never worked with functions before ...

ann
Posting Yak Master

220 Posts

Posted - 2009-06-03 : 17:20:10

ok - I figured it out:

SELECT @PageCount = SUM(PageCount)
FROM tblSectionPages
JOIN iter$simple_intlist_to_tbl(@sectionid) i on tblSectionPages.SectionID = i.number

which works, but now I have to do the same with an update:

-- UPDATE tblSections
-- SET ViewCount = ViewCount + 1, LastViewDate = GetDAte()
-- WHERE SectionID IN (@sectionID)

I tried doing:

UPDATE tblSections
SET ViewCount = ViewCount + 1, LastViewDate = GetDAte()
iter$simple_intlist_to_tbl(@sectionid) i on tblSectionPages.SectionID = i.number

but of course, that doesn't work. Anyone can help please?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-04 : 04:29:14

do you mean this?


UPDATE ts
SET ViewCount = ViewCount + 1, LastViewDate = GetDAte()
from tblSections ts
join iter$simple_intlist_to_tbl(@sectionid) i on ts.SectionID = i.number

No, you're never too old to Yak'n'Roll if you're too young to die.

a.rameshk
Starting Member

19 Posts

Posted - 2009-06-04 : 09:13:41

DECLARE @SectionD VARCHAR(2000)
DECLARE @PageCount INT
SET @SectionD='163,164'
SELECT @PageCount=SUM(PageCount) FROM tblSectionPages WHERE SectionID IN (SELECT [Value] FROM dbo.[SplitDelimited](@SectionD,','))
Here splitDelimited is the function. You can use above function which is written by Ann. If you are passing more than one Integer values in SectionID pass those in VArchar instead of INT.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources