is transaction safe within store procedure?

Hello,
Is it safe to do this in a store procedure? Please share your comments or
suggestions. Thanks!
create procedure PerformAtomicDataCheck
@.ObjId varchar(100),
as
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
// need to perform an atomic data operation here, might run into error
etc...
// if there is fatal error, would it leave the transaction around?
COMMIT TRANSACTIONFirst off if you are only doing a single operation (One insert, update or
delete ) regardless of the number of rows affected it will be an atomic
operation without adding BEGIN TRAN or changing the Isolation level. If you
do issue a Begin Tran it is up to you to either commit it or roll it back.
The only exception is if you use SET XACT_ABORT. If you get an error inside
a transaction and it is severe enough then you may not be able to address it
in the sp itself and must clean it up in the section that called the sp.
Errors above 15 severity usually abort the batch but do not commit or
rollback open transactions.
Andrew J. Kelly SQL MVP
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:eCZxo$BLFHA.2468@.tk2msftngp13.phx.gbl...
> Hello,
> Is it safe to do this in a store procedure? Please share your comments or
> suggestions. Thanks!
> create procedure PerformAtomicDataCheck
> @.ObjId varchar(100),
> as
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> BEGIN TRANSACTION
> // need to perform an atomic data operation here, might run into error
> etc...
> // if there is fatal error, would it leave the transaction around?
> COMMIT TRANSACTION
>|||Within SP, if you start a transation using Begin Transaction, you must eithe
r
execute Rollback, or COmmit, or you will leave an open transaction on your
server, along with all the locks it hasa created...
"Zeng" wrote:

> Hello,
> Is it safe to do this in a store procedure? Please share your comments or
> suggestions. Thanks!
> create procedure PerformAtomicDataCheck
> @.ObjId varchar(100),
> as
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> BEGIN TRANSACTION
> // need to perform an atomic data operation here, might run into error
> etc...
> // if there is fatal error, would it leave the transaction around?
> COMMIT TRANSACTION
>
>|||Transaction control can be used if it is necessary like you are going to do
more thatn one operation in the same Procedure. So, either all of its data
modifications are performed, or none of them is performed. Refer (ACID) BOL.
You can check for error at the end of the procedure
IF @.@.Error > 0
ROLLBACK TRANSACTION
Else
Commit Transaction
Thanks
Baiju
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:eCZxo$BLFHA.2468@.tk2msftngp13.phx.gbl...
> Hello,
> Is it safe to do this in a store procedure? Please share your comments or
> suggestions. Thanks!
> create procedure PerformAtomicDataCheck
> @.ObjId varchar(100),
> as
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> BEGIN TRANSACTION
> // need to perform an atomic data operation here, might run into error
> etc...
> // if there is fatal error, would it leave the transaction around?
> COMMIT TRANSACTION
>|||Baiju wrote:
> Transaction control can be used if it is necessary like you are going
> to do more thatn one operation in the same Procedure. So, either all
> of its data modifications are performed, or none of them is
> performed. Refer (ACID) BOL.
> You can check for error at the end of the procedure
> IF @.@.Error > 0
> ROLLBACK TRANSACTION
> Else
> Commit Transaction
> Thanks
> Baiju
>
To be clear, you need to check @.@.ERROR after every SQL statement since
it's value is reset after each successful call.
David Gugick
Imceda Software
www.imceda.com

Is this Code right

Hi,

This is my dataset for a report. the reason i am creating this table is because i want to split the result set of the store procedure rpt_Selectinvestments, so that i can display the results of the table thats InvestmentName evenly.

The first time i create this table its fine but the next time i try to run this query i get an error saying that the table or object already exist is the database.

Create table #TmpResults

( rowid int IDENTITY,

PlanId int,

PlanName varchar(200),

InvestmentName varchar(500),

InvestmentType char(1),

IsPortfolioFundOnly bit,

InvestmentId int)

Declare @.PlanId int

set @.PlanId = 682

Insert Into #TmpResults

Exec ICCStatements..rpt_SelectInvestments @.PlanId

I am also creating a Internal parameter called Split which is an integer which has the following expression

select split = case when max(rowid)%2 = 1 then max(rowid)/2) + 1 else max(rowid)/2 end from #TmpResults.

but when i try to run my report i am getting an error saying that "Split doesnt have the expected parameter type.

Some one please please help me

So what can i do in order to by pass it.

Regards,

Karen

Karenros wrote:

The first time i create this table its fine but the next time i try to run this query i get an error saying that the table or object already exist is the database.

Create table #TmpResults

( rowid int IDENTITY,

PlanId int,

PlanName varchar(200),

InvestmentName varchar(500),

InvestmentType char(1),

IsPortfolioFundOnly bit,

InvestmentId int)

Declare @.PlanId int

set @.PlanId = 682

Insert Into #TmpResults

Exec ICCStatements..rpt_SelectInvestments @.PlanId

The first time you run this it is creating a table called TmpResults. The second time you run this, it tries to create a table called TmpResults, but it looks in your database and finds that there is already one there, thus the error.

|||

so what should i do.. All i am trying to do is to split resultset into half so that i can display them in 2 tables.

Can u please help me out.

|||

how can i take the results of the sproc and insert it into a table? Is it possible to do it...

|||

Have you tried using functions instead?

You can create a function that returns a table and I would think you could insert that table into another table. I typically just select from it though instead of inserting it.

|||

can u please give me an example of that. or do u mean write a custom code to do it?

Regards

Karen

|||

Code Snippet

USE [database]

GO

