Dynamic table in sql Procedure?

squattyarun

HI all, I am creating a Procedure in which i need to take table name from user as input I m trying to achieve this but getting some errors please help me out. here is the code.... set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER procedure [dbo].[apo_GetQuestionsByInqTransId] --Passing inventory id as input variable --Generate all the questions of it @InqTransId int, @appId int, @option nvarchar(20), @schema nvarchar(30) As Begin if @option='All' begin select qm.QID,qm.[name],QM.ParentQID,itql.SequenceNo from QuestionMaster qm,InquiryTransaction it, InquiryQuestionLinkage itql where itql.qid=qm.qid and it.InquiryTransId=itql.InquiryTransId and it.InquiryTransId=@InqTransId order by QM.ParentQID end else begin declare @AnswerDetailTable as nvarchar(40) set @AnswerDetailTable =@schema+'.AnswerDetalTable' select qm.QID,qm.[name],QM.ParentQID,itql.SequenceNo from QuestionMaster qm,InquiryTransaction it,InquiryQuestionLinkage itql where itql.qid=qm.qid and it.InquiryTransId=itql.InquiryTransId and it.InquiryTransId=@InqTransId and qm.QID not in(select QID **from @AnswerDetailTable** where appId=@appId) order by QM.ParentQID end End In the above code I need ur help in the else statement where I am using @AnswerDetailTable in from clause. I m geetin error "Msg 102, Level 15, State 1, Procedure apo_GetQuestionsByInqTransId, Line 33 Incorrect syntax near '@AnswerDetailTable'." wating for suggestions....:)

Arun Singh Noida.

pmarfleet

You can't do this. You have to build a string that represents your SQL statement and execute it using the EXEC statement.

Paul Marfleet

John ph

SQl Server does not do a parameter substitution for Table Names in the SQL statement. To acheive this you need to build a dynamic sql and execute using sp_executesql or EXEC command. try something like this...

DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
DECLARE @appId AS INT 
 
SET @appId = 10
SET @SQLQuery = 'SELECT * FROM ' + @AnswerDetailTable  + ' WHERE appId = @appId'
SET @ParameterDefinition =  '@appId INT'
 
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @appId

Regards
- J O N -

squattyarun

Thanx now it is solved

Arun Singh Noida.