Solved

How do you call a stored procedure with a scalar function as one of it's parameters in SQL?

Posted on 2012-04-11
11
600 Views
Last Modified: 2012-04-11
How do you call a stored procedure, in SQL, when you need one of the parameters to be one of the built in SQL scalar functions? What I know doesn't work is this:

exec MyStoredProc 'Param1', ScalarFunction(value)

     OR THIS

exec MyStoredProc 'Param1', dbo.ScalarFunction(value)

and neither of those work with tick marks around the call of the scalar function either.
0
Comment
Question by:mikesExpertExchange
  • 6
  • 5
11 Comments
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
You can't pass in as a parameter directly the result of a SQL Scalar function but you can put it in a variable first then caqll the SP pass in that variable like:

DECLARE @value sysname -- make sure same data type as result of dbo.ScalarFunction(value)

SET @value = SELECT dbo.ScalarFunction(value)

EXEC MyStoredProc @Param1 = @value

--or just

EXEC MyStoredProc @value
0
 
LVL 1

Author Comment

by:mikesExpertExchange
Comment Utility
thank you,...just one last question...can you call a stored proc inline with a select statement? if so, how?
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
"can you call a stored proc inline with a select statement?"

not that I know of but you can call a function inline with a select - or join with a function and almost any stored proc can be written as function.

another option would be to put the select in a WHILE - LOOP (or CURSOR) where you can byild dynamic SQL with the different @values and execute the stmnt below in a loop for each value:

EXEC MyStoredProc @value
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
--Here's an example to join a table with a function
-- Create a function that calculates the percentage of each approved expense against the total
CREATE FUNCTION F_expercent () RETURNS @exp TABLE (expenseid uniqueidentifier,expercent numeric(9,4))
AS
begin
insert into @exp
      SELECT e.ExpenseId, (e.unitprice/er.totalexpenses)*100
      from expense e, expensereport er
      where       e.approvalstatus='A' and
            e.expensereportid is not null and
            e.expensereportid = er.expensereportid and
            er.totalexpenses > 0
RETURN
end

GO

-- Get the percentage of each entry in the expense table calculated by the function
select e.expenseid, F_expercent.expercent
      from expense e join F_expercent () on e.expenseid = F_expercent.expenseid
GO
0
 
LVL 1

Author Comment

by:mikesExpertExchange
Comment Utility
thank you. i'm including my function. i'm new to creating functions and am getting the following error for this function:

"Msg 444, Level 16, State 3, Procedure fn_GetSARViolations, Line 168
Select statements included within a function cannot return data to a client."

How do I fix this?
USE [AimeeDB]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_GetSARViolations]    Script Date: 04/11/2012 14:31:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*
*
* Procedure: Get SAR Violation Code Meanings
* Comments:	this procedure transaltes the individual
  SAR violation codes (given as a characer string
  where each letter represents a different violation)
  into their meanings
* Input:	violation code string (i.e.: ADG)
* Output:	a concatenated string containing the 
			meaing for each letter in the string.
* Creator:	Mike Sterling
* To Test: exec GetSARViolations 'BJL'--, 3 LEN('ADG')
*/

/*
Code	Violation Description
 A		Bank Secrecy Act/Structuring/Money Laundering
 D		Check Kiting
 G		Consumer Loan Fraud
 J		Counterfeit Instrument
 M		Defalcation/Embezzlement
 P		Mortgage Loan Fraud
 T		Terrorist Financing
 B		Bribery/Gratuity
 C		Check Fraud
 E		Commercial Loan Fraud
 H		Counterfeit Check
 F		Computer Intrusion
 I		Counterfeit Credit/Debit Card
 K		Credit Card Fraud
 L		Debit Card Fraud
 N		False Statement
 O		Misuse of Position or Self-Dealing
 Q		Mysterious Disappearance
 R		Wire Transfer Fraud
 U		Identity Theft
 S		Other
 */
 
CREATE FUNCTION [dbo].[fn_GetSARViolations]
(
	@string NVARCHAR(1000)
)

RETURNS NVARCHAR

