EXEC String Problem

[jeff alerta]

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

[Alex]

On 8/21/98 4:15:58 PM, jeff alerta wrote:
> 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

Hi
As I understand You construct SQL statement incorrectly, because You concatenate empty variable @serial_num_temp. As a result You have something like this: `SELECT = SerialNo FROM tblSerialNo001`.

Becides even if You construct within variable SQL statement like:
`SELECT @serial_num_temp = SerialNo FROM tblSerialNo001` (looks like OK, huh?)
You cannot execute it by EXEC function, because variable @serial_num_temp was not declared inside a statement scope.

Resume - You cannot use variables in statements which You execute by EXEC functions.

Bye
Alex

[wk wong]

Hi All,

Try to change the statement from

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

to

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

Best Regards,
wk wong

Hi All,
Sorry I make a mistake.
please try the following:

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

Best Regards,
wk wong

[jeff alerta]

Thank you all for the replies.

I did try the code below ( and multiple variations of the same ) but
that happened is that the variables when quoted that way would just
be converted into literal strings.

This of course was not what I wanted.

Finally, with some help from the Microsoft SQL Server newsgroup at
msnews.microsoft.com I was able to figure out that I needed to create
a temporary table first, assign the value I needed there and then use
that value.

Here is the code I finally got to work for anyone out there that is
interested.

Again, thanks for all the help.




SET NOCOUNT ON

CREATE TABLE #VarHolder (
Serial_Num_Temp varchar (15) NULL )


DECLARE @SerialTable as varchar(30),
@serial_no_temp varchar(15),
@incremented_value varchar(15)

SELECT @SerialTable = "tblSerialNo" + @CompanyNo

EXEC (`INSERT INTO #VarHolder(Serial_Num_Temp) SELECT Serial_No FROM ` +@SerialTable)

SELECT @serial_no_temp = Serial_Num_Temp
FROM #VarHolder

DELETE #VarHolder

SELECT @serial_no_output = @serial_no_temp


SELECT @incremented_value = CONVERT(varchar(15),(CONVERT(integer,@serial_no_ou tput + 1)))

EXEC (`UPDATE ` + @SerialTable + ` SET Serial_No = ` +@incremented_value )






jeff alerta






On 8/23/98 4:28:26 AM, wrote:
> Hi All,
Sorry I make a mistake.
please try the following:

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

Best Regards,
wk wong

[Kevin]

Is there anyplace I can download the EM for SQL2k?

Thanks,
Kevin