SS converting varchar to int on its own...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SS converting varchar to int on its own...

Author

Topic

crazycat503
Starting Member

16 Posts

Posted - 2012-04-30 : 07:05:31

I have a Stored Pro that accepts gender argument. It is like


ALTER PROCEDURE [dbo].[dslmsSP_memberReportsLists] 
	-- Add the parameters for the stored procedure here
	
	
	@gender varchar(1)='',
	
	
AS
DECLARE @runsql varchar(4000)='SELECT DISTINCT studentidcard,firstname from tblstudents'
DECLARE @limits varchar(4000)=''

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

-- add limits now
IF @gender<>''
SET @limits=@limits + ' sex=' + CHAR(39)  + @gender + CHAR(39)
-- DO WE NEED TO LIMIT STUFF OR NOT?
if @limits<>''
BEGIN

SET @runsql=@runsql + ' WHERE ' + @limits
END 

-- exectue statement 

EXEC (@runsql)
return @@ERROR

[/CODE]

When I run it, gives the following error:
[code]
Msg 245, Level 16, State 1, Procedure dslmsSP_memberReportsLists, Line 113
Conversion failed when converting the varchar value ' sex='M' ' to data type int.

I dont want it to be converted to int at all nor I have casted anywhere. It is drivin me really crazy..anyone can spot the source please? The error is raised if I pass gender argument only.

All guys!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-30 : 07:14:14

I can't see a reason why this should be done using dynamic sql.

Just

select ... from ... where sex=@gender

would do it

No, you're never too old to Yak'n'Roll if you're too young to die.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-30 : 07:18:45

I am assuming you have a rationale for doing this in dynamic SQL other than what can be observed from the stored proc. Regardless, do two things:

a) Check what the data type of the column sex in tblstudents is. Is it an int or numeric type.

b) Instead of calling EXEC (@runsql), print out @runsql and execute that to see if you are able to execute it correctly from an SSMS window.

crazycat503
Starting Member

16 Posts

Posted - 2012-05-01 : 14:23:16

1st...there are other pms required...it is custom. Every parameter causes the error so it is why i just listed that one..

2. sex field is not int/numeric in db.

All guys!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 14:28:01

change
SET @limits=@limits + ' sex=' + CHAR(39) + @gender + CHAR(39)
to
SET @limits=@limits + ' sex=''' + @gender + ''''

and try

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

crazycat503
Starting Member

16 Posts

Posted - 2012-05-03 : 10:37:59

i tried that...not workin either.

All guys!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-03 : 13:18:41

quote:
Originally posted by visakh16

change
SET @limits=@limits + ' sex=' + CHAR(39) + @gender + CHAR(39)
to
SET @limits=@limits + ' sex=''' + @gender + ''''

I might be missing something, but why change from using the nice CHAR(39) method to mashing single-quotes together method?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources