Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
609 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
ID: 37834116
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
ID: 37834285
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
ID: 37834372
"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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 39

Expert Comment

by:lcohan
ID: 37834388
--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
ID: 37834542
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
 
LVL 39

Expert Comment

by:lcohan
ID: 37834628
/****** 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
ID: 37834657
@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
ID: 37834708
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
ID: 37834725
never mind just realized why...i'd left out a begin / end pair...
0
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 37834886
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
ID: 37834891
thanks for your help
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Error - Query 6 41
Are triggers slow? 7 11
Comparison query - 4 columns 9 23
too many installs coming along with SQL 2016? 1 16
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 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