July 24, 2010 at 7:47 am
I'm calling the stored procedure from a web application and passing a parameter (@agenttype varchar(50)) based on the user's selection from a drop down list.
The selection the user makes determines the specific field from the transactions table that will be used to join the agents table based on its agentid field.
@agenttype can be either 'seller_id' or 'buyer_id'.
All of the ID fields are of type bigint.
So this is the TSQL:
SELECT transactions.transactionid,agents.agentname
FROM transactions
INNER JOIN agents on @agenttype=agentid
And this is the error:
Error converting data type varchar to bigint.
I'm not sure why.
July 24, 2010 at 8:42 am
You cannot specify a column name in a variable. What that statement is going to do is to compare the value of the variable with the value of the agentid column. Since the varchar cannot be converted to int, you get the expected error.
If the column name is variable, you'll need to build up a dynamic SQL statement and EXEC (or sp_executesql) that. Beware of SQL injection.
Basically...
DECLARE @SQL nvarchar(1000)
SET @SQL = 'SELECT transactions.transactionid,agents.agentname
FROM transactions
INNER JOIN agents on ' + @agenttype + '=agentid'
EXEC sp_executesql @SQL
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply