February 23, 2015 at 9:34 am
I have created a store procedure, but the requirement is function because by using this function we need to add columns in a table with SSIS.
I have tried to create function, but the error I am facing is select statement can not return data.
CREATE PROCEDURE SP_STAT_CURR
(
@I_NET_AMOUNT NUMERIC(10,3),
@I_DOCUMENT_CURR VARCHAR(3),
@I_TARGET_CURR VARCHAR(3)
)
AS
BEGIN
IF (@I_TARGET_CURR = @I_DOCUMENT_CURR)
BEGIN
SELECT @I_NET_AMOUNT AS O_TARGET_CURR
END
ELSE
IF (@I_DOCUMENT_CURR = 'USD' AND @I_TARGET_CURR != 'USD')
BEGIN
SELECT ((@I_NET_AMOUNT) / (RATE)) AS O_TARGET_CURR
FROM CURRENCY
WHERE CURRENCY = @I_TARGET_CURR AND RTYPE = 'M'
END
ELSE
IF(@I_DOCUMENT_CURR <> 'USD' AND @I_TARGET_CURR = 'USD')
BEGIN
SELECT (@I_NET_AMOUNT * RATE) AS O_TARGET_CURR
FROM CURRENCY
WHERE CURRENCY = @I_DOCUMENT_CURR AND RTYPE = 'M'
END
ELSE
BEGIN
SELECT ((SELECT (@I_NET_AMOUNT * RATE) AS O_TARGET_CURR
FROM CURRENCY WHERE CURRENCY = @I_DOCUMENT_CURR AND RTYPE = 'M')/(RATE)) FROM CURRENCY
WHERE CURRENCY = @I_TARGET_CURR AND RTYPE = 'M'
END
END
February 23, 2015 at 9:54 am
A Scalar UDF is one of the worst things you can do in SQL Server. Best BY FAR is to just inline the logic and use it directly in your set-based operations. Next best would be an Inline Table Valued Function, likely with a CROSS APPLY operation.
Scalar functions are created with a variable declared that is populated by the code inside the UDF.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 23, 2015 at 10:07 am
As Kevin said, user defined scalar functions and multi-line functions are bad for performance, but inline table-valued functions don't suffer the same problem.
This code is untested but might give you an idea on what you could do.
CREATE FUNCTION FN_STAT_CURR
(
@I_NET_AMOUNT NUMERIC(10,3),
@I_DOCUMENT_CURR VARCHAR(3),
@I_TARGET_CURR VARCHAR(3)
) RETURNS TABLE AS
RETURN
SELECT CASE WHEN @I_DOCUMENT_CURR = 'USD' AND @I_TARGET_CURR <> 'USD'
THEN @I_NET_AMOUNT / MAX(CASE WHEN CURRENCY = @I_TARGET_CURR THEN RATE END)
WHEN @I_DOCUMENT_CURR <> 'USD' AND @I_TARGET_CURR = 'USD'
THEN @I_NET_AMOUNT * MAX(CASE WHEN CURRENCY = @I_DOCUMENT_CURR THEN RATE END)
ELSE
(@I_NET_AMOUNT * MAX(CASE WHEN CURRENCY = @I_DOCUMENT_CURR THEN RATE END))
/ MAX(CASE WHEN CURRENCY = @I_TARGET_CURR THEN RATE END)
END AS O_TARGET_CURR
FROM CURRENCY
WHERE CURRENCY IN (@I_DOCUMENT_CURR, @I_TARGET_CURR)
AND RTYPE = 'M'
AND @I_DOCUMENT_CURR <> @I_TARGET_CURR
UNION ALL
SELECT @I_NET_AMOUNT AS O_TARGET_CURR ;
February 23, 2015 at 11:00 am
Why did this need a new thread?
Maybe instead of doing this a bit at a time, with a new thread each time, in which nobody has anything real to work with in any of the threads, you could take some time and explain in detail the entire picture of what you are trying to accomplish.
So start with not only describing for us but providing a sample of the information you have.
So if you have an exchange rate table, give us a CREATE TABLE statement that generates it, so we can work with your field names and test our code. Then provide an INSERT statement that will give us some sample rate data we can test.
Then explain to us how you want to use what you are building. In the last thread you made it sound like something that might be called from a web front end. Someone provides one amount and two currencies, and gets a converted amount. Now it sounds like you want to be able to convert a table full of amounts. So do you want to apply the same SOURCE and DESTINATION currencies to every row, or a different one for each row (specified by columns).
You say you want to do this for SSIS. How about a description of what the SSIS package does (where it gets information, how it transforms it, where it puts it), and why it was selected as the tool for a job.
That way, instead of getting untested sample code where none of us really know if it best accomplishes your goals (since your goals are unknown), we can advise on an overall strategy and provide a detailed sample of what that solution would look like. It also means that instead of helping you patch between different choices you already made, but which may not be ideal, we can advise on an efficient way to do the whole thing.
February 23, 2015 at 11:58 pm
Hi Nevyn,
I am glad for your reply, I would happily to share the scope the project but at the moment the company haven't explained to me itself, all I need to do is whenever they come up with some requirements then I need to complete the requirement. This SP or Function is used for generic purpose for their future usage it seems.I have already the script for create table for exchange_rate table. I am New to SQL Servercentral, I haven't continued this thread because I already Mark as Solution, So I am unsure whether people can see this new thread or not.
Now I need to add extra columns by using this function. I already added few columns by using derived column with are straight forward Logics. If possible can you suggest me how can I add new columns in SSIS which required this user_defined_function logic to use. I can't use Derived column, but I need to add the column with calculations.
February 24, 2015 at 12:03 am
Hi Luiz,
I am really thankful for your conversion script, can you suggest me, based on this function logic, I need to add extra columns in SSIS, could you tell me which transformation or task will do. I need to add few more columns with some function logic to be lookup and obtain new column.
February 24, 2015 at 8:03 am
Since nobody else mentioned it I feel compelled to suggest that you discuss about changing the naming convention for stored procedures. The sp_ prefix is reserved for system procedures. When executing a procedure if you don't specify the schema and there is a naming conflict the system procedure will take precedence over a user defined procedure. I am not a fan of prefixes in the first place but if you have to use them maybe something like usp_ or something else.
_______________________________________________________________
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/
February 24, 2015 at 8:09 am
Sangeeth878787 (2/23/2015)
Hi Nevyn,I am glad for your reply, I would happily to share the scope the project but at the moment the company haven't explained to me itself, all I need to do is whenever they come up with some requirements then I need to complete the requirement. This SP or Function is used for generic purpose for their future usage it seems.I have already the script for create table for exchange_rate table. I am New to SQL Servercentral, I haven't continued this thread because I already Mark as Solution, So I am unsure whether people can see this new thread or not.
Now I need to add extra columns by using this function. I already added few columns by using derived column with are straight forward Logics. If possible can you suggest me how can I add new columns in SSIS which required this user_defined_function logic to use. I can't use Derived column, but I need to add the column with calculations.
Often people who help on a thread get an email subscription to it that tells them when a post is added. So even if you have marked it as solved, if you post an additional question on it, the people who helped you find an answer are likely to see it.
This is an article on forum etiquette[/url]. It is called 'etiquette' but it is really much more than a manners thing. The people who follow those guidelines tend to get the most responses, the fastest answers, and the best and most tested solutions to their problems. It lets people trying to hate you create a simulation of your problem and then write their solution.
Did you post the exchange rate table on here or the other thread? I don't recall seeing it.
I don't really know what "generic purpose for their future usage" means. If they gave you a requirement for the first one, it should have included an understanding of how they were likely to call the procedure/function you created. (For example, from a web front end, from SSIS, from management studio in a query).
Likewise, now you are telling us that you want a function that you can use to add columns for an SSIS package, but you have not described the problem. Does this package copy from one database/server to another or just between tables in the same database? Is the exchange rate table on the source system or the destination system? How many rows are generally copied over? Is the package updating rows or just inserting them? Do you have any sort of staging table in between?
All of those pieces of information will impact what the best way is to approach your problem. By not sharing that information you get very narrow answers on here that may not be the best overall approach and trying to tie them together.
Example: It is as if you came on here asking us for directions on how to build a raft. You might get instructions on how to do it without people realizing that you really just wanted to cross a river, and there was a bridge you didn't know about a hundred yards upstream.
February 24, 2015 at 9:30 am
Sangeeth878787 (2/24/2015)
Hi Luiz,I am really thankful for your conversion script, can you suggest me, based on this function logic, I need to add extra columns in SSIS, could you tell me which transformation or task will do. I need to add few more columns with some function logic to be lookup and obtain new column.
T-SQL functions work only on T-SQL code. If you want to use it in SSIS, you need to add it to the source if it's a query or transform it from a table into a query that uses the function. To use table valued functions, you need to use the APPLY operator.
We don't have the full picture and without it, we can only guess and possibly give bad advice.
February 24, 2015 at 9:30 am
Hi Luiz,
I have two tables[transactions table and Header table] and merging them make one table [Grand Table] , along with that I need to add few more column with few logic's. For one column to add I need this Function to use, the inputs of this function are columns from Table Transactions and Header.
I have stuck here to add these columns because the inputs for this functions are columns from other tables. Can you give any ideas.
Many Thanks
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply