can't figure out the syntax for sp_executesql to run a function

can't figure out the syntax for sp_executesql to run a function

Post by Rick » Sat, 09 Aug 2003 17:38:19



I can't figure out the syntax to execute sp_executesql
command when in the from clause I have a case statement
selecting the appropriate udf instead of a table name.

Following runs fine:









Then 'fn_PIGetHourlydataForPlant' When 'Daily'
Then 'fn_PIGetDailyDataForPlant' End + '(' +  



('MetNESecAT09')+ ' ) as AT09 '



But I need to use above select statement as literal

I get Incorrect syntax near '+' when I do:

Exec sp_executesql N'Select at09.datetime,isnull

Then 'fn_PIGetHourlyDataForPlant' When 'Daily'
Then 'fn_PIGetDailyDataForPlant' end+



I also tried:
Exec sp_executesql N'Select at09.datetime,isnull

Then fn_PIGetHourlyDataForPlant When Daily Then



Query parses successfully but when I run it I get
Incorrect syntax near the keyword 'case'

What am I doing wrong??

Thanks for helping out as always....

Ricky

 
 
 

can't figure out the syntax for sp_executesql to run a function

Post by Erland Sommarsko » Mon, 11 Aug 2003 00:23:34



> Exec sp_executesql N'Select at09.datetime,isnull

> Then 'fn_PIGetHourlyDataForPlant' When 'Daily'
> Then 'fn_PIGetDailyDataForPlant' end+




You cannot pass expressions as parameters to stored procedures.
You must build the SQL statement in a variable.

> But I need to use above select statement as literal


Excuse a nosy question, but why do you "need" do this?

--

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

 
 
 

can't figure out the syntax for sp_executesql to run a function

Post by Art Ringl » Tue, 12 Aug 2003 19:10:36


/**************************************************************************
        Try placing additional single quotes
        in your Case to define the results, remeber
        this result is going to be passed BACK into
        your Dynamic Statement
**************************************************************************/











                        as [TK # 09] from '
                        +

                                        When 'Daily'    Then '''fn_PIGetDailyDataForPlant'''  <=== ***
                        End
                        +'('



                        '+dbo.fn_piquotestring('MetNESecAT09')+')as AT09'




/**************************************************************************
        as a rule of thumb I always attempt to PRINT a dynamic
        SQL Statement then compile the results in a Query analyzer,
        this will help you find the holes in your syntaxt
**************************************************************************/

--Example derived from Books on Line SP_ExecuteSQL







                        +                      


                        end
                        +'      
                        FROM    pubs.dbo.employee



GO
/**     Results
        Y-L77953M       Yoshi                   Latimer 12      32      Manager
**/






                        +                      


                        end
                        +'      
                        FROM    pubs.dbo.employee



/**     Results
        PMA42628M       Paolo           M       Accorti 13      35      technician
        H-B39728F       Helen                   Bennett 12      35      technician
        ENL44273F       Elizabeth       N       Lincoln 14      35      technician
**/

/**************************************************************************
        Testing of SP
**************************************************************************/


AS
BEGIN  




                        +                      


                        end
                        +'      
                        FROM    pubs.dbo.employee


END
-- DROP PROCEDURE TEST001

EXEC TEST001 32         -- 35

/**     --32
Y-L77953M       Yoshi                   Latimer 12      32      Manager
**/
/**     --35
PMA42628M       Paolo           M       Accorti 13      35      technician
H-B39728F       Helen                   Bennett 12      35      technician
ENL44273F       Elizabeth       N       Lincoln 14      35      technician
**/

 
 
 

1. Basic Command function but can't figure out

Hi

I am using VB 6,   I want to ask about the Default property in Command
button,  the one that when you press Enter, it will be access ....

There is a default property , you can chooose to turn it FALSE OR TRUE ,
but no matter what I choose,  the command button I want to be default won't
work .   So  I think it's a bug in VB 6  -  can anyone help ?

Thanks

2. DBGrids and input constraints

3. if you will promise Allahdad's swamp against cans, it will angrily depart the unit

4. Data extract to text file?

5. SQL Syntax Help - Incorrect syntax near '%'.

6. linked server security

7. Time Outs When running a query

8. Font has changed error message

9. time-outs or similar function

10. Time-outs that didn't happen under NT4sp6

11. the sp_executesql syntax

12. sp_executesql syntax error

13. syntax for sp_executesql from ADO