Stored procedure and @Tablename

[Eyresy]

I'm trying construct the Following Stored Procedure however I cannot select the @Table which I have refered to as @T1 in my Stored Procedure. I can hard code the Table name but I will have to create 13 stored procedures for each table that I need am I going wrong some where

I'm running the following SQL to test the SP
DECLARE @ReturnValue bit
EXECUTE sp_CompareAlloc 'CH','NL','TQ1','1',12, @ReturnValue OUTPUT
PRINT @ReturnValue


CREATE PROCEDURE sp_CompareAlloc

@SalesOrg char(2), /* ie 'CH'*/
@UNCountryCode char(2), /* ie 'NL' */
@ModelGrpCode char(3), /* ie 'TQ1' */
@EOSMode char(1), /* ie 'C' for COF, '1' for OTD1, and '2' for OTD2 */
@Alloc1 int = 0, /* the single allocation value for OTD mode, or the first weeks volume in COF Mode < 9999 */
@ReturnValue bit OUTPUT, /* return value 0 implies same alloc, 1 implies different output */
@NoWeeks int = 1, /* the number of pipeline weeks for COF Mode < 10*/
@Alloc2 int = 0, /* the volumes for the weeks 2 - 10 in COF Mode < 9999 */
@Alloc3 int = 0,
@Alloc4 int = 0,
@Alloc5 int = 0,
@Alloc6 int = 0,
@Alloc7 int = 0,
@Alloc8 int = 0,
@Alloc9 int = 0,
@Alloc10 int = 0


AS

DECLARE @CurrentAlloc1 int
DECLARE @CurrentAlloc2 int
DECLARE @CurrentAlloc3 int
DECLARE @CurrentAlloc4 int
DECLARE @CurrentAlloc5 int
DECLARE @CurrentAlloc6 int
DECLARE @CurrentAlloc7 int
DECLARE @CurrentAlloc8 int
DECLARE @CurrentAlloc9 int
DECLARE @CurrentAlloc10 int

DECLARE @T1 varchar(1400)

SET @T1 = 'dbo.T_'+@SalesOrg+@UNCountryCode+'_Params'

/* Assume Allocation is the same */
SET @ReturnValue = 0

/* find the current allocation values */

