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 ( ) 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 DeleteThis @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-fc...6855293 >> Stay informed about: Problems with scalar function, and dynamic SQL 
FAQ
Search
Profile
Private Messages
Log in
