Hi
I am creating an store procedure in sql server 2005. i want to pass table name as parameter dynamically in sp. here is my sp but it give me an error

ALTER PROCEDURE dbo.ImportToTable	
	(
	@TableName nvarchar(100),
		@que_id int ,
		@sub_code int,
		@chap_no int,
		@que nvarchar(max),	
		@opt1 nvarchar(max),
		@opt2 nvarchar(max),
		@opt3 nvarchar(max),
		@opt4 nvarchar(max),
		@ans nvarchar(max),
		@solution nvarchar(max)	
	)
AS
	insert into   @tablename  (que_id,sub_code,chap_no,que,opt1,opt2,opt3,opt4,ans,solution) values(@que_id,@sub_code,@chap_no,@que,@opt1,@opt2,@opt3,@opt4,@ans,@solution)  
	RETURN

it give an error must declare the table variable "@TableName"

how i can solve the problem

that wont work, you need to create your dynamic sql. Something like:


decalare @sql as varchar(500)

set @sql = 'select * from ' + @table

exec (@sql)

please mark this thread as solved.

how i can use this in my query.

You Have To create Dynamic Sql as Posted by jfarrugia. You can use it as:

ALTER PROCEDURE dbo.ImportToTable
(
@TableName nvarchar(100),
@que_id int ,
@sub_code int,
@chap_no int,
@que nvarchar(max),
@opt1 nvarchar(max),
@opt2 nvarchar(max),
@opt3 nvarchar(max),
@opt4 nvarchar(max),
@ans nvarchar(max),
@solution nvarchar(max)
)
AS
Declare @Sql varchar(500)
BEGIN
set @Sql='insert into'+ @tablename +'(que_id,sub_code,chap_no,que,opt1,opt2,opt3,opt4,ans,solution) values('+@que_id+','+@sub_code+','+@chap_no+','+@que+','+@opt1+','+@opt2+','+@opt3+','+@opt4+','+@ans+','+@solution+')'
EXEC(@Sql)
RETURN
END

Why would you want to do this, anyways?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.