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
611 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
[X]
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
  • 6
  • 5
11 Comments
 
LVL 40

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 40

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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 40

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 40

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 40

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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Suggested Solutions

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 …
In this article I will describe the Copy Database Wizard 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.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

763 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