Script for Run in Function

Asked

Viewed 655 times

2

I’m performing a conversion on some comic objects where they are with a fixed name of the database.

Example: 

select * from banco.dbo.tabela ...

In this case, we are changing this banco fixed, by a variable that contains the name in each base. In the procedures we perform the conversion of the sentences into Scripts, concatenating the variable.

Example:

 select * from '+@banco+'.dbo.tabela..

And running with sp_executesql.

The problem is that in Function’s I cannot execute this command. Could you help me with a possible solution to the case.

Ex:

CREATE FUNCTION mat.Retorna   
(        
    @valor INT         
   ,@valor2  INT      
   ,@valor3 INT             
) RETURNS INT    
AS    
BEGIN      

  DECLARE @valor4 INT    

  SELECT @valor4 = E001.coddot         
    FROM BANCO.dbo.tabela1 tb1      
    JOIN BANCO.dbo.tabela2 tb2 ON tb2.campo = tb1.campo         
    JOIN BANCO.dbo.tabela3 tb3  ON tb2.campo = tb3.campo          
    JOIN BANCO.dbo.tabela4 tb4  ON tb4.campo = tb3.campo      
                                   AND tb4.campo = @valor3      
   WHERE tb2.campo    < 50000        
     AND tb3.campo IS NULL        
     AND tb3.campo = @valor2      
     AND tb3.campo    = @valor      

  RETURN @valor4;    

END
sql sql-server function

  • How about deleting the seat prefix completely and using a USE database; before performing the procedures?

  • But it is that in the case are separate bases, where we perform Join for checks or information search.

  • What if only the Procedure call you put the name of the bank? Example: Use bancoA; Call BancoB.dbo.procedure

  • I am not running a Procedure. I am servicing a Function. In Function there is a Select that returns a value, but in select there is.

  • I put an example. Where the BANK can not be a fixed but dynamical Noma. I have a Function that returns the name of the local base, where I can assign a variable.

  • You can create a process that makes the dynamic query and insert the results into a specific table, and use the function to select from this table (take a look in this question from Ask SQL Server Central).

Show 1 more comment

1 answer

0

In SQL Server it is not possible to run stored procedures within Functions, but you can add conditions to perform different SELECT instructions for each database.

Follow a T-SQL script for you to adapt to your need:

CREATE FUNCTION dbo.Retorna
(
    @valor      INT
   ,@valor2     INT
   ,@valor3     INT
) RETURNS INT
AS
BEGIN
DECLARE @valor4 INT

    IF @VALOR = 1
    BEGIN
        SET @valor4 =  1
    END

    IF @VALOR = 2
    BEGIN
        SET @valor4 =  2
    END

  RETURN @valor4;    

END
GO

SELECT dbo.Retorna(1, 2, 3)
GO

For more information see:

https://msdn.microsoft.com/pt-br/library/ms191320.aspx

https://msdn.microsoft.com/pt-br/library/ms186755(v=sql.110). aspx

Browser other questions tagged sql sql-server function

You are not signed in. Login or sign up in order to post.