SET @CurrentAlloc1 = (SELECT Allocation01 FROM @T1 WHERE ModelGrpCode = Substring(@ModelGrpCode,1,2)

/* check the first allocation value */
IF @Alloc1 = @CurrentAlloc1
BEGIN
/* only now need to check if COF mode */
IF @EOSMode = 'C'
BEGIN
SET @CurrentAlloc3 = (SELECT Allocation03 FROM @T1 WHERE ModelGrpCode = Substring(@ModelGrpCode,1,2)
SET @CurrentAlloc4 = (SELECT Allocation04 FROM @T1 WHERE ModelGrpCode = Substring(@ModelGrpCode,1,2)
SET @CurrentAlloc5 = (SELECT Allocation05 FROM @T1 WHERE ModelGrpCode = Substring(@ModelGrpCode,1,2)
SET @CurrentAlloc6 = (SELECT Allocation06 FROM @T1 WHERE ModelGrpCode = Substring(@ModelGrpCode,1,2)
SET @CurrentAlloc7 = (SELECT Allocation07 FROM @T1 WHERE ModelGrpCode = Substring(@ModelGrpCode,1,2)
SET @CurrentAlloc8 = (SELECT Allocation08 FROM @T1 WHERE ModelGrpCode = Substring(@ModelGrpCode,1,2)
SET @CurrentAlloc9 = (SELECT Allocation09 FROM @T1 WHERE ModelGrpCode = Substring(@ModelGrpCode,1,2)
SET @CurrentAlloc10 = (SELECT Allocation10 FROM @T1 WHERE ModelGrpCode = Substring(@ModelGrpCode,1,2)

/* check wk 2 if necessary */
IF @NoWeeks > 1
BEGIN
SET @CurrentAlloc2 = (SELECT Allocation02 FROM @T1 WHERE ModelGrpCode = Substring(@ModelGrpCode,1,2)
IF NOT @Alloc2 = @CurrentAlloc2
SET @ReturnValue = 1
END

/* check wk 3 if necessary */
IF @NoWeeks > 2 AND @ReturnValue = 0
BEGIN
SET @CurrentAlloc3 = (SELECT Allocation03 FROM @T1 WHERE ModelGrpCode = Substring(@ModelGrpCode,1,2)
IF NOT @Alloc3 = @CurrentAlloc3
SET @ReturnValue = 1
END

/* check wk 4 if necessary */
IF @NoWeeks > 3 AND @ReturnValue = 0
BEGIN
SET @CurrentAlloc4 = (SELECT Allocation04 FROM @T1 WHERE ModelGrpCode = Substring(@ModelGrpCode,1,2)
IF NOT @Alloc4 = @CurrentAlloc4
SET @ReturnValue = 1
END

/* check wk 5 if necessary */
IF @NoWeeks > 4 AND @ReturnValue = 0
BEGIN
SET @CurrentAlloc5 = (SELECT Allocation05 FROM @T1 WHERE ModelGrpCode = Substring(@ModelGrpCode,1,2)
IF NOT @Alloc5= @CurrentAlloc5
SET @ReturnValue = 1
END

/* check wk 6 if necessary */
IF @NoWeeks > 5 AND @ReturnValue = 0
BEGIN
SET @CurrentAlloc6 = (SELECT Allocation06 FROM @T1 WHERE ModelGrpCode = Substring(@ModelGrpCode,1,2)
IF NOT @Alloc6= @CurrentAlloc6
SET @ReturnValue = 1
END

/* check wk 7 if necessary */
IF @NoWeeks > 6 AND @ReturnValue = 0
BEGIN
SET @CurrentAlloc7 = (SELECT Allocation07 FROM @T1 WHERE ModelGrpCode = Substring(@ModelGrpCode,1,2)
IF NOT @Alloc7= @CurrentAlloc7
SET @ReturnValue = 1
END

/* check wk 8 if necessary */
IF @NoWeeks > 7 AND @ReturnValue = 0
BEGIN

SET @CurrentAlloc8 = (SELECT Allocation08 FROM @T1 WHERE ModelGrpCode = Substring(@ModelGrpCode,1,2)
IF NOT @Alloc8= @CurrentAlloc8
SET @ReturnValue = 1
END

/* check wk 9 if necessary */
IF @NoWeeks >8 AND @ReturnValue = 0
BEGIN
SET @CurrentAlloc9= (SELECT Allocation09 FROM @T1 WHERE ModelGrpCode = Substring(@ModelGrpCode,1,2)
IF NOT @Alloc9 = @CurrentAlloc9
SET @ReturnValue = 1
END

/* check wk 10 if necessary */
IF @NoWeeks >9 AND @ReturnValue = 0
BEGIN
SET @CurrentAlloc10= (SELECT Allocation10 FROM @T1 WHERE ModelGrpCode = Substring(@ModelGrpCode,1,2)
IF NOT @Alloc10 = @CurrentAlloc10
SET @ReturnValue = 1
END
END
END
ELSE
SET @ReturnValue = 1


PRINT(@EOSMode)

PRINT(@Alloc1)

PRINT( @Alloc2)

PRINT( @CurrentAlloc1)

PRINT( @CurrentAlloc2)

All I seem to get when I check the Syntax is
Line 54: Incorrect syntax near '@T1'.

 

[ranjtl]

looks like the first issue is that you haven't actually @t1 as a table before you try to use it as one. here's a quick demo of using a table variable.

declare @t1 table(
pkT1 int identity,
t1Stuph varchar(50)
)
insert into @t1(t1Stuph) values('This is a test. It is only a test')
select * from @t1

-rik

 

[tlbroadbent]

The SQL language does not permit the use of variables for object or column names in SQL statements. You can create and execute dynamic SQL Statements.

Declare @sql varchar(2000)
Select @sql=
'Select * From '+@t1+' Where DateCol=''1/1/2002'''

Exec(@sql)

You can also use the system stored procedure sp_executesql. sp_executesql is more flexible offering parameter substitution and output variables. It is often more efficient than using Execute.

There are lots of examples of Excuting dynamic SQL in this forum. Try a keyword search. Also, see the following links for details and examples.

http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp

http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_04_8pwl.asp

http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_04_9uek.asp

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q262499

http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_14_2kc3.asp

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.