EXEC String Problem (reply)
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
EXEC String Problem (reply)
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
EXEC String Problem (reply)
Hi All,
Sorry I make a mistake.
please try the following:
EXEC ( `SELECT SerialNo = "` +@serial_num_temp + `" FROM ` + @SerialTable )
Best Regards,
wk wong
EXEC String Problem (reply)
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
Enterprise Manager for SQL2K
Is there anyplace I can download the EM for SQL2k?
Thanks,
Kevin