Pass Table Name as Input Parameter

I have a StoredProcedure, to which i am passing Table Name as Input Paramter ..

On doing so, i am getting an error ..

Msg 1087, Level 16, State 1, Procedure usp_DrugSummationByGenericCode, Line 15
Must declare the table variable "@table".


So, How to pass Table Name as Input Paramter for a StoredProcedure. Since i need to use this Query Syntax for more than one table on multiple scenarios
Create proc usp_DrugSummationByGenericCode
(
	@table nvarchar(4000)
) 
As
Begin
 
			with p as (
			select    
			   OC.[Generic Code],  
			   OC.[Drug Name],
			   OC.NDC,     
			   Sum(OC.[Dispensed Qty]) as Qty  
			from  @table OC      
			Group By OC.[Generic Code], OC.NDC,OC.[Drug Name] 
			)
			select x.[Generic Code], x.NDC, x.[Drug Name], y.Qty from 
			(
			select p.*, row_number() over (partition by [Generic Code] order by Qty desc) rn from p
			) x 
			left join 
			(
			select [Generic Code], sum(Qty) Qty from p group by [Generic Code]
			) y 
			on x.[Generic Code]=y.[Generic Code]
			where rn=1;

End

Open in new window

chokkaStudentAsked:
Who is Participating?
 
Lee SavidgeCommented:
You cannot use the table name as a parameter and then use it in the way you want to in the sp. You need dynamics SQL.
Create proc usp_DrugSummationByGenericCode
(
        @table nvarchar(100) -- if your table name could have been upto 2000 chars long you need to rethink your naming convention.
) 
As
Begin
    declare @sql as nvarchar(2000)

select @sql = 'with p as ( ' + 
              '          select     ' + 
              '             OC.[Generic Code],   ' + 
              '             OC.[Drug Name], ' + 
              '             OC.NDC,      ' + 
              '             Sum(OC.[Dispensed Qty]) as Qty   ' + 
              '          from  ' + @table + ' OC       ' + 
              '          Group By OC.[Generic Code], OC.NDC,OC.[Drug Name]  ' + 
              '          ) ' + 
              '          select x.[Generic Code], x.NDC, x.[Drug Name], y.Qty from  ' + 
              '          ( ' + 
              '          select p.*, row_number() over (partition by [Generic Code] order by Qty desc) rn from p ' + 
              '          ) x  ' + 
              '          left join ' +  
              '          ( ' + 
              '          select [Generic Code], sum(Qty) Qty from p group by [Generic Code] ' + 
              '          ) y  ' + 
              '          on x.[Generic Code]=y.[Generic Code] ' + 
              '          where rn=1; '
    exec(@sql)

End

Open in new window

0
 
chokkaStudentAuthor Commented:
Thank you, But why in SQL - We are not able to pass Table Name as Parameter ?? Any specific reasosn .. !!!
0
 
Lee SavidgeCommented:
Ask Microsoft!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.