using text datatype in stored procedure

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)

using text datatype in stored procedure

Author

Topic

mrsaif
Starting Member

43 Posts

Posted - 2005-12-27 : 11:20:25

hi,
in my stored prcedure i have a string which contains Ids(comma seprated value) like this '1,2,3,4,5,612,12,4'. this string might be longer than 8000 character so i cannot use varchar or nvarchar as they limit to 8000 characters.
so what i decided to use is TEXT datatype. I can use len and charindex and substring functions to extract these ids one by one when using varchar data type. How can this be done in text datatype. what should i do to get ids indvidually like '1' '2' '3' .. etc. form this text type stored procedure variable? IS it possible or i should see som othere way to do that
if other why then what should be the best way pleas help.?

Muhammad Saifullah

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2005-12-27 : 11:32:52

Text data type is a bit different than char types

Go thru BOL
These functions and statements can be used with ntext, text, or image data.
Functions
----------
DATALENGTH
PATINDEX
SUBSTRING
TEXTPTR
TEXTVALID

Statements
----------
READTEXT
SET TEXTSIZE
UPDATETEXT
WRITETEXT

Instead of Len function, u have to use Datalength and so on.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-12-27 : 11:38:57

also look up openXml in BOL.
that way you don't have to parse anything.

and by the way... 8000+ CSVed ID's is a lot.
What are you tring to do?

Go with the flow & have fun! Else fight the flow

mrsaif
Starting Member

43 Posts

Posted - 2005-12-28 : 01:26:08

thanks all i have use the following scnerio in my stroed procdure to get the value--
it worked fine when string lenght is 4987. please see the stored procedure and tell me will it work when @strLocIDs lenght is more than 8000 characters.
CREATE PROCEDURE sp_Create_Shops
@intProgramId numeric,
@strShopTitle varchar(200),
@strLocIDs text,
@dtS_startDate datetime,
@dtE_endDate datetime,
@fltS_Pay decimal(12,4),
@fltS_expence decimal(12,4) =null,
@fltSpecialExpense decimal(12,4)=null,
@strSpeciaexpencesDesc varchar(500)=null,
@fltS_bouns decimal(12,4)=null,
@strhours varchar(50)=null,
@strShopComments varchar(500)=null,
--@shopOrder numeric,
@ShopStatusID smallint,
@dtPostDate datetime,
@blnListin bit,
@blnAssignDefaultSchedler bit
AS

declare @p int
declare @p1 int
declare @intlocID numeric
declare @strlocID varchar(40)
declare @intFreq numeric
DECLARE @intSchedulerID NUMERIC
DECLARE @inMspId NUMERIC
DECLARE @fltfreq numeric

set @p=1
set @p1=1

begin transaction

while @p < datalength(@strLocIDs)
begin
set @p1=charindex(',',@strLocIDs,@p)
set @strlocID = substring(@strLocIDs,@p,@p1-@p)
set @p=@p1+1
set @p1=@p1+1
set @intlocID=cast(@strlocID as numeric)

set @fltFreq=-1
select @fltFreq=frequency from tbl_client_locations where location_id=@intLocId
if @fltFreq =-1
begin
rollback
select 'FAILD','Invalid Location ID'
return
end
declare @i int
set @i = 1
while @i <= @fltFreq
begin
insert into tbl_shops(program_id,
location_id,
shop_title,
shop_start_date,
shop_end_date,
shoppers_pay,
shop_expense,
special_expense,
special_expense_desc,
shop_bonus,
hours,
Shop_comments,
shop_order,
shop_status_id,
post_date,
payment_status_id,
job_board_listing)
values( @intProgramId,
@intlocID,
@strShopTitle,
@dtS_startDate ,
@dtE_endDate ,
@fltS_Pay,
@fltS_expence ,
@fltSpecialExpense ,
@strSpeciaexpencesDesc ,
@fltS_bouns ,
@strhours ,
@strShopComments ,
@i ,
@ShopStatusID,
@dtPostDate,
1,
@blnListin)
set @i=@i+1
if @@identity=0
begin
select 'FAILD','FAILD TO INSERT INTO TBALE TBL_SHOPS'
rollback
return
end
set @intSchedulerID = -1
Set @inMspId = -1
select @intSchedulerID=msp_employee_id, @inMspId=msp_id from tbl_msp_client_locations where location_id = @intlocID
if (@intSchedulerID <> -1 and @blnAssignDefaultSchedler =1)
begin
insert into tbl_scheduler_shop(shop_id, msp_employee_id, msp_id) values(@@identity ,@intSchedulerID,@inMspId)
if @@error <> 0
begin

select 'FAILD','Error in inserting tbl_scheduler_shop'
rollback
return
end
end
end
end
IF @@ERROR<>0
begin
rollback
select 'FAILD','An Error Occoured'
end
else
begin
select 'SUCCESS'
commit
end
GO

Muhammad Saifullah

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-28 : 01:35:18

Why do you want to pass CSV and split them?
Make sure that table is properly normalised. Refer this http://www.datamodel.org/NormalizationRules.html

To split the CSV, refer
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Also In this sticky topic, search for 8000
http://sqlteam.com/forums/topic.asp?TOPIC_ID=55210

Madhivanan

Failing to plan is Planning to fail

mrsaif
Starting Member

43 Posts

Posted - 2005-12-28 : 08:13:38

thank you for you reply Madhivanan
in my application user can select loction Ids to post shops on that locaitons, depending on the location frequency. user can select tens of thousands of locations. to speed up my application I conctenate location Ids with the comma, as seprater, and send it to the stored proceure. there I get each location Id and perform desired Operation. I do this because sql server engine is much faster. and also inserting in shops table for each location ids( individually) in application takes much time(opening db connection and closing db connection). Please comment that I am thinking right or wronge, or what should be the best way to do that. one way is that istead of using text datatype. i use sp_xml_generateDocument to get the locaitonIds string. but i think it also cost much.

Muhammad Saifullah

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources