Usage of count() function inside sql Transaction

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

Usage of count() function inside sql Transaction

Author

Topic

pradeepmv
Starting Member

27 Posts

Posted - 2007-05-09 : 07:46:12

Please find my second post in this thread.

Can anyone help me in sorting out the problem and let me know what might be the reason.

Thanks & Regards

Pradeep M V

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-09 : 07:50:07

Not clear exactly what you want. Can you explain bit more clearly with sample data and expected output to support your question?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pradeepmv
Starting Member

27 Posts

Posted - 2007-05-10 : 02:24:44

Hi

I'm using a insert statement which is used inside a BEGIN/COMMIT Transaction block.It is a set based query that inserts a set of data.

EG:

...
BEGIN TRANSACTION

           INSERT INTO CL40104
(SYEAR,SMONTH,CUSTNMBR,CPTYPE,CPTRXTYPE,CPTRXSOURCE,ORTRXSRC,
	COMMENT_4,CLFUNCSALAMNT,PTRATIO,CPPOINTS,CPPOSTED)
	SELECT @YEAR,@PERIODID,CASE WHEN g.CUSTNMBR IS NULL THEN h.CUSTNMBR ELSE g.CUSTNMBR END,
@CPTYPE,@CPTRXTYPE,dbo.clGetNextTrxSource(@CPTYPE,@CPTRXTYPE,@PERIODID,@YEAR),'SOP',
	convert(varchar(24),getdate()),	0,@PTRATIO,round((isnull(g.CPPOINTS,0) -
isnull(h.CPPOINTS,0)),0),0 FROM [Joins of four tables] 

        SELECT @count=count(1)  FROM CL40104 WHERE SYEAR=@YEAR AND SMONTH=@PERIODID 
	AND CPTYPE=@CPTYPE AND CPTRXTYPE=@CPTRXTYPE
		
	UPDATE CL40106 SET CL_Trx_Source_ID=CL_Trx_Source_ID + @count WHERE CPTYPE=@CPTYPE AND CPTRXTYPE=@CPTRXTYPE

if @@error<>0
    ROLLBACK TRANSACTION
else
    COMMIT TRANSACTION

Please find the call to dbo.clGetNextTrxSource in above code.Its called for each record insertion to CL40104.

The function clGetNextTrxSource has below code which returns @NextTrxSource as below

SELECT @CLTrxSourceID=CL_Trx_Source_ID,@CLTRXSRCIDABR=CLTRXSRCIDABR FROM
CL40106 WHERE CPTYPE=@CPTYPE AND CPTRXTYPE=@CPTRXTYPE

	SELECT @CLTrxSourceID=count(1) + @CLTrxSourceID FROM CL40104 WHERE SYEAR=@YEAR AND SMONTH=@PERIODID
		AND CPTYPE=@CPTYPE AND CPTRXTYPE=@CPTRXTYPE

	SET @i=len(LTRIM(RTRIM(STR(@CLTrxSourceID))))
	SELECT @string	CASE @i
		WHEN 1 THEN '000000000'
		WHEN 2 THEN '00000000'
		WHEN 3 THEN '0000000'
		WHEN 4 THEN '000000'
		WHEN 5 THEN '00000'
		WHEN 6 THEN '0000'
		WHEN 7 THEN '000'
		WHEN 8 THEN '00'
		WHEN 9 THEN '0'
	END

	SET @NextTrxSource=@CLTRXSRCIDABR + LTRIM(RTRIM(@string)) +
LTRIM(RTRIM(STR(@CLTrxSourceID)))

RETURN(@NextTrxSource)

@NextTrxSource looks like "MRE0000000011"

Is there anything wrong in the above statements?

Will @NextTrxSource returns a unique value for each record?

In sql instance "Latin1_General_BIN" is returning but not in sql instance "SQL_Latin1_General_CP1_CI_AS".

Is this have anything to do with Transaction Isolation level?

I tried using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in "SQL_Latin1_General_CP1_CI_AS" instance & SET TRANSACTION ISOLATION LEVEL READ COMMITTED in "Latin1_General_BIN" instance before BEGIN Transaction but doesn't make any difference.

How can we find the default isolation level and also change the same?

Thanks & Regards

Pradeep M V

pradeepmv
Starting Member

27 Posts

Posted - 2007-05-10 : 06:14:16

