Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 640
  • Last Modified:

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

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
Michael Sterling
Asked:
Michael Sterling
  • 6
  • 5
1 Solution
 
lcohanDatabase AnalystCommented:
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
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
thank you,...just one last question...can you call a stored proc inline with a select statement? if so, how?
0
 
lcohanDatabase AnalystCommented:
"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
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.

 
lcohanDatabase AnalystCommented:
--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
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
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
 
lcohanDatabase AnalystCommented:
/****** 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
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
@lcohan: when you ran this did it work? when i ran it, i got the same error message.
0
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
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
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
never mind just realized why...i'd left out a begin / end pair...
0
 
lcohanDatabase AnalystCommented:
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
 
Michael SterlingWeb Applications DeveloperAuthor Commented:
thanks for your help
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now