CODEWITHSUNDEEP
sqlsql-servert-sqlparameters
GorkemHalulu
GorkemHalulu

Reputation: 3075

Stored Procedure That Return Different Table According To Parameter

I want to write a stored procedure that takes @FirmId as a parameter and I will use the related table according to this parameter.

What I want to obtain (but I don't want to use) is something like that:

CREATE PROCEDURE spFirmDetailGetByFirmId 
    @FirmId AS INT
AS
BEGIN
    IF @FirmId = 1
        SELECT * FROM Firm1
    ELSE IF @FirmId = 2
        SELECT * FROM Firm2
        .
        .
        .
        .
    ELSE IF @FirmId = 1000
        SELECT * FROM Firm1000
END

And also I don't want to create query string and then EXEC it, something like that in the fallowing code block. Because the real query is too complex and it will be very hard to manage if I use this option.

CREATE PROCEDURE spFirmDetailGetByFirmId 
    @FirmId AS INT
AS
BEGIN 
    DECLARE @Query AS NVARCHAR(MAX) = 'SELECT * FROM Firm'
    SET @Query = @Query + CAST(@FirmId AS NVARCHAR(10))
    EXEC(@Query)
END

Is there any other option? Thanks.

Upvotes: 2

Views: 1104

Answers (2)

Byron
Byron

Reputation: 389

This code can by use in a stored procedure to automatic create the view, every time you need to add columns

declare @tableId  int
declare @columns varchar(max)
declare @tablesCount int
declare @tableName varchar(255)
declare @query varchar(255)
declare @id int
declare @result nvarchar(max)

set @columns = ''
set @tableName = 'Firm'
set @id = 1
set @result = ''

--Base table
select @tableId = object_id from sys.tables where name =@tableName
--Count how many table with the 'same name'
select @tablesCount= count(*) from sys.tables where name like @tableName+'%'
--Build Columns to add in the view
select  @columns =@columns+name+', 'from Sys.columns where object_id = @tableId

--Drop View
set @result = 'Drop view  vw_'+@tableName
exec sp_executesql @result
set @result=''

while(@id<=@tablesCount)
Begin 
 declare @idVarchar varchar(10)
 set @idVarchar = cast(@id as varchar(10))

 set @result =@result+'Select '+@columns+@idVarchar+' as FirmId from '+@tableName+@idVarchar
 +'
Union all
'
 set @id =@id+1
End
set @result  = substring(@result, 1, len(@result)-12)
set @result='Create view vw_'+@tableName+' as
'+@result


exec sp_executesql @result

There is a another choice to this, you can also use sp_helpText to get the current definition of the view and append only add new table identifier

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67341

I take your Yes the tables are identical and will be kept identical to suggest two approaches:

DECLARE @Firm VARCHAR(10)='Firm3';

SELECT * FROM Firm1 WHERE @Firm='Firm1'
UNION ALL
SELECT * FROM Firm2 WHERE @Firm='Firm2'
UNION ALL
SELECT * FROM Firm3 WHERE @Firm='Firm3'
[...]
UNION ALL
SELECT * FROM Firm1000 WHERE @Firm='Firm1000'

The second is:

DECLARE @query NVARCHAR(MAX)='SELECT * FROM ####';
SET @query=REPLACE(@query,'####',@Firm);
EXEC (@query)

The second could be used with a VIEW (in place of the @query), where you could read the VIEW's definition into the variable and create an ALTER VIEW-statement dynamically... Your procedure would call the same VIEW (but this would crash with parallel calls!)

Upvotes: 3

Related Questions