CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
Saurabh Moses Ram
Saurabh Moses Ram

Reputation: 59

Sql Server Stored Procedure dynamically select table name

i have this stored procedure, I want to dynamically select table name based on the variable passing ie @Practice_Short_Name 

 Create procedure [dbo].[GetCompleteCPTDetails]
    @Practice_Short_Name varchar(50) is Null,
    @Uploaded_Date varchar(30) is Null
 as
 begin
 DECLARE @CPTtablename varchar(100)
 DECLARE @vQuery NVARCHAR(100)

 --Dynamically select Table name based on @practice_short_name
 set @CPTtablename ='ACER_CLAIMS_MASTER_DETAIL_Hist_'+@Practice_Short_Name+''

   SET @vQuery = 'select Practice_Short_Name,Service_Date_From,Carrier_Name,
   Location_Description,Patient_Number,Patient_First_Name,
   Patient_Last_Name,Voucher_Number,Procedure_Code,Service_Fees,
   Service_Payments,Service_Adjustments,Acer_Status,Acer_Allowed_Amount    

   from '+@CPTtablename+'

   where  Uploaded_Date ='+@Uploaded_Date+' and
   Practice_Short_Name ='+@Practice_Short_Name+'
   order by acer_status asc, Service_Date_From desc, Patient_First_Name asc'

EXEC @vQuery
end
GO

but while running this proc it is throwing error like

"Could not find stored procedure 'select Practice_Short_Name,Service_Date_From,Carrier_Name, Location_Description,Patient_Numb'."

can anyone explains me what i am doing wrong..

Upvotes: 0

Views: 252

Answers (3)

Zohar Peled
Zohar Peled

Reputation: 82524

The variable you use to store the query is too short to store the entire query text.
This means that the query is truncated to fix the 100 characters limit of the @vQuery variable. Use nvarchar(max) instead of nvarchar(100).

Also, using Dynamic SQL is usually a security hazard, leaving an opening to SQL Injection attacks. You might want to re-think your design and keep all the data in the same table, instead of keeping different tables with the same structure on your datadase.

Upvotes: 1

Deep
Deep

Reputation: 3202

2 things need to be corrected : length of your dynamic query variable that caused you this problem. use parameterized dynamic query to prevent sql injection.

DECLARE @CPTtablename varchar(100)
DECLARE @vQuery NVARCHAR(2000) -- increased length, you can also use nvarchar(max)
--Dynamically select Table name based on @practice_short_name
SET @CPTtablename ='ACER_CLAIMS_MASTER_DETAIL_Hist_' + @Practice_Short_Name + ''
SET @vQuery = 'select Practice_Short_Name,Service_Date_From,Carrier_Name,
   Location_Description,Patient_Number,Patient_First_Name,
   Patient_Last_Name,Voucher_Number,Procedure_Code,Service_Fees,
   Service_Payments,Service_Adjustments,Acer_Status,Acer_Allowed_Amount    

   from ' + @CPTtablename + '

   where  Uploaded_Date = @Uploaded_Date and
   Practice_Short_Name = @Practice_Short_Name
   order by acer_status asc, Service_Date_From desc, Patient_First_Name asc'

--dynamic query with input params
EXEC sp_executesql
  @vQuery,
  N'@Uploaded_Date varchar(30), @Practice_Short_Name varchar(50)',
  @Uploaded_Date = @Uploaded_Date,
  @Practice_Short_Name = @Practice_Short_Name

Upvotes: 0

Robert
Robert

Reputation: 25763

This way you call a procedure

EXEC @vQuery

but this way you run dynamic sql

EXEC (@vQuery)

so your SP should look as below

 Create procedure [dbo].[GetCompleteCPTDetails]
    @Practice_Short_Name varchar(50) is Null,
    @Uploaded_Date varchar(30) is Null
 as
 begin
 DECLARE @CPTtablename varchar(100)
 DECLARE @vQuery NVARCHAR(100)

 --Dynamically select Table name based on @practice_short_name
 set @CPTtablename ='ACER_CLAIMS_MASTER_DETAIL_Hist_'+@Practice_Short_Name+''

   SET @vQuery = 'select Practice_Short_Name,Service_Date_From,Carrier_Name,
   Location_Description,Patient_Number,Patient_First_Name,
   Patient_Last_Name,Voucher_Number,Procedure_Code,Service_Fees,
   Service_Payments,Service_Adjustments,Acer_Status,Acer_Allowed_Amount    

   from '+@CPTtablename+'

   where  Uploaded_Date ='+@Uploaded_Date+' and
   Practice_Short_Name ='+@Practice_Short_Name+'
   order by acer_status asc, Service_Date_From desc, Patient_First_Name asc'

EXEC (@vQuery)
end
GO

Upvotes: 2

Related Questions