SQL Server, using functions in stored procedure

Question

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

You can't parameterize identifiers in sql server. You need to use dynamic sql for that. — Zohar Peled
– Zohar Peled, Commented May 16, 2016 at 5:48

Vivek Khandelwal · Accepted Answer · 2016-05-16 05:48:22Z

3

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/

answered May 16, 2016 at 5:48

Vivek Khandelwal

4341 gold badge4 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Majid Hojati Over a year ago

Thank..This is exactly what I need

Collectives™ on Stack Overflow

SQL Server, using functions in stored procedure

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related