johnson1
asked on
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(@My
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-
exec sp_executesql @query2;
3) Returns rows, i.e. works.
SELECT * FROM fnFunction1(34253,'2015-09
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(@My
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-
exec sp_executesql @query2;
3) Returns rows, i.e. works.
SELECT * FROM fnFunction1(34253,'2015-09
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
just pass the declared variables as parameters
DECLARE @MyId int = 34253
DECLARE @MyDate date = '2015-09-15'
SELECT * FROM fnFunction1(@MyId,@MyDate)
ASKER