return string from store procedure? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

return string from store procedure?

Author

Topic

pamyral_279
Posting Yak Master

143 Posts

Posted - 2006-08-14 : 06:41:30

How can i do ?
It's always display error : "Can' not convert nvarchar to int"
Thank you very much.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-14 : 06:45:21

its becuase you can not convert the varchar types of data to the integer type.

Either put '' in the where clause or make use of IsNumeric function.

Post some data so that we can get more idea about the problem you are facing.

Chirag

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-14 : 06:45:59

please show SP definition, and how you are using at. At a guess, your SP is returning a NVARCHAR, but the calling operation is trying to convert it into integer. This error may be in the SP itself, so you may need to include the table definitions when you give more info.

HTH

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-14 : 11:54:23

quote:
Originally posted by pamyral_279

How can i do ?
It's always display error : "Can' not convert nvarchar to int"
Thank you very much.

Do you think you have given enough information?
Post the code you used

Madhivanan

Failing to plan is Planning to fail

pamyral_279
Posting Yak Master

143 Posts

Posted - 2006-08-14 : 22:48:47

Thank all people :
My SP:
CREATE PROCEDURE AdminViewSearchNameTest
(
@sl bigint output,
@numberfail bigint output,
@numbersucc bigint output
)
AS

SET @sl = (SELECT COUNT(*) FROM tblSearchName)
SET @numberfail = (SELECT COUNT(*) FROM tblSearchName where fail=1)
SET @numbersucc = (SELECT COUNT(*) FROM tblSearchName where succ=1)

Declare @s1 bigint;
Set @s1=@numberfail+'#'+@numbersucc+@sl
print(@s1)
return @s1
GO

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-14 : 23:14:38

This line isn't going to work


Set @s1=@numberfail+'#'+@numbersucc+@sl

It's like saying this:
Set @s1 = 40 +'#'+60 + 200

What are you trying to do?

CODO ERGO SUM

pamyral_279
Posting Yak Master

143 Posts

Posted - 2006-08-14 : 23:45:04

Thank Michalel !
My purpose is display output like : 40#260 or something like :40#60#200 ....?
Help me !
Thanks you very much.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-15 : 02:02:32

you cannot use BigInt datatype to save values in the format you want to,
either make use of varchar or nvarchar type.


Declare @s1 varchar(10)
Set @s1=Convert(varchar(10),@numberfail)+'#'+
Convert(varchar(10),@numbersucc) + Convert(varchar(10),@sl)
print(@s1)

Chirag

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-08-15 : 02:42:01

ok, there are a number of problems with this stored procedure.

1. you define @sl as a bigint, yet you try to concatenate the '#' char to it as MVJ pointed out. This won't work.
2. you are trying to return the @sl value using RETURN. RETURN can only return numeric values, not character values. Look at using OUTPUT variables instead.

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources