Link to home
Start Free TrialLog in
Avatar of Michael Sterling
Michael SterlingFlag for United States of America

asked on

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.
Avatar of lcohan
lcohan
Flag of Canada image
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
Avatar of Michael Sterling
Michael Sterling
Flag of United States of America image

ASKER

thank you,...just one last question...can you call a stored proc inline with a select statement? if so, how?
Avatar of lcohan
lcohan
Flag of Canada image
"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
Avatar of lcohan
lcohan
Flag of Canada image
--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
Avatar of Michael Sterling
Michael Sterling
Flag of United States of America image

ASKER

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

Avatar of lcohan
lcohan
Flag of Canada image
/****** 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
Avatar of Michael Sterling
Michael Sterling
Flag of United States of America image

ASKER

@lcohan: when you ran this did it work? when i ran it, i got the same error message.
Avatar of Michael Sterling
Michael Sterling
Flag of United States of America image

ASKER

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?
Avatar of Michael Sterling
Michael Sterling
Flag of United States of America image

ASKER

never mind just realized why...i'd left out a begin / end pair...
ASKER CERTIFIED SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Michael Sterling
Michael Sterling
Flag of United States of America image

ASKER

thanks for your help