October 24, 2013 at 4:18 am
Hi ,
I have a function which is returning the null, can some provide your inputs to get this sorted
Create Function sp_BD_Daily
(
@TableName varchar(50)
)
Returns Datetime
AS
BEGIN
Declare @sqlquery varchar(100)
Declare @DateReturn Datetime
SET @sqlquery='select @DateReturn = [BusinessDate] From [QRM_DataStore].[Daily].'+@TableName
Return @DateReturn
End
October 24, 2013 at 5:51 am
Did any one has inputs on this ?
October 24, 2013 at 8:30 am
You're not assigning any value to your @DateReturn variable. You just assign the value to @sqlquery.
Why are you doing this? You might want to keep away from scalar functions.
October 24, 2013 at 8:50 am
Luis is right about scalar functions. In addition, you've missed out the bit where you execute the string that you've built. However, it still won't work, since:
(a) Your string only gets a value of @DateReturn; it doesn't return it to the calling context
(b) You can't use dynamic SQL in a function definition.
You might be better off with a stored procedure (which the name of your function suggests it may originally have been) instead.
John
October 24, 2013 at 10:04 am
I wanted to return the business date from the table which i am passing as parameter. Let me know how to correct it ?
October 24, 2013 at 10:47 am
Suresh Babu Palla (10/24/2013)
I wanted to return the business date from the table which i am passing as parameter. Let me know how to correct it ?
Well to "correct" this you have to use a stored procedure instead of a function. You have to execute dynamic sql because you are passing in the table and you can't use dynamic sql in a function. This is a sign that possibly the real issue is the table structure. Why do you need a number of different tables all holding the same type of information? I am thinking there may be sort of roll your own partitioning going on here.
If you wrote your proc like this it should work.
Create procedure sp_BD_Daily
(
@TableName varchar(50)
)
AS
BEGIN
Declare @sqlquery nvarchar(200) = 'select [BusinessDate] From [QRM_DataStore].[Daily].'+@TableName
EXEC sp_executesql @sqlquery
end
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 24, 2013 at 10:56 am
Thanks sean, I have given the stored proc to the business, but they are specifically looking for a function instead of stored proc. So Just wanted to know if I can implement the same logic in function. I am aware that i cant use the dynamic sql efficiently with the function, still keen to know if there is any way to do that.
October 24, 2013 at 11:00 am
Suresh Babu Palla (10/24/2013)
Thanks sean, I have given the stored proc to the business, but they are specifically looking for a function instead of stored proc. So Just wanted to know if I can implement the same logic in function. I am aware that i cant use the dynamic sql efficiently with the function, still keen to know if there is any way to do that.
Nope. You have to dynamically state which table the query is selecting from. There is no way to do that without dynamic sql. Why does business want to cripple performance by demanding that you use a scalar function?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 24, 2013 at 11:25 am
It is possible to make a big, fat, ugly function with a CASE of selects from all tables in your database. I've done it in a pinch and it actually works fast enough for one-off imports.
drop Function fn_BD_Daily
go
Create Function fn_BD_Daily
(
@TableName varchar(50)
)
Returns Datetime
AS
BEGIN
Declare @sqlquery varchar(100)
Declare @DateReturn Datetime
SET @DateReturn =
CASE
WHEN @TableName = 'BillingCodes' THEN (SELECT MAX(CREATEDDATE) FROM BillingCodes)
WHEN @TableName = 'BillingCycles' THEN (SELECT MAX(CREATEDDATE) FROM BillingCycles)
END
Return @DateReturn
End
go
select dbo.fn_BD_Daily( 'BillingCycles')
October 24, 2013 at 12:51 pm
Bill given that the OP state business is asking for this I suspect this is not a one time situation. They are looking for something to use repeatedly. The excellent case expression you posted will work just fine for a one off type of thing as you stated. However, it is a nightmare to maintain this in a system where there are obviously a lot of very similar tables. This would have to modified every time a new table was added to the system that would be needed by this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 25, 2013 at 2:31 am
Bil, Thanks for your inputs. But the query you sent is static, the case statement has only two comparisions, I have more than 20+ tables that I am going to use this function for to fetch the businessdate, writing 20+ case statements just to return a businessdate doesn't make sense to me.
October 25, 2013 at 2:50 am
You really haven't given us much to go on here, just a series of terse requests for help. For example, do your tables contain only one row? If not, the query [font="Courier New"]SELECT @DateReturn = [BusinessDate] From [QRM_DataStore].[Daily].<TableName> [/font]will fail if you don't add a WHERE clause. You also haven't told us whether your 20 or so tables are the same 20 or so tables every day, or whether they change. Depending on the answer to that, this may work for you, and it doesn't need dynamic SQL:
WITH AllTables (TableName, <Columns you need>) AS (
SELECT 'Table01', <Columns you need> FROM QRM_DataStore.Daily.Table01 UNION ALL
SELECT 'Table02', <Columns you need> FROM QRM_DataStore.Daily.Table02 UNION ALL
...
SELECT 'Table19', <Columns you need> FROM QRM_DataStore.Daily.Table19 UNION ALL
SELECT 'Table20', <Columns you need> FROM QRM_DataStore.Daily.Table20 UNION ALL
)
SELECT @DateReturn = BusinessDate
FROM AllTables
WHERE TableName = @TableName
AND <whatever condition gives you a single row>
John
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply