Table name as an input parameter to a Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Table name as an input parameter to a Procedure

Author

Topic

BendJoe
Posting Yak Master

128 Posts

Posted - 2008-02-25 : 17:48:59

Hi
I have this procedure it is creating the proc but when I execute it gives error
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@ID".
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@nextCode".

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetNextAction]

(
@Code char(10),
@Track varchar(30)

)
AS
BEGIN

SET NOCOUNT ON;
Declare @ID int;
DECLARE @SQL1 VARCHAR(2000)
SET @SQL1='Select @ID = Sequence from'+' '+ @Track+ ' where Code=@Code'
EXEC(@SQL1);
Declare @nextCode varchar;
DECLARE @SQL2 VARCHAR(2000)
SET @SQL2 ='Select @nextCode= Code from '+' '+ @Track+ ' where sequence =(@ID+1)'
EXEC(@SQL2);
Declare @NextAction varchar(30);
Select @NextAction= nextAction from [dbo].[CaseStage] where Code=@nextCode;
Select @NextAction;

END
GO
Can someone correct me here
Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-25 : 17:58:59

Why are you using dynamic SQL for this?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

BendJoe
Posting Yak Master

128 Posts

Posted - 2008-02-25 : 18:10:59

The same stored proc needs to be run against different tables depending on the user input.
Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-25 : 18:16:11

Why? Sounds like bad database design. Could you post the table structure for the tables that will be used in this stored procedure?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-26 : 01:27:46

You must read this fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources