query proc - 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

General SQL Server Forums

New to SQL Server Programming

query proc

Author

Topic

tomlinde
Starting Member

2 Posts

Posted - 2009-12-01 : 10:51:32

i have one function called fncoststring which i have to call in procedure below
CREATE PROCEDURE [dbo].[proccost]

@sname varchar(1000) OUTPUT

AS

SELECT Distinct
s.salesid, sc.costid, sc.costdesc, d.depttitle, d.deptno,m.code, m.codename, @sname 'sales name'

FROM salescost sc
join sales s
on s.salesid = sc.salesid
join dept d
on d.salesid = d.salesid and s.deptno = d.deptno
join marketing m
on m.salesid = s.salesid

WHERE s.salesid=101 and sc.status = 'true'
and s_name In (select * from fncoststring (@sname))

--Exec procost 'Casino' (it gives me 5rows)
--Eexc procost 'Casino,High Traffic,Emblem' (it also give me 5rows instead of 12rows)
it gives me like -
salesid costid costdesc depttitle deptno code codename Salesname
------------------------------------------------------ Casino,High Traffic,Emblem

instead of i want -------------Casino - 5rows
-------------------------------High Traffic 3rows
-------------------------------Emblem - 4rows
so total 12rows...can anyone give me any suggestions.

Thanks,
Tom

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-01 : 10:59:52

Without the code of fncoststring we cannot help.

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

tomlinde
Starting Member

2 Posts

Posted - 2009-12-01 : 11:07:03

The function is fncoststring-

CREATE FUNCTION [dbo].[fncoststring]

(

@word VARCHAR(5000)

)

RETURNS @kword TABLE (word VARCHAR(1000))

AS

BEGIN

DECLARE @Words VARCHAR(500)

DECLARE @Tempword TABLE (word VARCHAR(5000))

DECLARE @Delimiter VARCHAR(10)

SET @Delimiter = '|'

WHILE (CHARINDEX(@Delimiter, @word, 1)>0)

BEGIN

SET @Words = SUBSTRING(@word, 1 , CHARINDEX(@Delimiter, @word, 1) - 1)

SET @word = SUBSTRING(@word, CHARINDEX(@Delimiter, @word, 1) + 1, LEN(@word))

INSERT INTO @Tempword VALUES(@Words)

END

INSERT INTO @Tempword VALUES(@word)

INSERT @kword

SELECT * FROM @Tempword

RETURN

END

--select * from fncoststring('normal|subnormal|abnormal')

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-01 : 11:18:01

Wait!
In a short time someone comes up and tells you that this function isn't working and shows you the right way.

I'm away from keyboard now...

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-01 : 13:38:14

Sorry I have had a look closer now...
The function is working.
The procedure is working.
Your <@sname 'sales name'> in the select list displays always the unchanged variable @sname.
If you are missing records in the result set then maybe because of the DISTINCT?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources