Script to Execute in Function

Question

Script to Execute in Function

Navigation

#1 by (0 votes)

2

I'm doing a conversion on some bd objects where they have a fixed database name.

Example:

select * from banco.dbo.tabela ...

In this case, we are exchanging this banco fixed, with a variable that contains the name in each database. In the procedures we perform the conversion of the sentences in Scripts, concatenating the variable.

Example:

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

And running with sp_executesql .

The problem is that in Function's I can not execute this command. They could 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

asked by anonymous 18.06.2015 / 15:07

1 answer

Using Firebird database from one PC to another How to automate the deploy process? [closed]

score 0 · Answer 1

In SQL Server it is not possible to execute stored procedures inside Functions, but you can add conditions to execute different SELECT statements for each database.

Follow a T-SQL script to suit your needs:

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:

link