EXEC String Problem

Hi All,

I am using SQL Server 7 Beta 3 and I am having trouble with using a variable
in an EXEC statement.

I have a stored procedure that is designed to retrieve a serial
number from a table and then increment it by one(1) and return the
original serial number to the calling function.

As long as the table name is hardcoded the following code works fine:


CREATE PROCEDURE IncrementSerialIDNo
(@serial_num_temp varchar(15), @serial_num_output varchar(15) output)

AS
BEGIN TRANSACTION
BEGIN


SELECT @serial_num_temp = tblSerialNo.SerialNo
FROM tblSerialNo

SELECT @serial_num_output = @serial_num_temp

UPDATE tblSerialNo
SET tblSerialNo.SerialNo =
CONVERT(varchar(15),(CONVERT(integer,@serial_num_t emp ) + 1))

IF (@@error = 0)

COMMIT TRANSACTION

ELSE

ROLLBACK TRANSACTION

END

RETURN


However, if I change the table name to a variable that is passed to the
stored procedure as a argument, I need to use the EXEC statement.

My problem is that for the EXEC statement to work I need to escape the
variables and I run into a problem with the " = " ( equal sign ).

Example:


DECLARE @SerialTable as varchar(30), @CompanyNo as varchar (3)

SELECT @CompanyNo = `001`

SELECT @SerialTable = `tblSerialNo` + @CompanyNo

EXEC ( `SELECT ` +@serial_num_temp + `= SerialNo FROM ` +
@SerialTable )


Following is the error message that I keep getting:

Server: Msg 170, Level 15, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near `=`.



No amount of testing with different qoutes or doublequotes and such,
have made any difference.

I have tried numerous variations on the above SELECT statement with the
same end result of the error on the "=" sign.

I cannot seem to assign one variable to another using the "=" (equal sign)
in an EXEC statement.

Does anyone have example code of using the equal sign to assign one variable
to another in an EXEC statement or can someone point me in the right direction?

Thanks in advance for any and all help.



jeff alerta
jeff@nestworks.com