UDF - User Defined Flop - 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

SQL Server 2008 Forums

Transact-SQL (2008)

UDF - User Defined Flop

Author

Topic

markiandean
Starting Member

6 Posts

Posted - 2011-05-02 : 22:10:07

Hi All!

First off, you know you're in trouble when you get the error 'incorrect syntax near 'BEGIN' !!!

I have written a little pivot code to convert rows into columns, but I cannot, for the life of me and no matter how many combinations of begin return end and go, write this code as a UDF. Can someone please help???

Set dateformat dmy

DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)
SELECT @listCol = STUFF(( SELECT DISTINCT

'],[' + yahoo_ticker

FROM CARNA_SHARE_PRICES_AND_VOLUMES.dbo.YAHOO_OHLC_UNADJUSTED_TRADING_DAYS_ONLY

Where YAHOO_TICKER IN (select distinct yahoo_ticker
from CARNA_TEST_DB.dbo.STOCKS_TO_OPTIMISE)
ORDER BY '],[' + yahoo_ticker
FOR XML PATH('')

), 1, 2, '') + ']'
SET @query =
'SELECT * FROM

(SELECT DATE, YAHOO_TICKER, PX_CLOSE

FROM CARNA_SHARE_PRICES_AND_VOLUMES.dbo.YAHOO_OHLC_UNADJUSTED_TRADING_DAYS_ONLY) src

PIVOT (SUM(PX_CLOSE) FOR Yahoo_ticker

IN ('+@listCol+')) AS pvt'
EXECUTE (@query)

Thanks ever so much,

Mark

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2011-05-02 : 22:23:17

does the script work by itself outside the UDF?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-02 : 22:25:19

you can't use dynamic SQL in a UDF. Write it as a stored procedure

KH
[spoiler]Time is always against us[/spoiler]

markiandean
Starting Member

6 Posts

Posted - 2011-05-03 : 05:25:33

USP route worked a treat, thanks very much guys!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources