Set integer variable to SELECT within stored procedure

[RichardParry]

Hi All,

I am writing a relatively simple stored procedure, but having problems with a datatype conversion issue.

The stored procedure does a number of things, firstly it brings in some variables and does an UPDATE on a table - great.

The second, is it needs to get the MasterID from another table, so be then used as the criteria for another UPDATE on another table. This saves having to find the MasterID in the VB code - thought it would be best done in one SP.

I have the following;

<FIRST SQL UPDATE>

DECLARE @masterid int
SET @masterid = 'SELECT MasterID FROM Accounts_Users WHERE ID=' + Convert(varchar(4),@id)

UPDATE <OTHER TABLE> WHERE ID=@masterid

The variable @id is brought into the SP and is also used with the first UPDATE. I am sure I have done this before and it worked great, obviously not, as I get the following error;

[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value 'SELECT MasterID FROM Accounts_Users WHERE ID=1' to a column of data type int.

In short, how can I perform a SELECT within the SP and set its single Integer result to a variable, that I can then use further down in the SP? I am sure this is possible?

 

[bborissov]

You don't need this (I mean some kind of Dynamic SQL) You must use:

DECLARE @masterid int
SELECT @masterid = MasterID
       FROM Accounts_Users
WHERE ID= @id

UPDATE <OTHER TABLE> WHERE ID=@masterid

or even:

UPDATE <OTHER TABLE> SET SomeFIeld = ?????
INNER JOIN Accounts_Users
     ON <OTHER TABLE>.Id = Accounts_Users.MasterID AND
       Accounts_Users.Id = @Id

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[RichardParry]

Hi Borislav,

Thank you for the very prompt reply! The examples worked great!

Thanx, Richard