AS
BEGIN
DECLARE @strLen INT = LEN(@string)
DECLARE @result NVARCHAR(1000) = ''
DECLARE @RSLT NVARCHAR(1000)

WHILE @strLen <> 0
--BEGIN		
	SET @strLen = @strLen - 1
	--SELECT @string AS 'STARTING STRING'
	IF CHARINDEX('A', @string ) > 0
	BEGIN
	  SET @result = @result + 'Bank Secrecy Act-Structuring-Money Laundering / '
	  SET @string = REPLACE(@string, 'A', '')
	END
	ELSE IF CHARINDEX('D', @string ) > 0
	BEGIN
	  SET @result = @result + 'Check Kiting / '
	  SET @string = REPLACE(@string, 'D', '')
	END
	ELSE IF CHARINDEX('G', @string ) > 0
	BEGIN
	  SET @result = @result + 'Consumer Loan Fraud / '
	  SET @string = REPLACE(@string, 'G', '')
	END
	ELSE IF CHARINDEX('J', @string ) > 0
	BEGIN
	  SET @result = @result + 'Counterfeit Instrument / '
	  SET @string = REPLACE(@string, 'J', '')
	END
	ELSE IF CHARINDEX('M', @string ) > 0
	BEGIN
	  SET @result = @result + 'Defalcation-Embezzlement / '
	  SET @string = REPLACE(@string, 'M', '')
	END
	ELSE IF CHARINDEX('P', @string ) > 0
	BEGIN
	  SET @result = @result + 'Mortgage Loan Fraud / '
	  SET @string = REPLACE(@string, 'P', '')
	END
	ELSE IF CHARINDEX('T', @string ) > 0
	BEGIN
	  SET @result = @result + 'Terrorist Financing / '
	  SET @string = REPLACE(@string, 'T', '')
	END
	ELSE IF CHARINDEX('B', @string ) > 0
	BEGIN
	  SET @result = @result + 'Bribery-Gratuity / '
	  SET @string = REPLACE(@string, 'B', '')
	END
	ELSE IF CHARINDEX('C', @string ) > 0
	BEGIN
	  SET @result = @result + 'Check Fraud / '
	  SET @string = REPLACE(@string, 'C', '')
	END
	ELSE IF CHARINDEX('E', @string ) > 0
	BEGIN
	  SET @result = @result + 'Commercial Loan Fraud / '
	  SET @string = REPLACE(@string, 'E', '')
	END
	ELSE IF CHARINDEX('H', @string ) > 0
	BEGIN
	  SET @result = @result + 'Counterfeit Check / '
	  SET @string = REPLACE(@string, 'H', '')
	END
	ELSE IF CHARINDEX('F', @string ) > 0
	BEGIN
	  SET @result = @result + 'Computer Intrusion / '
	  SET @string = REPLACE(@string, 'F', '')
	END
	ELSE IF CHARINDEX('I', @string ) > 0
	BEGIN
	  SET @result = @result + 'Counterfeit Credit-Debit Card / '
	  SET @string = REPLACE(@string, 'I', '')
	END
	ELSE IF CHARINDEX('K', @string ) > 0
	BEGIN
	  SET @result = @result + 'Credit Card Fraud / '
	  SET @string = REPLACE(@string, 'K', '')
	END
	ELSE IF CHARINDEX('L', @string ) > 0
	BEGIN
	  SET @result = @result + 'Debit Card Fraud / '
	  SET @string = REPLACE(@string, 'L', '')
	END
	ELSE IF CHARINDEX('N', @string ) > 0
	BEGIN
	  SET @result = @result + 'False Statement / '
	  SET @string = REPLACE(@string, 'N', '')
	END
	ELSE IF CHARINDEX('O', @string ) > 0
	BEGIN
	  SET @result = @result + 'Misuse of Position or Self-Dealing / '
	  SET @string = REPLACE(@string, 'O', '')
	END
	ELSE IF CHARINDEX('Q', @string ) > 0
	BEGIN
	  SET @result = @result + 'Mysterious Disappearance / '
	  SET @string = REPLACE(@string, 'Q', '')
	END
	ELSE IF CHARINDEX('R', @string ) > 0
	BEGIN
	  SET @result = @result + 'Wire Transfer Fraud / '
	  SET @string = REPLACE(@string, 'R', '')
	END
	ELSE IF CHARINDEX('U', @string ) > 0
	BEGIN
	  SET @result = @result + 'Identity Theft / '
	  SET @string = REPLACE(@string, 'U', '')
	END
	ELSE IF CHARINDEX('S', @string) > 0
	BEGIN
	  SET @result = @result + 'Other / '
	  SET @string = REPLACE(@string, 'S', '')
	END		