WITH NOLOCK also didn't worked.

I was using the count in function because we cannot use update satement inside a sql function. In above example i cann't use CL40106 table update in function.

Thanks & Regards

Pradeep M V

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-10 : 06:22:10

look at this link and post accordingly

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Chirag

http://chirikworld.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-10 : 06:59:18

See if you need this

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57069

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-10 : 07:47:12

Still you didnt see my reply

Madhivanan

Failing to plan is Planning to fail

pradeepmv
Starting Member

27 Posts

Posted - 2007-05-10 : 08:06:14

Hi Madhivanam

My function is returning unique value with above code.I'm calling the function inside a insert statement which inserts a set of data at a time.The table only saves abreviation in one field and integer count in another which i concatenating using case statement.

Now the problem is its not working in another sql instance.
Do you have any idea of same?

Thanks & Regards

Pradeep M V

pradeepmv
Starting Member

27 Posts

Posted - 2007-05-10 : 08:20:24

Instead of case statement i can replace with below code


set @NextTrxSource=@CLTRXSRCIDABR+'0000000000'
select left(@NextTrxSource,3)+right('000000000'+cast(max(substring(@NextTrxSource,4,len(@NextTrxSource))+@CLTrxSourceID) as varchar),10)

Thanks & Regards

Pradeep M V

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-11 : 03:48:55

Yes you can. Also you need to consider other methods that I specified in the link

Madhivanan

Failing to plan is Planning to fail

pradeepmv
Starting Member

27 Posts

Posted - 2007-05-11 : 05:19:54

Do you have any idea why the above code is working in some system and not in others?

Thanks & Regards

Pradeep M V

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-11 : 05:30:57

in other systems did you get any error?

Madhivanan

Failing to plan is Planning to fail

pradeepmv
Starting Member

27 Posts

Posted - 2007-05-11 : 05:42:28

Nope.No error.Just inserting same value for all record in single execution.

Thanks & Regards

Pradeep M V

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-11 : 05:48:28

your function will return the same data for each row.

read this article to see if it gives you some new ideas:
http://www.sqlteam.com/item.asp?ItemID=26939

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

pradeepmv
Starting Member

27 Posts

Posted - 2007-05-11 : 05:58:33

quote:
Originally posted by spirit1

your function will return the same data for each row.

read this article to see if it gives you some new ideas:
http://www.sqlteam.com/item.asp?ItemID=26939

SELECT @CLTrxSourceID=count(1) + @CLTrxSourceID FROM CL40104 WHERE SYEAR=@YEAR AND SMONTH=@PERIODID
		AND CPTYPE=@CPTYPE AND CPTRXTYPE=@CPTRXTYPE

In above statement count(1) of table CL40104 will be diferent after each insert.So what is the reason for your comments? Please share your thoughts so that i can undersatnd the problem in it.

Thanks & Regards

Pradeep M V

pradeepmv
Starting Member

27 Posts

Posted - 2007-05-11 : 06:09:48

I'm getteing an error saying "[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification" when i try to debug a store procedure.
The Store Procedure parameters are as below.

PROCEDURE clCreateUnpostedTrxEarnType 
(
@SERIES int,
@PERIODID int,
@YEAR int,
@PTRATIO float,
@CPTYPE int,
@CPTRXTYPE int,
@Startdate datetime,
@Enddate datetime,
@O_iErrorState int = NULL  output 
)

I'm passing the values as below

3, 8, 2007, 3, 1, 3, '2007.08.01', '2007.08.31', Null

I tried passing '20070801', '20070831' & 2007.08.01, 2007.08.31 for date.

How should i fix this issue to debug for above issue?

Thanks & Regards

Pradeep M V

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-11 : 06:10:04

yes it will be different after each insert.
i thought you were talking about values in more than 1 row a single insert.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

pradeepmv
Starting Member

27 Posts

Posted - 2007-05-11 : 06:18:21

I'm talking about more than 1 row a single insert itself.In that case itself,the profiler shows that the function is called for each record.Which means count should be different each time.What do you think?

Thanks & Regards

Pradeep M V

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-11 : 07:09:02

please provide relevant create table statement, sampla data and desired results, because that's the only way to avoid confusion.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-14 : 06:04:20

Dont edit your previous topic
Post that as new reply

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources