Problem with SQL function

Question

Post reply

Problem with SQL function

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

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

There's no syntax errors with the function. The problem is how you're calling it.

... FROM [InfraDesk_ASP_3].[dbo].[RPT_nSLA_REPORT] ( 'All Priorities',1222799400,1225391399,null,null,('Software'),

('Client/Server Application',' Intel Engineering',' INTERNET'), ('Appliance',' Application'), ('')

Functions take their parameters in a single set of brackets. You have multiple sets of brackets. So, it should be something more like this (if I'm guessing right what you want to do)

FROM [InfraDesk_ASP_3].[dbo].[RPT_nSLA_REPORT] ( 'All Priorities',1222799400,1225391399,null,null,'Software',

'''Client/Server Application'','' Intel Engineering'','' INTERNET'', ''Appliance'','' Application'', '''')

That said, the function's probably not going to do what you want.

and CATEGORIZATION_CLASS IN ('@class')

and CATEGORIZATION_CATEGORY IN ('@category')

and CATEGORIZATION_TYPE IN ('@type')

and CATEGORIZATION_ITEM IN ('@item')

If you pass a comma-delimited list in a variable and then use it in an IN, SQL will not treat it like a list of values, but rather will do an equality. To do what you want requires either dynamic SQL (which isn't allowed in a function) or a split function (check the scripts library here for several good ones)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Vijoy Kolady SSChasing Mays Points: 607 More actions · Answer 2

Thanks for a prompt response.

Can you please give any sample code for splitting the values for the function ? Or I would be very grateful if you could provide me with a link.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

This one's pretty good. Read the comments on the blog post too, as the function needs a table creating.

http://philcart.blogspot.com/2007/06/split-function.html

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 4

GilaMonster (10/3/2008)
This one's pretty good. Read the comments on the blog post too, as the function needs a table creating.
http://philcart.blogspot.com/2007/06/split-function.html

Teach someone to fish... please see the following URL for how a Tally or Numbers table actually works to do a split. It also shows you how to make one without a bloody While loop! 😉

http://www.sqlservercentral.com/articles/TSQL/62867/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)