--END

	IF @result <> ''
	SET @result = LEFT(@result, LEN(@result) - 1)
	SELECT
	CASE
		WHEN @result <> '' THEN
			 LEFT(@result, LEN(@result) - 0)
		ELSE
			''
	END RESULT	
	
	RETURN @result
END
GO

Open in new window

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 39

Expert Comment

by:lcohan
Comment Utility
/****** Object:  UserDefinedFunction [dbo].[fn_GetSARViolations]    Script Date: 04/11/2012 14:31:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*
*
* Procedure: Get SAR Violation Code Meanings
* Comments:      this procedure transaltes the individual
  SAR violation codes (given as a characer string
  where each letter represents a different violation)
  into their meanings
* Input:      violation code string (i.e.: ADG)
* Output:      a concatenated string containing the
                  meaing for each letter in the string.
* Creator:      Mike Sterling
* To Test: exec GetSARViolations 'BJL'--, 3 LEN('ADG')
*/

/*
Code      Violation Description
 A            Bank Secrecy Act/Structuring/Money Laundering
 D            Check Kiting
 G            Consumer Loan Fraud
 J            Counterfeit Instrument
 M            Defalcation/Embezzlement
 P            Mortgage Loan Fraud
 T            Terrorist Financing
 B            Bribery/Gratuity
 C            Check Fraud
 E            Commercial Loan Fraud
 H            Counterfeit Check
 F            Computer Intrusion
 I            Counterfeit Credit/Debit Card
 K            Credit Card Fraud
 L            Debit Card Fraud
 N            False Statement
 O            Misuse of Position or Self-Dealing
 Q            Mysterious Disappearance
 R            Wire Transfer Fraud
 U            Identity Theft
 S            Other
 */
 
CREATE FUNCTION [dbo].[fn_GetSARViolations]
(
      @string NVARCHAR(1000)
)

RETURNS NVARCHAR

AS
BEGIN
DECLARE @strLen INT = LEN(@string)
DECLARE @result NVARCHAR(1000)
DECLARE @RSLT NVARCHAR(1000)
SET @result = ''

