selecting from function

Hello,
I am trying to select from function.  Here below are 3 methods I used. It works for 3) but not for 1) and 2).
The function has the parameters @MyId int and @MyDate datetime.
Can you please help? I have been using this to call a function with just @MyId but when I add @MyDate it does not work.

1) Shows columns from the function but does not return any rows.
DECLARE @query nvarchar(500)
DECLARE @MyId int=34253
DECLARE @MyDate date='2015-09-15'
SET @query = 'SELECT * FROM fnFunction1('+Cast(@MyId as nvarchar(16))+','+CAST(@MyDate as nvarchar(16))+') f'
exec sp_executesql @query;

2) Shows columns from the function but does not return any rows.
DECLARE @query2 nvarchar(500)
SET @query2 = 'SELECT * FROM fnFunction1(34253,2015-09-15)'
exec sp_executesql @query2;

3) Returns rows, i.e. works.
SELECT * FROM fnFunction1(34253,'2015-09-15')
johnson1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
you need quotes around your date and because you are already in quotes, you need to double them:
DECLARE @query2 nvarchar(500)
 SET @query2 = 'SELECT * FROM fnFunction1(34253,''2015-09-15'')'
 exec sp_executesql @query2;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnson1Author Commented:
Thank you,
PortletPaulEE Topic AdvisorCommented:
why place everything into a string? (you don't have to use exec sp_executesql)
just pass the declared variables as parameters

DECLARE @MyId int = 34253
DECLARE @MyDate date = '2015-09-15'

SELECT * FROM fnFunction1(@MyId,@MyDate)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.