Problems with scalar function, and dynamic SQL

[Gaz]

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

[Uri Dimant]

Gaz
Read this stuff
http://www.sommarskog.se/arrays-in-sql.html

Post by Gaz
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...
from table
where
t1.column in (
1,9
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...
-------------------------------------------------------------------------------------------------------------------------------
@SQL nvarchar(max)
'FROM table.column t1 ' +
----------------------------------------------------------------------------------------------------------------------------
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

[Gaz]

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?

[Santhosh]

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

Post by Gaz
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?

[--CELKO--]

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.

[unknown]

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
WHEN UPPER(@parm) = 'YOU
AND col_x =
THEN 1 ELSE 0 END) AS parm_cn
FROM Foobar

Watch out for a NULL in the ELSE clause. Display your parameter s
you know what count you are actually getting back from the query. D
not think in terms of functions and dynamic SQL; use queries and no
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
Ga
Read this stuf
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 thi

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 #tem

"Gaz" wrote:

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

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

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

col = @val1 OR col = @val2 OR col = @val

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, ***@sommarskog.s

Books Online for SQL Server 2005 a
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.msp
Books Online for SQL Server 2000 a
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 base
a set of criteria. What i first tried to do was a case statemen
within the where clause which looked like this..

select @ReturnMe=count(column
from tabl
wher

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



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

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

set @ReturnMe =

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

exec sp_executesql @SQ

RETURN @ReturnM
---------------------------------------------------------------------------------------------------------------------------

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

Only functions and extended stored procedures can be executed fro
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 o
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

[Dan]

Post by unknown
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.

[Gert-Jan Strik]

Post by Dan

Post by unknown
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.

[Dan]

Post by Gert-Jan Strik

Post by Dan

Post by unknown
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

Post by Gert-Jan Strik
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.

Post by Gert-Jan Strik
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.

Post by Gert-Jan Strik
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]

Post by Dan
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. :(

[Erland Sommarskog]

Post by Dan
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 is a forum for replication:
http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/threads

There is a forum for SQL Server Search, which I assume includes
fulltext:
http://social.msdn.microsoft.com/Forums/en-US/sqlsearch/threads
(The last post is from Hilary Cotter, which is a good indication
that fulltext is covered there.)

I can also see these forums in the Newsgroups list from the NNTP
bridge. (Which I don't use much, since it does not produce messages
in text/plain format.)

Post by Dan
There's also no sign of any older technology groups such as ADO

The forum SQL Server Data Access lists ADO as covered. As for ASP
and VB6, I don't what Microsoft has to offer.

Post by Dan
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.

I think you can edit your profile.

[Dan]

Post by Erland Sommarskog

Post by Dan
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.

http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/threads
There is a forum for SQL Server Search, which I assume includes
http://social.msdn.microsoft.com/Forums/en-US/sqlsearch/threads
(The last post is from Hilary Cotter, which is a good indication
that fulltext is covered there.)
I can also see these forums in the Newsgroups list from the NNTP
bridge. (Which I don't use much, since it does not produce messages
in text/plain format.)

Odd, they didn't appear in NNTP Bridge for me on Friday. Looking at the NNTP
Bridge forum I see that it's a flakey piece of software at best.

Post by Erland Sommarskog

Post by Dan
There's also no sign of any older technology groups such as ADO

The forum SQL Server Data Access lists ADO as covered. As for ASP
and VB6, I don't what Microsoft has to offer.

Given that they consider the technology outdated, I'd guess at zip.
Unfortunately for me ASP and VB6 is the bulk of my ongoing application
development and support and my wage earner.

Post by Erland Sommarskog

Post by Dan
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.

I think you can edit your profile.

My work WL ID is really really old (from back even before they were called
.NET Passport ID) - I set it up back when MS needed it for me to get access
to the MS Press Connections site for pre-release and sales info. If I login
on the forum site I have the Create a Profile link at the upper right, but
this takes me to the profile page for DanCr (which is my MSDN profile ID)
but that's for someone else entirely on social.msdn, and the Edit My Profile
link is missing. I haven't found a way to disassociate my WL ID with the
MSDN profile DanCr, because even at the MSDN site where the Create a Profile
link is replaced with DanCr, clicking it just takes me back to the
social.msdn profile page for DanCr which is read-only as it's not my profile
...

Repeated emails to MS have resulted in nothing. As I said, at least I can
use my personal WL ID to access the forums.

[Erland Sommarskog]

Post by Gaz
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...
from table
where
t1.column in (
1,9
8
)

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.

Post by Gaz
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

You cannot use dynamic SQL in functions.

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