WHILE @strLen <> 0
--BEGIN            
      SET @strLen = @strLen - 1
      --SELECT @string AS 'STARTING STRING'
      IF CHARINDEX('A', @string ) > 0
      BEGIN
        SET @result = @result + 'Bank Secrecy Act-Structuring-Money Laundering / '
        SET @string = REPLACE(@string, 'A', '')
      END
      ELSE IF CHARINDEX('D', @string ) > 0
      BEGIN
        SET @result = @result + 'Check Kiting / '
        SET @string = REPLACE(@string, 'D', '')
      END
      ELSE IF CHARINDEX('G', @string ) > 0
      BEGIN
        SET @result = @result + 'Consumer Loan Fraud / '
        SET @string = REPLACE(@string, 'G', '')
      END
      ELSE IF CHARINDEX('J', @string ) > 0
      BEGIN
        SET @result = @result + 'Counterfeit Instrument / '
        SET @string = REPLACE(@string, 'J', '')
      END
      ELSE IF CHARINDEX('M', @string ) > 0
      BEGIN
        SET @result = @result + 'Defalcation-Embezzlement / '
        SET @string = REPLACE(@string, 'M', '')
      END
      ELSE IF CHARINDEX('P', @string ) > 0
      BEGIN
        SET @result = @result + 'Mortgage Loan Fraud / '
        SET @string = REPLACE(@string, 'P', '')
      END
      ELSE IF CHARINDEX('T', @string ) > 0
      BEGIN
        SET @result = @result + 'Terrorist Financing / '
        SET @string = REPLACE(@string, 'T', '')
      END
      ELSE IF CHARINDEX('B', @string ) > 0
      BEGIN
        SET @result = @result + 'Bribery-Gratuity / '
        SET @string = REPLACE(@string, 'B', '')
      END
      ELSE IF CHARINDEX('C', @string ) > 0
      BEGIN
        SET @result = @result + 'Check Fraud / '
        SET @string = REPLACE(@string, 'C', '')
      END
      ELSE IF CHARINDEX('E', @string ) > 0
      BEGIN
        SET @result = @result + 'Commercial Loan Fraud / '
        SET @string = REPLACE(@string, 'E', '')
      END
      ELSE IF CHARINDEX('H', @string ) > 0
      BEGIN
        SET @result = @result + 'Counterfeit Check / '
        SET @string = REPLACE(@string, 'H', '')
      END
      ELSE IF CHARINDEX('F', @string ) > 0
      BEGIN
        SET @result = @result + 'Computer Intrusion / '
        SET @string = REPLACE(@string, 'F', '')
      END
      ELSE IF CHARINDEX('I', @string ) > 0
      BEGIN
        SET @result = @result + 'Counterfeit Credit-Debit Card / '
        SET @string = REPLACE(@string, 'I', '')
      END
      ELSE IF CHARINDEX('K', @string ) > 0
      BEGIN
        SET @result = @result + 'Credit Card Fraud / '
        SET @string = REPLACE(@string, 'K', '')
      END
      ELSE IF CHARINDEX('L', @string ) > 0
      BEGIN
        SET @result = @result + 'Debit Card Fraud / '
        SET @string = REPLACE(@string, 'L', '')
      END
      ELSE IF CHARINDEX('N', @string ) > 0
      BEGIN
        SET @result = @result + 'False Statement / '
        SET @string = REPLACE(@string, 'N', '')
      END
      ELSE IF CHARINDEX('O', @string ) > 0
      BEGIN
        SET @result = @result + 'Misuse of Position or Self-Dealing / '
        SET @string = REPLACE(@string, 'O', '')
      END
      ELSE IF CHARINDEX('Q', @string ) > 0
      BEGIN
        SET @result = @result + 'Mysterious Disappearance / '
        SET @string = REPLACE(@string, 'Q', '')
      END
      ELSE IF CHARINDEX('R', @string ) > 0
      BEGIN
        SET @result = @result + 'Wire Transfer Fraud / '
        SET @string = REPLACE(@string, 'R', '')
      END
      ELSE IF CHARINDEX('U', @string ) > 0
      BEGIN
        SET @result = @result + 'Identity Theft / '
        SET @string = REPLACE(@string, 'U', '')
      END
      ELSE IF CHARINDEX('S', @string) > 0
      BEGIN
        SET @result = @result + 'Other / '
        SET @string = REPLACE(@string, 'S', '')
      END            
--END

      IF @result <> ''
      SET @result = LEFT(@result, LEN(@result) - 1)
      SELECT @result = CASE WHEN @result <> ''
                                                THEN LEFT(@result, LEN(@result) - 0)
                                       ELSE '' END       
      
      RETURN @result
END
GO
0
 
LVL 1

Author Comment

by:mikesExpertExchange
Comment Utility
@lcohan: when you ran this did it work? when i ran it, i got the same error message.
0
 
LVL 1

Author Comment

by:mikesExpertExchange
Comment Utility
i actually got it to run but it's not behaving as expected. when i run it against a single value it returns the appropriate value. but any time there is more than 1 character in the string i put in, i only get the result as if there was only one character in the string. it doesn't seem to be concatenating at all. any ideas?
0
 
LVL 1

Author Comment

by:mikesExpertExchange
Comment Utility
never mind just realized why...i'd left out a begin / end pair...
0
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
Comment Utility
even with that it worked for me like:

declare @str varchar(1000)
set @str = dbo.fn_GetSARViolations('BJL')
print (@str)


--or just

select dbo.fn_GetSARViolations('BJL')
0
 
LVL 1

Author Closing Comment

by:mikesExpertExchange
Comment Utility
thanks for your help
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now