How to pass dynamic View name in SQL stored procedure without using a dynamic query?

Question

Here is my stored procedure below. I am concatenating @CultureCode paramter along with view name which is [_0002HR_EmployeeNames_en-US_View]. The part en-US will be passed through a parameter named as @CultureCode. Is there any way to do so because i have requirement not to use dynamic query. Thank you.

CREATE PROCEDURE [dbo].[_001HR_Report_Loans] (@Parameters VARCHAR(max))
 AS
 DECLARE @ReportOption VARCHAR(5)    SET @ReportOption     = [dbo].DB_Split(@Parameters, 1) 
 DECLARE @CultureCode VARCHAR(10)    SET @CultureCode      = [dbo].DB_Split(@Parameters, 2) 
 DECLARE @ShowItems VARCHAR(5)       SET @ShowItems        = [dbo].DB_Split(@Parameters, 3) 
 DECLARE @StartDate NVARCHAR(8)      SET @StartDate        = [dbo].DB_Split(@Parameters, 4) 
 DECLARE @EndDate NVARCHAR(8)        SET @EndDate          = [dbo].DB_Split(@Parameters, 5) 
 DECLARE @EmployeeCode NVARCHAR(30)  SET @EmployeeCode     = [dbo].DB_Split(@Parameters, 6) 
 DECLARE @BranchCode NVARCHAR(30)    SET @BranchCode       = [dbo].DB_Split(@Parameters, 7) 

 --IF @StartDate = ''
    -- SET @StartDate = NULL


 SELECT HR.*, EN.[Name] AS EmployeeName
 FROM [0002HR_EmployeeLoans] HR  
 LEFT JOIN [_0002HR_EmployeeNames_ + '@CultureCode' +_View]  EN ON HR.EmployeeCode = EN.EmployeeCode 
 LEFT JOIN [_0002HR_EmployeePackagesView]  EP ON EP.EmployeeCode = HR.EmployeeCode

 WHERE 
(HR.EmployeeCode = @EmployeeCode OR @EmployeeCode IS NULL)
AND
(EP.BranchCode = @BranchCode OR @BranchCode IS NULL)
AND
(HR.Date BETWEEN @StartDate AND @EndDate OR @StartDate IS NULL AND @EndDate IS NULL) 
AND
(HR.Date >= @StartDate OR @StartDate IS NULL)
AND
(HR.Date  <= @EndDate OR @EndDate IS NULL)

Tag dbms product used. (Doesn't look like ANSI SQL at all...) — jarlh, Jan 21 '16 at 13:32

score 1 · Answer 1 · answered Jan 21 '16 at 13:33

1

This is not possible in T-SQL so far. Things like TOP clauses, table or column names can not be parameterized.

One way would be to create a union over all possible tables/views, add a new column that matches the table/view name and filter that.

Like this:

SELECT * FROM
(
    SELECT 'Table1' as TableName, t1.* FROM Table1 t1 WHERE ...
    UNION ALL
    SELECT 'Table2' as TableName, t2.* FROM Table2 t2 WHERE ...
) tmp
WHERE TableName = @tableName

Another (and possibly the most "clean" way) would be to only have one single table and make the culture a column in that table, so you only need to pass the correct culture string to filter over that column.

answered Jan 21 '16 at 13:33

Thorsten Dittmar

55,956
8
91
139

i have heard that there is a way to do so but i really don't know what it is. Thank you for your feed back. Please rate my question as i am new to stackoverflow. Thank you once again. – Muhammad Bilal Jan 21 '16 at 13:36
1

You could also create a single view as a union of all your culture-dependent views that introduces a new column like I suggested in my answer. – Thorsten Dittmar Jan 21 '16 at 13:38

score 1 · Answer 2 · answered Jan 21 '16 at 13:40

So you have a view per language. They are getting the data from the tables for their particular language. But now you want to write a procedure that is not language-specific.

The solution seems simple: Don't use the language views, but access the tables directly instead. (Or build an all-languages view which you query with where language = @language.)

How to pass dynamic View name in SQL stored procedure without using a dynamic query?

2 Answers2