/****** Object: UserDefinedFunction [dbo].[func2] Script Date: 08/03/2007 10:29:33 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[func2]

(

@.StartDate varchar(100),

@.EndDate varchar(100)

)

RETURNS TABLE

AS

RETURN (SELECT SUM(TOTAL) AS TOTAL FROM MYTABLE WHERE StartDate = @.StartDate AND EndDate = @.EndDate)

Then you could put this in a stored procedure:

Code Snippet

select SUM(TOTAL) AS TOTAL from dbo.func2('07/01/2006', '07/31/2006')

As you can see, a table is returned from func2 and you can select from it.

In your case, you would want to try to use that table that is returned and insert the first half into one table and the second half into another table.

|||

Greg,

Thanks for ur answer. This is what i have done right now, I have created a functions which is a follows

ALTER Function [dbo].[Func2]

(

@.PlanId int

)

RETURNS Table

AS

Return (Select Count(*) as RowId from PlanFund Where PlanId = @.PlanId)

and this is the sproc that i am using to poplulate my report it is as follows

ALTER PROCEDURE [dbo].[rpt_SelectInvestments] (@.PlanId AS integer)

AS

-- History

-- 08/17/2004 svanpatter/JSWCO initial version created

-- 08/30/2004 svanpatter/JSWCO add

-- Select available funds

SELECT

[ClientPlan].PlanId,

[ClientPlan].PlanName,

-- Fund.[FundName] AS InvestmentName,

CASE

WHEN

PlanFund.PlanFundDisplayName IS NULL

THEN

Fund.ShortName

ELSE PlanFund.PlanFundDisplayName

END InvestmentName,

'F' AS InvestmentType,

--EmpIncrementPct =

--CASE

-- WHEN EmpIncrementPct IS NULL THEN '0'

-- WHEN EmpIncrementPct = 0 THEN EmpIncrementPctOther

-- ELSE CAST( CAST(EmpIncrementPct AS integer) AS varchar(50))

--END,

--PlanFund.PlanId As InvestmentID

PlanFund.IsPortfolioFundOnly,

PlanFund.FundDisplayOrder As InvestmentID

FROM

[ClientPlan]

--INNER JOIN PlanAllocation ON [ClientPlan].PlanId = [PlanAllocation].PlanId

INNER JOIN PlanFund ON [ClientPlan].PlanId = PlanFund.PlanId And IsPortfolioFundOnly = "0"

INNER JOIN Fund ON PlanFund.FundId = Fund.FundId

--INNER JOIN Abbrev ON Lipper.LipperID = Abbrev.LipperID

WHERE

[ClientPlan].PlanId = @.PlanId

UNION

-- Select Portfolios

SELECT

[ClientPlan].PlanId,

[ClientPlan].PlanName,

PlanPortfolio.PortfolioName AS InvestmentName,

'P' AS InvestmentType,

--EmpIncrementPct =

-- CASE

-- WHEN EmpIncrementPct IS NULL THEN '0'

-- WHEN EmpIncrementPct = 0 THEN EmpIncrementPctOther

-- ELSE CAST( CAST(EmpIncrementPct AS integer) AS varchar(50))

-- END,

NULL,

PlanPortfolio.PortfolioId As InvestmentID

FROM [ClientPlan]

INNER JOIN PlanPortfolio ON [ClientPlan].PlanId = PlanPortfolio.PlanId

--INNER JOIN PlanAllocation ON [ClientPlan].PlanId = [PlanAllocation].PlanId

WHERE

[ClientPlan].PlanId = @.PlanId

ORDER BY

InvestmentType, InvestmentID

Select RowId from dbo.Func2(@.PlanId)

As u can see at the end of the sproc i am calling the function...

and when i run this sproc i get 2 tables one which returns the each record and the other one which returns the count for the other table. like suppose if i have 24 records... Select RowId returns 24.

but when i run this sproc as a dataset in the report i dont the select Rowid part in the result set. why is that?

any help will be appreciated

|||

Karenros wrote:

but when i run this sproc as a dataset in the report i dont the select Rowid part in the result set. why is that?

Are you using rpt_SelectInvestments as the sproc in your report?

If so, then you need to incorporate "select RowId from dbo.Func2(@.PlanId)" into your sproc. Right now you have it as two separate select statements.

|||

ok i dont think the function i created will work... so is there a way that i can put the results of the sproc in a parameter or a variable in the report ?

For ex. in my sproc i am returning the @.@.RowCount, is it Possible to access this @.@.RowCount in the report?

Regards

Karen

|||

Karenros wrote:

For ex. in my sproc i am returning the @.@.RowCount, is it Possible to access this @.@.RowCount in the report?

For the first time when you call your procedure from report with all valid parameter value. It will create list of all parameter for your report. which are useed to call the procedure next time. And you can modify parameter from menu Report - > Report parameter...

If you have parameter in stored proc with output type. It will create that also as report parameter.. and you can use them on report whereever you want whenever you want.

|||

Hi its Me,

Thanks for your answer..

So in my sproc if i do

Create proc [dbo].[procname]

@.PlanId as integer,

@.Count int output

AS

Select

<whatever> i want

fromm

tablename

Union

Select statment

where PlanId = @.PlanId

and then at the end i am setting

SEt @.Count = @.@.RowCount

Return @.count.

When i run the sproc it asks me a value for @.Count...

What should i do..

Regards

Karen

|||

select blank or null for output parameter. or just pass any value.. that doesnt make any difference to your proc. as you are not using that parameter in your proc..

|||

thanks for ur answer... But how can i get value of the output parameter in the report?

Regards,

Karen

|||

In expression just write :

=Parameters!Count.Value

And you will get the value of the parameter..

Is this Code right

Hi,

This is my dataset for a report. the reason i am creating this table is because i want to split the result set of the store procedure rpt_Selectinvestments, so that i can display the results of the table thats InvestmentName evenly.

The first time i create this table its fine but the next time i try to run this query i get an error saying that the table or object already exist is the database.

Create table #TmpResults

( rowid int IDENTITY,

PlanId int,

PlanName varchar(200),

InvestmentName varchar(500),

InvestmentType char(1),

IsPortfolioFundOnly bit,

InvestmentId int)

Declare @.PlanId int

set @.PlanId = 682

Insert Into #TmpResults

Exec ICCStatements..rpt_SelectInvestments @.PlanId

I am also creating a Internal parameter called Split which is an integer which has the following expression

select split = case when max(rowid)%2 = 1 then max(rowid)/2) + 1 else max(rowid)/2 end from #TmpResults.

but when i try to run my report i am getting an error saying that "Split doesnt have the expected parameter type.

Some one please please help me

So what can i do in order to by pass it.

Regards,

Karen

Karenros wrote:

The first time i create this table its fine but the next time i try to run this query i get an error saying that the table or object already exist is the database.

Create table #TmpResults

( rowid int IDENTITY,

PlanId int,

PlanName varchar(200),

InvestmentName varchar(500),

InvestmentType char(1),

IsPortfolioFundOnly bit,

InvestmentId int)

Declare @.PlanId int

set @.PlanId = 682

Insert Into #TmpResults

Exec ICCStatements..rpt_SelectInvestments @.PlanId

The first time you run this it is creating a table called TmpResults. The second time you run this, it tries to create a table called TmpResults, but it looks in your database and finds that there is already one there, thus the error.

|||

so what should i do.. All i am trying to do is to split resultset into half so that i can display them in 2 tables.

Can u please help me out.

|||

how can i take the results of the sproc and insert it into a table? Is it possible to do it...

|||

Have you tried using functions instead?

You can create a function that returns a table and I would think you could insert that table into another table. I typically just select from it though instead of inserting it.

|||

can u please give me an example of that. or do u mean write a custom code to do it?

Regards

Karen

|||

Code Snippet

USE [database]

GO

/****** Object: UserDefinedFunction [dbo].[func2] Script Date: 08/03/2007 10:29:33 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[func2]

(

@.StartDate varchar(100),

@.EndDate varchar(100)

)

RETURNS TABLE

AS

RETURN (SELECT SUM(TOTAL) AS TOTAL FROM MYTABLE WHERE StartDate = @.StartDate AND EndDate = @.EndDate)

Then you could put this in a stored procedure:

Code Snippet

select SUM(TOTAL) AS TOTAL from dbo.func2('07/01/2006', '07/31/2006')

As you can see, a table is returned from func2 and you can select from it.

In your case, you would want to try to use that table that is returned and insert the first half into one table and the second half into another table.

|||

Greg,

Thanks for ur answer. This is what i have done right now, I have created a functions which is a follows

ALTER Function [dbo].[Func2]

(

@.PlanId int

)

RETURNS Table

AS

Return (Select Count(*) as RowId from PlanFund Where PlanId = @.PlanId)

and this is the sproc that i am using to poplulate my report it is as follows

ALTER PROCEDURE [dbo].[rpt_SelectInvestments] (@.PlanId AS integer)

AS

-- History

-- 08/17/2004 svanpatter/JSWCO initial version created

-- 08/30/2004 svanpatter/JSWCO add

-- Select available funds

SELECT

[ClientPlan].PlanId,

[ClientPlan].PlanName,

-- Fund.[FundName] AS InvestmentName,

CASE

WHEN

PlanFund.PlanFundDisplayName IS NULL

THEN

Fund.ShortName

ELSE PlanFund.PlanFundDisplayName

END InvestmentName,

'F' AS InvestmentType,

--EmpIncrementPct =

--CASE

-- WHEN EmpIncrementPct IS NULL THEN '0'

-- WHEN EmpIncrementPct = 0 THEN EmpIncrementPctOther

-- ELSE CAST( CAST(EmpIncrementPct AS integer) AS varchar(50))

--END,

--PlanFund.PlanId As InvestmentID

PlanFund.IsPortfolioFundOnly,

PlanFund.FundDisplayOrder As InvestmentID

FROM

[ClientPlan]

--INNER JOIN PlanAllocation ON [ClientPlan].PlanId = [PlanAllocation].PlanId

INNER JOIN PlanFund ON [ClientPlan].PlanId = PlanFund.PlanId And IsPortfolioFundOnly = "0"

INNER JOIN Fund ON PlanFund.FundId = Fund.FundId

--INNER JOIN Abbrev ON Lipper.LipperID = Abbrev.LipperID

WHERE

[ClientPlan].PlanId = @.PlanId

UNION

-- Select Portfolios

SELECT

[ClientPlan].PlanId,

[ClientPlan].PlanName,

PlanPortfolio.PortfolioName AS InvestmentName,

'P' AS InvestmentType,

--EmpIncrementPct =

-- CASE

-- WHEN EmpIncrementPct IS NULL THEN '0'

-- WHEN EmpIncrementPct = 0 THEN EmpIncrementPctOther

-- ELSE CAST( CAST(EmpIncrementPct AS integer) AS varchar(50))

-- END,

NULL,

PlanPortfolio.PortfolioId As InvestmentID

FROM [ClientPlan]

INNER JOIN PlanPortfolio ON [ClientPlan].PlanId = PlanPortfolio.PlanId

--INNER JOIN PlanAllocation ON [ClientPlan].PlanId = [PlanAllocation].PlanId

WHERE

[ClientPlan].PlanId = @.PlanId

ORDER BY

InvestmentType, InvestmentID

Select RowId from dbo.Func2(@.PlanId)

As u can see at the end of the sproc i am calling the function...

and when i run this sproc i get 2 tables one which returns the each record and the other one which returns the count for the other table. like suppose if i have 24 records... Select RowId returns 24.

but when i run this sproc as a dataset in the report i dont the select Rowid part in the result set. why is that?

any help will be appreciated

|||

Karenros wrote:

but when i run this sproc as a dataset in the report i dont the select Rowid part in the result set. why is that?

Are you using rpt_SelectInvestments as the sproc in your report?

If so, then you need to incorporate "select RowId from dbo.Func2(@.PlanId)" into your sproc. Right now you have it as two separate select statements.

|||

ok i dont think the function i created will work... so is there a way that i can put the results of the sproc in a parameter or a variable in the report ?

For ex. in my sproc i am returning the @.@.RowCount, is it Possible to access this @.@.RowCount in the report?

Regards

Karen

|||

Karenros wrote:

For ex. in my sproc i am returning the @.@.RowCount, is it Possible to access this @.@.RowCount in the report?

For the first time when you call your procedure from report with all valid parameter value. It will create list of all parameter for your report. which are useed to call the procedure next time. And you can modify parameter from menu Report - > Report parameter...

If you have parameter in stored proc with output type. It will create that also as report parameter.. and you can use them on report whereever you want whenever you want.

|||

Hi its Me,

Thanks for your answer..

So in my sproc if i do

Create proc [dbo].[procname]

@.PlanId as integer,

@.Count int output

AS

Select

<whatever> i want

fromm

tablename

Union

Select statment

where PlanId = @.PlanId

and then at the end i am setting

SEt @.Count = @.@.RowCount

Return @.count.

When i run the sproc it asks me a value for @.Count...

What should i do..

Regards

Karen

|||

select blank or null for output parameter. or just pass any value.. that doesnt make any difference to your proc. as you are not using that parameter in your proc..

|||

thanks for ur answer... But how can i get value of the output parameter in the report?

Regards,

Karen

|||

In expression just write :

=Parameters!Count.Value

And you will get the value of the parameter..

sql

Is there any way better than XP_CMDSHELL?

Hi ..
I want to Write in files or read from files
for example i have My_File.txt . i need a syntax and i want to call this syntax in my Store procedure and this syntax write forexample " Hello Word " in My_File.txt .
and i want another syntax that read from My_File.txt forexample "Word" from My_File.txt . what are those syntaxes do that ??

Is there any way better than XP_CMDSHELL for writing in or reading from MyFile.txt ??

thanksYou could BCP the SQL file into a temporary table and then execute the code through dynamic SQL, but that is pretty round-a-bout.

Is there an equivalent to an MSAccess IIF statement when creating Store Procs

I'm creating a stored proc and need to use some sort of IF or IIF statement. Is theresome sort of equivalent statement i can use?T-SQL supports an IF and CASE statment. you can read up on both in Books Online.

IF...ELSE
Imposes conditions on the execution of a Transact-SQL statement. The Transact-SQL statement following an IF keyword and its condition is executed if the condition is satisfied (when the Boolean expression returns TRUE). The optional ELSE keyword introduces an alternate Transact-SQL statement that is executed when the IF condition is not satisfied (when the Boolean expression returns FALSE).

Syntax
IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]

CASE
Evaluates a list of conditions and returns one of multiple possible result expressions.

CASE has two formats:

The simple CASE function compares an expression to a set of simple expressions to determine the result.

The searched CASE function evaluates a set of Boolean expressions to determine the result.
Both formats support an optional ELSE argument.

Syntax
Simple CASE function:

CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

Searched CASE function:

CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END|||IF(<CHECK CONDITION>)
BEGIN
<CODE>
END
ELSE
BEGIN
< CODE>
END|||IIF doesn't exists in SQL but you can always use the old fashion way:

IF <your_condition>
BEGIN
:
:
END
ELSE
BEGIN
:
:
END

Originally posted by Sammy_S
I'm creating a stored proc and need to use some sort of IF or IIF statement. Is theresome sort of equivalent statement i can use?