?
Solved

Functions in SQL 2005

Posted on 2011-10-13
6
Medium Priority
?
178 Views
Last Modified: 2012-05-12
Hi Experts I am coverting some VBA code to TSQL,

I have a function in VBA that is passed some parameters and then does some transforming and then passes them back to the routine that called the function.

In tsql I want to use temp tables and dont believe this is possible with functions in TSQL plus I want to return more than one value.

If some one could tell me if what im doing is possible, please help!


0
Comment
Question by:MrDavidThorn
  • 4
  • 2
6 Comments
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 36963397
I use a combination of functions and stored procedures to do all kinds of wacky stuff. What exactly do you need to do?
0
 

Author Comment

by:MrDavidThorn
ID: 36964321
i,e In the example below i want the value of Test passed to the routing to return 'Thorn' not 'Dave'
Create procedure spPassValue
AS
BEGIN
DECLARE @TEST NVARCHAR(10)
SET @TEST = 'DAVIID'
EXEC fPasstest @TEST
END



CREATE PROCEDURE fPasstest
(
@TEST as nvarchar(10)
)
AS
BEGIN
SET @TEST = 'THORN'
END

Open in new window

0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 36964820

CREATE FUNCTION fPasstest (@TESTIN nvarchar(10))
RETURNS nvarchar(10)
AS
BEGIN
      Declare @testout nvarchar(10)
      SET @testout = 'THORN';
      return @testout;
END
GO

alter procedure spPassValue
AS
BEGIN
DECLARE @TEST NVARCHAR(10)
SET @TEST = 'DAVIID';
SET @TEST = dbo.fPasstest(@TEST)
print @TEST;
END



exec spPassValue
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 39

Accepted Solution

by:
Aaron Tomosky earned 2000 total points
ID: 36964829
So this will create the function and the proc. The proc sets a value, then sets the value again by calling the function which returns a different value. Then the proc prints out the value so you can see it.
CREATE FUNCTION fPasstest (@TESTIN nvarchar(10))
RETURNS nvarchar(10)
AS
BEGIN
	Declare @testout nvarchar(10)
	SET @testout = 'THORN';
	return @testout;
END
GO

create procedure spPassValue
AS
BEGIN
DECLARE @TEST NVARCHAR(10)
SET @TEST = 'DAVIID';
SET @TEST = dbo.fPasstest(@TEST)
print @TEST;
END



exec spPassValue

Open in new window

0
 

Author Comment

by:MrDavidThorn
ID: 36965182
thanks!, do I have to use a function the problem is that I have to use temp tables that are created in the procedure and then used in the function, if I cant do this then can I use table variables or shall I create 'static' tables and then drop them after use?
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 36965299
If you need something "done" use a proc. I you need something returned, use a function. So you can have a function that calls a proc, the proc creates a global temp table, the function returns that table.
It's getting hard to give concrete answers without real data.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question