Prev: Blocking with a View
Next: Possible to select a sample rows based on a column value?
From: maxx on 12 May 2010 14:28
It would help the community quite a bit if people would stop preaching about how things are supposed to be in a perfect world, and just answered the question directly.

I'm really tired of reading the same opinionated babble from engineers who clearly lack the experience to address real-world issues.

ALL code is procedural, including SQL. Hence the term: Stored Procedure. Don't be blinded by your textbooks, they don't know everything.



--CELKO-- wrote:

Re: Problems with scalar function, and dynamic SQL
07-Feb-08

SELECT UPPER(@parm),
SUM(CASE WHEN UPPER(@parm) = 'ME'
AND col_x IN (1, 9)
THEN 1
WHEN UPPER(@parm) = 'YOU'
AND col_x = 8
THEN 1 ELSE 0 END) AS parm_cnt
FROM Foobar;

Watch out for a NULL in the ELSE clause. Display your parameter so
you know what count you are actually getting back from the query. Do
not think in terms of functions and dynamic SQL; use queries and not
procedural code.

Previous Posts In This Thread:

On Tuesday, February 05, 2008 10:43 AM
Uri Dimant wrote:

Re: Problems with scalar function, and dynamic SQL
Gaz
Read this stuff
http://www.sommarskog.se/arrays-in-sql.html

On Tuesday, February 05, 2008 11:59 AM
Santhos wrote:

Re: Problems with scalar function, and dynamic SQL
Please try a solution like this

Declare @Type varchar(10)
Create Table #temp(ReturnMe int)
Declare @SQL nvarchar(max)
Set @Type = 'Me'
set @SQL = 'Insert into #temp SELECT Count(t1.column) '+
'FROM table t1 ' +
'WHERE t1.column in(Select * from dbo.CustomFunction(''' + @Type + '''))'
exec sp_executesql @SQL
Select ReturnMe from #temp

"Gaz" wrote:

On Tuesday, February 05, 2008 5:51 PM
Erland Sommarskog wrote:

Re: Problems with scalar function, and dynamic SQL
Gaz (gonkowonko(a)gmail.com) writes:

Keep in mind that "col IN (@val1, @val2, @val3)" is just short for

col = @val1 OR col = @val2 OR col = @val3

Seen in that the perspective, the above makes little sense.


You cannot use dynamic SQL in functions.

For once the prize for having posted the correct solution goes to
Joe Celko.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

On Thursday, February 07, 2008 9:32 PM
Gaz wrote:

Problems with scalar function, and dynamic SQL
Hi,

Im trying to write a scalar function that basically does a count based
a set of criteria. What i first tried to do was a case statement
within the where clause which looked like this...

select @ReturnMe=count(column)
from table
where

t1.column in (
case when Upper(@value) = 'ME' then
1,9
case when Upper(@Value) = 'YOU' then
8
)


if i use single values then it works and my function returned a value
of the count. So i thought i would try and dynamically create the SQL
statement and then exec it but SQL Server gets upset and throws up an
error message.

This is my code...
-------------------------------------------------------------------------------------------------------------------------------
DECLARE @ReturnMe int,
@SQL nvarchar(max)

set @ReturnMe = 0

set @SQL = 'SELECT @ReturnMe = Count(t1.column) '+
'FROM table.column t1 ' +
'WHERE t1.column in('+ dbo.CustomFunction(@Type)+')'


exec sp_executesql @SQL

RETURN @ReturnMe
----------------------------------------------------------------------------------------------------------------------------

The CustomFunction function is returning a string like 1,9 or 8 for
example. But when i execute this i get this message

Only functions and extended stored procedures can be executed from
within a function.
Invalid attempt to read when no data is present.

Can anyone offer any advice? All i want to do is return the value of
the count column to the calling store procedure

On Thursday, February 07, 2008 9:32 PM
Gaz wrote:

I can get it to work dynamically i suppose its more of a case how do iget exec
I can get it to work dynamically i suppose its more of a case how do i
get exec to return me a value for my scalar function?

On Thursday, February 07, 2008 9:32 PM
--CELKO-- wrote:

Re: Problems with scalar function, and dynamic SQL
SELECT UPPER(@parm),
SUM(CASE WHEN UPPER(@parm) = 'ME'
AND col_x IN (1, 9)
THEN 1
WHEN UPPER(@parm) = 'YOU'
AND col_x = 8
THEN 1 ELSE 0 END) AS parm_cnt
FROM Foobar;

Watch out for a NULL in the ELSE clause. Display your parameter so
you know what count you are actually getting back from the query. Do
not think in terms of functions and dynamic SQL; use queries and not
procedural code.


Submitted via EggHeadCafe - Software Developer Portal of Choice
Free Online Courses Available for Eggheadcafe.com Users
http://www.eggheadcafe.com/tutorials/aspnet/5261083e-6e03-4b25-8728-fc3cf6855293/free-online-courses-avail.aspx
From: Dan on 14 May 2010 06:56

"maxx lopes" wrote in message news:2010512142824maxxtheaxe(a)hotmail.com...
> It would help the community quite a bit if people would stop preaching
> about how things are supposed to be in a perfect world, and just answered
> the question directly.

Wouldn't it also help if people didn't use EggHeadCafe to post replies to
threads that more than 2 years old?!?!

The newsgroups here are going to disappear soon, which will leave
EggHeadCafe with a big hole to fill in their content thieving, which is the
one thing the newsgroups closure has going for it.

--
Dan

From: Gert-Jan Strik on 14 May 2010 08:27
Dan wrote:
>
> "maxx lopes" wrote in message news:2010512142824maxxtheaxe(a)hotmail.com...
> > It would help the community quite a bit if people would stop preaching
> > about how things are supposed to be in a perfect world, and just answered
> > the question directly.
>
> Wouldn't it also help if people didn't use EggHeadCafe to post replies to
> threads that more than 2 years old?!?!
>
> The newsgroups here are going to disappear soon, which will leave
> EggHeadCafe with a big hole to fill in their content thieving, which is the
> one thing the newsgroups closure has going for it.
>
> --
> Dan

I have seen that post about the closing. I hope it doesn't happen.

I know my news reader is arcane (Netscape Navigator 4.79), but it
doesn't work on Microsoft's Forums NNTP Bridge. And working with the GUI
is quite horrible.

Now I have all my contributions in one place. Also, my newsreader only
shows unread posts, and it is EASY to mark a post as unread again.

I only see Microsoft's self interest as the reason for moving away from
newsgroups.

--
Gert-Jan
From: Dan on 14 May 2010 09:36

"Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> wrote in message
news:4BED41C5.CF9E409D(a)xs4all.nl...
> Dan wrote:
>>
>> "maxx lopes" wrote in message news:2010512142824maxxtheaxe(a)hotmail.com...
>> > It would help the community quite a bit if people would stop preaching
>> > about how things are supposed to be in a perfect world, and just
>> > answered
>> > the question directly.
>>
>> Wouldn't it also help if people didn't use EggHeadCafe to post replies to
>> threads that more than 2 years old?!?!
>>
>> The newsgroups here are going to disappear soon, which will leave
>> EggHeadCafe with a big hole to fill in their content thieving, which is
>> the
>> one thing the newsgroups closure has going for it.
>>
>> --
>> Dan
>
> I have seen that post about the closing. I hope it doesn't happen.

I doubt there's anything anyone can do stop it. While the groups might
survive on NNTP servers that ignore the MS group removal message, the
shutting down of msnews.microsoft.com will effectively take out the majority
of the posters. It's not a case of MS asking if the newsgroups are being
used - they have announced that they will be shut down

> I know my news reader is arcane (Netscape Navigator 4.79), but it
> doesn't work on Microsoft's Forums NNTP Bridge. And working with the GUI
> is quite horrible.

I for one won't be moving to the new web based forums. It doesn't even work
with IE8 properly most of the time :P

However, the NNTP bridge looks interesting, I'll admit I'd missed that. It
provides a way for an NNTP newsreader to use the new system just as
msnews.microsoft.com is used now. It's basically a local HTTP to NNTP proxy
server that interacts directly with the new MS forums.

> Now I have all my contributions in one place. Also, my newsreader only
> shows unread posts, and it is EASY to mark a post as unread again.

Indeed, that's partly why I prefer NNTP over web based forums for this sort
of thing. I'm not sure how much functionality the bridge has, but I might
give it a go.

> I only see Microsoft's self interest as the reason for moving away from
> newsgroups.

Whatever happens it'll be "self interest", otherwise there would be no
reason to do it ... Moving to a web forum does have benefits, especially
for new users who don't understand what newsgroups are. It also means that
MS no longer have to support a cluster of NNTP servers that are likely
solely used for this purpose - that hardware can now be put to use
elsewhere, or removed as a cost saving measure (running servers is never
free, after all).

If the NNTP Bridge works, then I'll move to it and will still read and post
as before, I do find these groups a lot more useful than trawling the web
for answers. I just hope that the MVPs and other helpful posters don't get
put off by the removal of the NNTP servers and decide to give up on the MS
groups, or else there will be little point in continuing to use them.

--
Dan

From: Dan on 14 May 2010 10:05

"Dan" <news(a)worldofspack.com> wrote in message
news:##bjHq28KHA.5476(a)TK2MSFTNGP06.phx.gbl...
> However, the NNTP bridge looks interesting, I'll admit I'd missed that. It
> provides a way for an NNTP newsreader to use the new system just as
> msnews.microsoft.com is used now. It's basically a local HTTP to NNTP
> proxy server that interacts directly with the new MS forums.

Think I'll give up on this. Most of the groups I use regularly have no
corresponding group in the new system. For instance, SQL Replication and
Fulltext. There's also no sign of any older technology groups such as ADO
and ASP, so that's anyone who hasn't been able to move to .Net yet due to
business limitations (our central system for instance is COM based and only
supports VB6 for extension programming) out in the cold. I also can't find
any IIS related groups, although I'll admit to only searching for IIS and
Internet Information Services. What a waste of time that was.

Oh, and to top it off, somehow MS have managed to attach one of my Windows
Live accounts to someone else's "Online Community" profile, so I can log in
but I'll be posting with the details of another person. Good job I have
multiple WL accounts (one for work, one personal) or else I'd have to sign
up for yet another one. :(

--
Dan

 |  Next  |  Last
Pages: 1 2
Prev: Blocking with a View
Next: Possible to select a sample rows based on a column value?