"Syntax error converting the varchar value '@reqno' to a column of dat

[CompCodeBoy]

Guys,

I need help with this T-SQL syntax. I can not get it to work. I got a stored procedure that does a simple search.
here it is:

CREATE PROCEDURE [dbo].[usp_Search]

@reqno int ,
@deptno int

AS

SET NOCOUNT ON
DECLARE @strSQL varchar(1200)
DECLARE @check bit
SET @check = 0

SET @strSQL = 'SELECT *,teams.team_autonumber,team_name,departments.department_No,departments.department_Name--,projectstatuscodes.projectstatuscode,projectstatuscodes.projectstatuscodeDesc
FROM OpenIssues inner join teams ON teams.team_autonumber=openissues.team_assigned_to inner join Departments ON openissues.Req_Dept_No=Departments.department_No'

SET @strSQL = @strSQL + ' WHERE date_requested>''01/01/1900'''

IF (@reqNo > 0)

BEGIN
SET @strSQL = @strSQL + ' AND ( req_autonumber = cast (''@reqno'' AS int) '
SET @check = 1
END

IF @check =1
SET @strSQL = @strSQL + ' )'

EXEC (@strSQL)

My problem is on this line

SET @strSQL = @strSQL + ' AND ( req_autonumber = cast (''@reqno'' AS int) '

The error I get is:

"Syntax error converting the varchar value '@reqno' to a column of data type int. "

When I replace @reqno with an integer like 22 the stored procedure works just as expected. I took cast off and still got the same result. What Am I missing here ? Thank!

 

[amrita418]

Try this line

set @strSQL = @strSQL +  ' AND (req_autonumber  = ' + cast (@reqno AS int)

The best way to resolve such issues is the print the string value and see if it matches your expectations.

Regards,
AA

 

[vongrunt]

Remove all four single quotes surrounding reqno.

Or even better: get rid of dynamic SQL. In this case it is definitely not needed.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[NoCoolHandle]

2 reasons..

1. You are using dynamic sql (which is EVIL and unnecessary in your case and

2.You are passing a string of @reqno into the string @strsql

So sql was trying to find an integer value of @Reqno which is a string.

Try

CREATE PROCEDURE [dbo].[usp_Search] 

@reqno int ,
@deptno int

AS

SET NOCOUNT ON
DECLARE @check bit
SET @check = 0


IF (@reqNo > 0)

BEGIN
        SET @check = 1
END

IF @check =1
    SET @strSQL = @strSQL +  ' )'
SELECT *,teams.team_autonumber,team_name,departments.department_No,departments.department_Name--,projectstatuscodes.projectstatuscode,projectstatuscodes.projectstatuscodeDesc
FROM OpenIssues inner join teams ON teams.team_autonumber=openissues.team_assigned_to inner join Departments ON openissues.Req_Dept_No=Departments.department_No
 WHERE  date_requested>'01/01/1900' and req_autonumber = @reqno

 

[CompCodeBoy]

Thanks for your replies. I tried both solutions. They did not work . As I mentioned before, if I replace the @reqno parameter with an integer value, the stored procedure generates the desired result. I'm confused!

 

[vongrunt]

I just noticed this:

SET @strSQL = 'SELECT *,teams.team_autonumber,team_name,departments.department_No,departments.department_Name--,...

Exactly how is strSQL is supposed to look like?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[CompCodeBoy]

CREATE PROCEDURE [dbo].[usp_Search]

@reqno int ,
@deptno int

AS

SET NOCOUNT ON
DECLARE @strSQL varchar(1200)
DECLARE @check bit
SET @check = 0

SET @strSQL = 'SELECT *,teams.team_autonumber,team_name,departments.department_No,departments.department_Name
FROM OpenIssues inner join teams ON teams.team_autonumber=openissues.team_assigned_to inner join Departments ON openissues.Req_Dept_No=Departments.department_No'

SET @strSQL = @strSQL + ' WHERE date_requested>''01/01/1900'''

IF (@reqNo > 0)

BEGIN
SET @strSQL = @strSQL + ' AND ( req_autonumber = cast (@reqno AS int) '
SET @check = 1
END

IF @check =1
SET @strSQL = @strSQL + ' )'

EXEC (@strSQL)
---------------------------------------------

Replacing

SET @strSQL = @strSQL + ' AND ( req_autonumber = cast (@reqno AS int) '

WITH

SET @strSQL = @strSQL + ' AND ( req_autonumber = cast (2077 AS int) '

will make the stored procedure work.

 

[vongrunt]

Agh... how about:

CREATE PROCEDURE [dbo].[usp_Search]( @reqno int )
AS
SET NOCOUNT ON

SELECT teams.team_autonumber, team_name, departments.department_No, departments.department_Name
FROM OpenIssues 
inner join teams ON teams.team_autonumber=openissues.team_assigned_to 
inner join Departments ON openissues.Req_Dept_No=Departments.department_No
where date_requested > '19000101'
	and (@reqNo<=0 or (@reqNo > 0 and req_autonumber=@reqNo))

GO

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLBill]

WHY are you casting @reqno as INT? @reqno is DECLAREd as INT, so there's no need to CAST it.

What value are you entering for @reqno? Is it a value that can be an INT?

-SQLBill

Posting advice: FAQ481-4875

 

[CompCodeBoy]

Bill,
you're right I do not have to do that. Again, if I do not use cast, I'm getting same error. Basically, the value of @reqno is not passing correctly. As I said before, passing an integer makes it work. There must be an easy way out!
Thanks guys.

 

[amrita418]

Adding print statements is the best way to figure out where the problem is.

What is the value of @reqno you are passing. Is it being set? What does the @strSQL look like before and after IF conditional expression.

Also, why are you not looking at other options provided by nocoolhandle and vongrunt (about not using dynamic sql in this case).

 

[CompCodeBoy]

it is passed from an ASP.net page. OK give me another option of doing this

I have 6 to 7 comboboxes and textboxes on the web page and based on the user selection I pass values to the stored procedure.

If I have to check in my stored prcoc. every single combination, I will have like 64 If statements.
So what's a good solution

 

[CompCodeBoy]

SET @strSQL = @strSQL + ' AND ( req_autonumber = '+cast(@reqno as varchar)+' '

Finally got it working. The above modification took care of it. Thanks for all your input