sp help - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

sp help

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-11-15 : 10:59:04

I have this sp:
CREATE proc stsQuoteToOrdersList
@QuoteNumber varchar(20),
@OrderList varchar(100) output
as
begin
select @OrderList= Coalesce(@OrderList+',',' ') + CO_Number
from stsJobTracking where QuoteNumber = @QuoteNumber
return @OrderList
end
GO

When I tried to call it in QA using
DECLARE @Orderlist VARCHAR(100)
exec stsQuoteToOrdersList Q244255, @Orderlist
PRINT @Orderlist

I got:
Syntax error converting the varchar value ' 613977,613978,613979' to a column of data type int.

There is nowhere in the process that an int is involved.

What did I miss? Thanks!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-15 : 11:04:31

you have to convert the column as it is an int column

select @OrderList= Coalesce(@OrderList+',',' ') + convert(varchar,CO_Number)

Corey

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-11-15 : 11:24:13

Thank you Corey!

I got the return value I want.
Also, I needed to replace return @OrderList with Select @OrderList to make it work.

However, when I tried to include the sp in a select list, error says it is not a function. I guess I need a user defined functions instead of the sp.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-15 : 15:16:51

How were you trying to include it in a sp?
as columndata, or as a subquery?

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources