CODEWITHSUNDEEP
asp.netsqlsql-server
chamara
chamara

Reputation: 12711

passing parameters to insert query

DECLARE @SQL1 VARCHAR(MAX)
SET @SQL1='SELECT * FROM tblResponsibleEngineer WHERE ResponsibleEngineer='+CAST(@ResponsibleEngineer AS VARCHAR(500))+''
EXEC(@SQL1)
IF @@ROWCOUNT>0
BEGIN
SET @Result='EXIST'

above query fails execution due to the way i pass the parameter @ResponsibleEngineer in to the query. error is "Invalid column name TEST". TEST is the value i'm passing as the parameter value @ResponsibleEngineer and there is no such column as TEST in the table.what the error when passing the parameter to insert query?

Upvotes: 1

Views: 302

Answers (5)

Muthu
Muthu

Reputation: 2685

Remove the spaces around your select. A quick sample

DECLARE cnt int
SELECT cnt= count(*) FROM tblResponsibleEngineer WHERE ResponsibleEngineer='CAST(@ResponsibleEngineer AS VARCHAR(500))'
IF cnt>0
BEGIN
  SET @Result='EXIST'

Hope this helps.

Upvotes: 0

Ernesto Campohermoso
Ernesto Campohermoso

Reputation: 7371

Your query is translating to

 SELECT * FROM tblResponsibleEngineer WHERE ResponsibleEngineer=TEST

You need to get the following:

 SELECT * FROM tblResponsibleEngineer WHERE ResponsibleEngineer='TEST'

Upvotes: 0

Icarus
Icarus

Reputation: 63956

Try this:

DECLARE @SQL1 VARCHAR(MAX)
    SET @SQL1='SELECT * FROM tblResponsibleEngineer WHERE ResponsibleEngineer='''+CAST(@ResponsibleEngineer AS VARCHAR(500))+''''
    EXEC(@SQL1)
    IF @@ROWCOUNT>0
    BEGIN
    SET @Result='EXIST'

Upvotes: 0

TheTechGuy
TheTechGuy

Reputation: 17354

you must declare @ResponsibleEngineer and set its value. It is basically used as variable.

DECLARE @ResponsibleEngineer varchar(10)
SET @ResponsibleEngineer = 'my test'

Upvotes: 0

Curtis
Curtis

Reputation: 103348

You need speach marks around the Parameter:

'SELECT * FROM tblResponsibleEngineer WHERE ResponsibleEngineer='''+CAST(@ResponsibleEngineer AS VARCHAR(500))+''''

Otherwise you are generating:

SELECT * FROM tblResponsibleEngineer WHERE ResponsibleEngineer=TEST'

Therefore its looking for a column TEST. By having the parameter in speach marks you are generating:

SELECT * FROM tblResponsibleEngineer WHERE ResponsibleEngineer='TEST'

This query will then run a WHERE clause where the ResponsibleEngineer column equals the string value TEST

Upvotes: 3

Related Questions