sql server 2000 if i pass a stor

[ktucci]

sql server 2000

if i pass a stored proc a string of 1,2,3,4,5 for @var_view and p.preorder_postatus_id has an datatype of integer...how could i convert this variable to and integer so the query can process it

*btw...i am passing the string to the stored proc from coldfusion

--------------------------------

CREATE PROCEDURE [sp_qry_get_preorders_1]
@agentid int,
@var_view varchar(50)
AS
select p.preorder_postatus_id, p.preorder_agent_id,p.preorder_id, c.customer_name, preorder_descr, (convert(smalldatetime,preorder_dt)) as preorder_dt, tcs.postatus_name
from tbl_preorder p left join
tbl_customer c on p.preorder_customer_id = c.customer_id left join
tbl_postatus tcs on tcs.postatus_id = p.preorder_postatus_id
where 0=0
and p.preorder_agent_id = @agentid
and p.preorder_postatus_id in (@var_view)
GO

--------------------------


any help will be greatly appreciated

thanks in advance

keith

 

[SQLSister]

We do this kind of split in multiple places and thus have put in it a function which we call in the stored procedure.
Function is:
CREATE FUNCTION ParseWords
(@SearchPhrase nvarchar(255))

Returns @ParseWordsTable Table
(record nvarchar(50) not null
)

Begin

Declare @Length dec(6,2)
Declare @Record nvarchar(50)
Declare @WordMatch nvarchar(50)
Declare @WordCount int
Declare @Start smallint



Set @SearchPhrase = Ltrim(Rtrim(@SearchPhrase))
Set @Length = len(@SearchPhrase)

/*Check for multiple word search*/

Set @Start = 1


Set @WordCount = CHARINDEX (' ',@SearchPhrase,@Start)

If @WordCount <> 0
begin

While @WordCount < @Length
begin

If @WordCount <> 0
begin
Set @WordMatch = SUBSTRING(@SearchPhrase,@Start, @WordCount-@Start)

Insert into @ParseWordsTable (Record)
values(Ltrim(Rtrim(@WordMatch)))

Set @Start = @WordCount+1
Set @WordCount = CHARINDEX (' ',@SearchPhrase,@Start)
end
else
If @Start > 1
begin
Set @WordMatch = SUBSTRING(@SearchPhrase,@Start, cast(@Length as int)-@Start+1)

Insert into @ParseWordsTable (Record)
values(Ltrim(Rtrim(@WordMatch)))

Set @WordCount = @Length

end
end
End
else
begin
Set @WordMatch = @SearchPhrase
Insert into @ParseWordsTable (Record)
values(Ltrim(Rtrim(@WordMatch)))
end

Return

END



An example of how the stored procedure called the function is below:
Insert into #Service (Record)
Select record from parsewords(@ManufacturerID)

As you can see we always use space to separate our data input values but the Char index part could be modified to accept whatever you want. You can also modify to change the return values to integers in the function, but we use it for either integers or actual words, so we return it in a character datatype.

This should at least give you an idea of where to start.

 

[sunila7]

Hi,

I think u will have to do this with dyanmic query..... try this


CREATE PROCEDURE [sp_qry_get_preorders_1]
@agentid int,
@var_view varchar(50)
AS
Declare @SQL varchar(1000)

SET @SQL ='select p.preorder_postatus_id, p.preorder_agent_id,p.preorder_id, c.customer_name, preorder_descr, (convert(smalldatetime,preorder_dt)) as preorder_dt, tcs.postatus_name
from tbl_preorder p left join
tbl_customer c on p.preorder_customer_id = c.customer_id left join
tbl_postatus tcs on tcs.postatus_id = p.preorder_postatus_id
where 0=0
and p.preorder_agent_id = ' + convert(varchar,@agentid) +
' and p.preorder_postatus_id in (' + (@var_view) + ')'


Exec(@SQL)
GO

Hope it helps

Sunil

 

[ktucci]

thanks a million, the dynamic sql worked perfect

thanks

keith