CODEWITHSUNDEEP
sql-serverstored-procedures
Majid Hojati
Majid Hojati

Reputation: 1790

SQL Server, using functions in stored procedure

I want to join two different tables from two separate databases in SQL server, I can do it by adding database names manually but I want to get table names from another table. This is what I did.

I created a function which returns database names. This is it.

ALTER FUNCTION [dbo].[GetDatabaseName] 
    (@name Nvarchar(50))
RETURNS Varchar(Max) 
AS
BEGIN
    DECLARE @Database_name Varchar(Max)

    SET @Database_name= (SELECT value FROM dbo.setting WHERE name = @name)

    RETURN @Database_name
END

It works fine and returns sh94 which is another database name, I want to create a stored procedure to return the result of the join.

This is it:

CREATE PROCEDURE [dbo].[GParcel_shahrsazi]
    @SourceDatabase INT
AS
BEGIN
    DECLARE @DbName varchar(MAx) 
    SET NOCOUNT ON;

    IF @SourceDatabase = 1
       SET @DbName = CAST(dbo.GetDatabaseName('ShDB') as VARCHAR)
    ELSE
       SET @DbName = dbo.GetDatabaseName('NoDB') 
END

SET @DbName = CAST(dbo.GetDatabaseName('ShDB') as VARCHAR)

SELECT * 
FROM dbo.Gparcel AS p 
INNER JOIN  @DbName.dbo.GISMappingAttributeTable AS att ON p.FK_BlockNo = att.FK_BlockNo 
                                                        AND p.FK_LandNo = FK_LandNo

END
GO

where GISMappingAttributeTable is a view name in another database.

This throws an error

Msg 102, Level 15, State 1, Procedure GParcel_shahrsazi, Line 25
Incorrect syntax near '.'.

at this line

 SELECT * 
 FROM dbo.Gparcel as p 
 INNER JOIN  @DbName.dbo.GISMappingAttributeTable as att ON

I can easily run 

 SELECT * 
 FROM dbo.Gparcel as p 
 INNER JOIN  sh94.dbo.GISMappingAttributeTable as att ON p.FK_BlockNo =att.FK_BlockNo And p.FK_LandNo=att.FK_LandNo

With no problem but when I try to add it to a stored procedure it gives me that error. can someone helps me figure out the problem?

Thank you

Upvotes: 0

Views: 178

Answers (1)

Vivek Khandelwal
Vivek Khandelwal

Reputation: 314

As Mitch suggested, you have to use dynamic sql in this case. As your SP includes variables cauclation and substitution(@DbName) on the fly. Dynamic sql is used when you need to build sql statement on the fly.

For Example: Finding CustomerID, ContactName, City dynamically.

DECLARE @sqlCommand varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'CustomerID, ContactName, City'
SET @city = '''London'''
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = ' + @city
EXEC (@sqlCommand)

For more information you can read and article on below given link.

In your SP you need to make below given sql statment using dynamic sql

select * from dbo.Gparcel as p INNER JOIN  @DbName.dbo.GISMappingAttributeTable as att ON p.FK_BlockNo =att.FK_BlockNo And p.FK_LandNo=FK_LandNo

https://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/

Upvotes: 3

Related Questions