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 possible?

I have a procedure that uses a cursor. I want to use this procedure to build a table based on the cursor. How can I dynamically generate a table the will contain the results. I plan on executing this procedure to populate this table every week.
Thanks...create procedure...

declare my_cursor cursor for
select ...

open my_cursor
fetch next ... into <variables list>

while @.@.fetch_status = 0 begin
insert <table_name> values (<variables list>)
fetch next ... into ...
end

close my_cursor
deallocate my_cursor

returnsql

Is this possible?

I have a stored procedure and I want to do something like:
INSERT INTO @.Table
(Field1, Field2, Field3, ...FieldN)
SELECT sum(a) AS Field1, sum(b) AS Field2, sum(c) AS Field3
FROM @.Table2
WHERE n is null
SELECT sum(a) AS Field4, sum(b) AS Field5, sum(c) AS Field6
FROM @.Table2
WHERE x < 50
SELECT sum(a) AS Field7, sum(b) AS Field8, sum(c) AS Field9
FROM @.Table2
WHERE x >= 50
Can I somehow do this? Since I couldn't return the result sets from my
3 selects, I combined them into one result set.
Right now I have a giant UPDATE statement, but it seems really
unwieldy...it's something like:
UPDATE @.Table
SET Field1 = SELECT sum(a) FROM @.Table2 WHERE n is null,
SET Field2 = SELECT sum(a) FROM @.Table2 WHERE n is null,
SET Field3 = SELECT sum(a) FROM @.Table2 WHERE n is null,
SET Field4 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
SET Field5 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
SET Field6 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
SET Field7 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
SET Field8 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
SET Field9 = SELECT sum(a) FROM @.Table2 WHERE x >= 50
There has to be a better way. (Can you tell I only half know what I'm
doing?)
Thank you!union your selects together
Confused wrote:
> I have a stored procedure and I want to do something like:
> INSERT INTO @.Table
> (Field1, Field2, Field3, ...FieldN)
> SELECT sum(a) AS Field1, sum(b) AS Field2, sum(c) AS Field3
> FROM @.Table2
> WHERE n is null
> SELECT sum(a) AS Field4, sum(b) AS Field5, sum(c) AS Field6
> FROM @.Table2
> WHERE x < 50
> SELECT sum(a) AS Field7, sum(b) AS Field8, sum(c) AS Field9
> FROM @.Table2
> WHERE x >= 50
> Can I somehow do this? Since I couldn't return the result sets from my
> 3 selects, I combined them into one result set.
> Right now I have a giant UPDATE statement, but it seems really
> unwieldy...it's something like:
> UPDATE @.Table
> SET Field1 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field2 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field3 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field4 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field5 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field6 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field7 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field8 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field9 = SELECT sum(a) FROM @.Table2 WHERE x >= 50
> There has to be a better way. (Can you tell I only half know what I'm
> doing?)
> Thank you!
>|||Do:
SELECT SUM( CASE WHEN n IS NULL THEN a END ) AS "Field1",
SUM( CASE WHEN n IS NULL THEN b END ) AS "Field2",
SUM( CASE WHEN n IS NULL THEN c END ) AS "Field3",
SUM( CASE WHEN x < 50 THEN a END ) AS "Field4",
SUM( CASE WHEN x < 50 THEN b END ) AS "Field5",
SUM( CASE WHEN x < 50 THEN c END ) AS "Field6",
SUM( CASE WHEN x >= 50 THEN a END ) AS "Field7",
SUM( CASE WHEN x >= 50 THEN b END ) AS "Field8",
SUM( CASE WHEN x >= 50 THEN c END ) AS "Field9"
FROM Table2 ;
Anith|||"Confused" <cschanz@.gmail.com> wrote in message
news:1135287913.453858.252290@.g49g2000cwa.googlegroups.com...
>I have a stored procedure and I want to do something like:
> INSERT INTO @.Table
> (Field1, Field2, Field3, ...FieldN)
> SELECT sum(a) AS Field1, sum(b) AS Field2, sum(c) AS Field3
> FROM @.Table2
> WHERE n is null
> SELECT sum(a) AS Field4, sum(b) AS Field5, sum(c) AS Field6
> FROM @.Table2
> WHERE x < 50
> SELECT sum(a) AS Field7, sum(b) AS Field8, sum(c) AS Field9
> FROM @.Table2
> WHERE x >= 50
> Can I somehow do this? Since I couldn't return the result sets from my
> 3 selects, I combined them into one result set.
> Right now I have a giant UPDATE statement, but it seems really
> unwieldy...it's something like:
> UPDATE @.Table
> SET Field1 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field2 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field3 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field4 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field5 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field6 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field7 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field8 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field9 = SELECT sum(a) FROM @.Table2 WHERE x >= 50
> There has to be a better way. (Can you tell I only half know what I'm
> doing?)
> Thank you!
>
See the following example. Of course you would also replace the word "field"
with "column" because if you even half knew what you were doing then you'd
know that a column isn't a field. :-)
INSERT INTO @.Table
(field1, field2, field3, field4, field5, field6, field7, field8, field9)
SELECT
SUM(CASE WHEN n IS NULL THEN a END) AS field1,
SUM(CASE WHEN n IS NULL THEN b END) AS field2,
SUM(CASE WHEN n IS NULL THEN c END) AS field3,
SUM(CASE WHEN x < 50 THEN a END) AS field4,
SUM(CASE WHEN x < 50 THEN b END) AS field5,
SUM(CASE WHEN x < 50 THEN c END) AS field6,
SUM(CASE WHEN x >= 50 THEN a END) AS field7,
SUM(CASE WHEN x >= 50 THEN b END) AS field8,
SUM(CASE WHEN x >= 50 THEN c END) AS field9
FROM @.Table2 ;
David Portas
SQL Server MVP
--|||Use a case statement to populate Table2.
select <Other Columns>, sum(case when n is null then a else 0 end) as
Field1,
sum(case when n is null then b else 0 end) as Field2,
sum(case when n is null then c else 0 end) as Field3,
sum(case when x < 50 then a else 0 end) as Field4,
sum(case when x < 50 then b else 0 end) as Field5,
sum(case when x < 50 then c else 0 end) as Field6,
sum(case when x >= 50 then a else 0 end) as Field7,
sum(case when x >= 50 then b else 0 end) as Field8,
sum(case when x >= 50 then c else 0 end) as Field9
from Table2
group by <Other Columns>|||ok - i'm not even waiting for my other post to get out there - ignore it
- incomplete
insert into @.table (Field1, ..., Field9)
select
sum(case when n is null then a end) as Field1,
sum(case when n is null then b end) as Field2,
sum(case when n is null then c end) as Field3,
sum(case when x<50 then a end) as Field4,
sum(case when x<50 then b end) as Field5,
sum(case when x<50 then c end) as Field6,
sum(case when x>=50 then a end) as Field7,
sum(case when x>=50 then b end) as Field8,
sum(case when x>=50 then c end) as Field9
from @.Table2
another possibility is to change your table1 to have a criteria
indicator and fewer columns, and union the queries together, e.g.
insert into @.Table (criteria, Field1, Field2, Field3)
select 'null n', sum(a), sum(b), sum(c)
from @.table2
where n is null
union all
select 'x>50', sum(a), sum(b), sum(c)
from @.table2
where x>50
union all
select 'x<=50', sum(a), sum(b), sum(c)
from @.table2
where x<=50
Confused wrote:
> I have a stored procedure and I want to do something like:
> INSERT INTO @.Table
> (Field1, Field2, Field3, ...FieldN)
> SELECT sum(a) AS Field1, sum(b) AS Field2, sum(c) AS Field3
> FROM @.Table2
> WHERE n is null
> SELECT sum(a) AS Field4, sum(b) AS Field5, sum(c) AS Field6
> FROM @.Table2
> WHERE x < 50
> SELECT sum(a) AS Field7, sum(b) AS Field8, sum(c) AS Field9
> FROM @.Table2
> WHERE x >= 50
> Can I somehow do this? Since I couldn't return the result sets from my
> 3 selects, I combined them into one result set.
> Right now I have a giant UPDATE statement, but it seems really
> unwieldy...it's something like:
> UPDATE @.Table
> SET Field1 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field2 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field3 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field4 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field5 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field6 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field7 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field8 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field9 = SELECT sum(a) FROM @.Table2 WHERE x >= 50
> There has to be a better way. (Can you tell I only half know what I'm
> doing?)
> Thank you!
>|||Oops...I forgot to put that...I did try that. And when I do that I get
the following error:
The select list for the INSERT statement contains fewer items than the
insert list. The number of SELECT values must match the number of
INSERT columns.|||Thank you everyone for the input and help! It is much appreciated!

Is this possible ?

is it possible to have a select statement from a stored proceedure ? example ...

CREATE PROCEDURE spA
SELECT * FROM ( exec spB '1','1' )

go

the reason for doing this is becos spB does some data massaging and is used across many stored procedures, spB will be passing back a table if it is possible.

Thanks.I'd recommend that you rewrite spB as a User-defined table function. Then you can reference it in stored procedures, views, triggers, etc, just like any other table or subquery:

CREATE PROCEDURE spA
SELECT * FROM dbo.spB ('1','1')

blindman|||Or it that's something you would like to avoid, you could use

Insert Into #TmpSpB Exec SpB '1','1'

where #TmpSpB is a temporary table which has the structure of the result return by the stored procedure. The only problem with this, is that can not be nested, and I am not sure if it's working "below" SQL2000.

Best regards!|||thanks for the replies ... you've been a great help ...

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 this a permissions problem

I have a stored procedure that creates a temporary table, populates it,
deletes certain records from it and then selects all the data from it.
In query analyzer I get a resultset, however in my VB6 code it doesn't
return a resultset, the recordset isn't even open after running it.
If I run other stored procedures they work no problem and return a resultset
i cant seem to figure out the issue and I am assuming that its down to
permissions, as the only difference in the sp's are that this one uses temp
tables, although I thought that any #tables created inside of a stored
procedure are there till execution of the sp ends. Do I have to set
something that allows temporary tables to be created
TIAI dont think this is a permission problem. Just check to which SP u are
referring to and in which database does the SP reside.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"steven scaife" wrote:

> I have a stored procedure that creates a temporary table, populates it,
> deletes certain records from it and then selects all the data from it.
> In query analyzer I get a resultset, however in my VB6 code it doesn't
> return a resultset, the recordset isn't even open after running it.
> If I run other stored procedures they work no problem and return a results
et
> i cant seem to figure out the issue and I am assuming that its down to
> permissions, as the only difference in the sp's are that this one uses tem
p
> tables, although I thought that any #tables created inside of a stored
> procedure are there till execution of the sp ends. Do I have to set
> something that allows temporary tables to be created
> TIA|||I'm calling the right sp as I can call some others from the same database, I
can pass paramaters to the other sps and they run fine, the only difference
is one creates and uses a temp table, yet it runs fine under QA.
Its just bugging me as I can't seem to figure out the problem
"Chandra" wrote:
> I dont think this is a permission problem. Just check to which SP u are
> referring to and in which database does the SP reside.
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "steven scaife" wrote:
>|||I found the solution needed to have
set nocount on in my sp
"steven scaife" wrote:

> I have a stored procedure that creates a temporary table, populates it,
> deletes certain records from it and then selects all the data from it.
> In query analyzer I get a resultset, however in my VB6 code it doesn't
> return a resultset, the recordset isn't even open after running it.
> If I run other stored procedures they work no problem and return a results
et
> i cant seem to figure out the issue and I am assuming that its down to
> permissions, as the only difference in the sp's are that this one uses tem
p
> tables, although I thought that any #tables created inside of a stored
> procedure are there till execution of the sp ends. Do I have to set
> something that allows temporary tables to be created
> TIA|||SET NOCOUNT ON suppresses DONE_IN_PROC messages and can improve performance
by avoiding extra round trips. With the default SET NOCOUNT OFF,
DONE_IN_PROC messages are returned to ADO apps as empty closed recordsets
and can interfere with data retrieval unless you skip them using the
NextRecordset method.
Hope this helps.
Dan Guzman
SQL Server MVP
"steven scaife" <stevenscaife@.discussions.microsoft.com> wrote in message
news:88F9E00F-DFB3-4E44-AAC6-454042C7DE46@.microsoft.com...
>I found the solution needed to have
> set nocount on in my sp
> "steven scaife" wrote:
>sql

Is this a "stored procedure" situation?

We have 2 SQL tables being accessed through an Access form. The tables are an ORDER table and an ORDER-DETAIL table comprised of data regarding the Parts in any given Order. (Yes -- the classic Order-Entry situation.) The Access form is used to view/create new Orders, and shows ORDER data in fields, plus has a large field which presents a "spreadsheet"-like view of the related records from the ORDER-DETAIL table.

The users enter and modify data in the ORDER-DETAIL table directly through this "spreadsheet" in the Access form. However, because there is no PARTS table yet (that's part of what I'm working on), they have to enter part numbers and descriptions *manually* in each ORDER.

So... here's my question:

After I implement a PARTS table, I would like for users to be able to open an ORDER in the Access form, type in a Part # in a row of the ORDER-DETAIL "spreadsheet", and then have the rest of the row populate with the appropriate Part description and other data from the PARTS table. How do I go about making that a reality? Some kind of stored procedure triggered by a change in the Part # field? Ha ha if so, I am clueless as to how to make that happen. ANY information would greatly appreciated!

Thanks!
whill96205 the Noob :confused:You'll want a few stored procedures for this probably. :) You don't want to bind the datagrid to the order-detail table. You'll need to populate it, then after they enter a part number, you will want to have an ON UPDATE action that:

1. Gets the part information and updates the ORDER-DETAIL table.
2. Refreshed the datagrid.|||[QUOTE=derrickleggett]You don't want to bind the datagrid to the order-detail table.QUOTE]

I think I understand what you mean by "bind" -- that the datagrid is like a *direct* window into the ORDER DETAIL table, right?

Okay, so I DON'T want to bind them. How can I tell if the datagrid that is currently in use is bound or not?

--William|||>> DerrickLeggett said:
>>You don't want to bind the datagrid to the order-detail table. You'll
>>need to populate it, then after they enter a part number, you will
>>want to have an ON UPDATE action that:
>> 1. Gets the part information and updates the ORDER-DETAIL table.
>> 2. Refreshed the datagrid.

The "datagrid" is a subform. Currently, I am using a View as the datasource for the subform, and the View is comprised of a join from the ORDER table and the PART table, and displays the PARTs that are already associated with the ORDER being viewed on the main form. There are two issues I'm trying to nail down:
1) To do what Derrick suggested (above), so that entering a PartNum value into a row of the subform causes the rest of the row to update with other data from the PART table (part description, etc.); and
2) To also allow a user to actually create a *new* entry in the PART table by entering a new PartNum into a row of the subform.

SO, I'd like the subform to recognize if a PartNum being entered into it is new or not. Is that possible? And, if so, how do I do that? PLEASE be explicit - this is all very new to me... :)

Is there way to run something on subscriber after distribution agent pushed transactions?

I need execute stored procedure on subscriber right after distribution agent
finished pushing transactions.
How to do it?
I don't have anonymous and pull subscribers.
MS SQL2K+SP3a
In the best we trust
Georgy Nevsky
If you are not running continuously you can make this the 4th job step.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Georgy Nevsky" <apokriffree@.hotbox.ru> wrote in message
news:%23gVfg7UCFHA.2032@.tk2msftngp13.phx.gbl...
> I need execute stored procedure on subscriber right after distribution
agent
> finished pushing transactions.
> How to do it?
>
> I don't have anonymous and pull subscribers.
> MS SQL2K+SP3a
>
> --
> In the best we trust
> Georgy Nevsky
>
|||I'm manually running distribution agent to push changes from publisher to
subscriber and I'm running that on publisher but I need execute stored
procedure on subscriber so I can't add that as last step. Is there other
way?
In the best we trust
Georgy Nevsky
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O9n8fCWCFHA.3784@.TK2MSFTNGP15.phx.gbl...
> If you are not running continuously you can make this the 4th job step.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Georgy Nevsky" <apokriffree@.hotbox.ru> wrote in message
> news:%23gVfg7UCFHA.2032@.tk2msftngp13.phx.gbl...
> agent
>
|||In my push subscriptions, I have a trigger on the MSsubscription_agents
table and I check the status flag last_sync_status and call sp_start_job to
do some reporting following the push (I think 2 is successful).
Message posted via http://www.sqlmonster.com

Is there sp_helptext for tables

Hello Everybody, Please help me out:

Is there a system stored procedure for retrieving the sql statement that created a table.

I know i can use sp_helptext for views etc; i want the equivalent for tables.

sp_columns is not adequate either.

please help! thanks in advance;)sp_help will return all the columns of a table.|||sp_help will return all the columns of a table.
Hi Blindman,

I ran exec sp_help tblcustomers and i got:

Name: tblcustomers
Owner: dbo
Type: user table
Created_datetime: 4/18/2007 2:26:12 PM

Am i missing something??

Is there cascading permission?

Let's say that I have a view that has no explicit permissions defined for
users. Let's also say that I write a stored procedure which uses this view.
Now, if I give explicit permissions to a user to EXEC the stored procedure,
will the procedure execute correctly even if I don't explicitily give
permissions (such as SELECT) for the user to the view?
Michael HocksteinYes, it is.
"michael" <howlinghound@.nospam.nospam> wrote in message
news:CAB858A0-22F5-410C-842E-A0FE19C84343@.microsoft.com...
> Let's say that I have a view that has no explicit permissions defined for
> users. Let's also say that I write a stored procedure which uses this
> view.
> Now, if I give explicit permissions to a user to EXEC the stored
> procedure,
> will the procedure execute correctly even if I don't explicitily give
> permissions (such as SELECT) for the user to the view?
>
> --
> Michael Hockstein|||So, as long as a user has permissions to execute a stored procedure, the
stored procedure will execute correctly even if the user does not have
explicit permissions defined for the objects consumed within the stored
procedure?
If the user has permissions to execute a stored procedure but that the user
is explicitly denied permissions to objects consumed by the stored procedure
will the stored procedure still execute correctly?
--
Michael Hockstein
"Uri Dimant" wrote:

> Yes, it is.
> "michael" <howlinghound@.nospam.nospam> wrote in message
> news:CAB858A0-22F5-410C-842E-A0FE19C84343@.microsoft.com...
>
>|||So, if a user has permission to execute a stored procedure which in turn
consumes objects that the same user does not have explicit permissions
defined, the stored procedure will execute without security issues?
And, if a user has permission to execute a stored procedure which in turn
consumes objects that the same user is explicitly denied permisions, will th
e
stored procedure still execute without security issues?
Where can I find documentation on how this security cascades?
Michael Hockstein
"Uri Dimant" wrote:

> Yes, it is.
> "michael" <howlinghound@.nospam.nospam> wrote in message
> news:CAB858A0-22F5-410C-842E-A0FE19C84343@.microsoft.com...
>
>|||> So, as long as a user has permissions to execute a stored procedure, the
> stored procedure will execute correctly even if the user does not have
> explicit permissions defined for the objects consumed within the stored
> procedure?
yes, it will , unless you have dynamic sql within a stored procedure , then
you'll have to grant permision on undelaying tables

> If the user has permissions to execute a stored procedure but that the
> user
> is explicitly denied permissions to objects consumed by the stored
> procedure
> will the stored procedure still execute correctly?
Yes , it will
"michael" <howlinghound@.nospam.nospam> wrote in message
news:2DF71CC1-0FAF-4D82-A396-782AC53757C9@.microsoft.com...[vbcol=seagreen]
> So, as long as a user has permissions to execute a stored procedure, the
> stored procedure will execute correctly even if the user does not have
> explicit permissions defined for the objects consumed within the stored
> procedure?
> If the user has permissions to execute a stored procedure but that the
> user
> is explicitly denied permissions to objects consumed by the stored
> procedure
> will the stored procedure still execute correctly?
> --
> Michael Hockstein
>
> "Uri Dimant" wrote:
>|||See:
Security -Giving Permissions through Stored Procedures
http://www.sommarskog.se/grantperm.html
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"michael" <howlinghound@.nospam.nospam> wrote in message
news:F84CE2FB-8709-4B68-BB3C-9F86722B9A8E@.microsoft.com...[vbcol=seagreen]
> So, if a user has permission to execute a stored procedure which in turn
> consumes objects that the same user does not have explicit permissions
> defined, the stored procedure will execute without security issues?
> And, if a user has permission to execute a stored procedure which in turn
> consumes objects that the same user is explicitly denied permisions, will
> the
> stored procedure still execute without security issues?
> Where can I find documentation on how this security cascades?
>
> --
> Michael Hockstein
>
> "Uri Dimant" wrote:
>|||Thanks. I'll look at the reference. BTW, your tag line is one of my favorite
all time sayings.
Michael Hockstein
"Arnie Rowland" wrote:

> See:
> Security -Giving Permissions through Stored Procedures
> http://www.sommarskog.se/grantperm.html
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "michael" <howlinghound@.nospam.nospam> wrote in message
> news:F84CE2FB-8709-4B68-BB3C-9F86722B9A8E@.microsoft.com...
>
>|||Check out 'ownership chains' in the SQL Server Books Online. The principal
is basically that permissions on indirectly referenced objects are not
needed as long as the objects (or schema on 2005) involved have the same
owner.
Hope this helps.
Dan Guzman
SQL Server MVP
"michael" <howlinghound@.nospam.nospam> wrote in message
news:0EED2BFD-1EA5-4DA8-A512-C4D8E91CEFC0@.microsoft.com...[vbcol=seagreen]
> Thanks. I'll look at the reference. BTW, your tag line is one of my
> favorite
> all time sayings.
> --
> Michael Hockstein
>
> "Arnie Rowland" wrote:
>

is there any way to sort the SELECT statement?

hi,

i have a stored procedure

SELECT PictureID,Left(Name, 16) +'...'AS ShortNameFROM Pictures

some pictures has their name lower that 16 characters, and when i show on the page is something like "Sunrise..."

how can i 'sort' the Select so to return the ShortName if the name is greater than 16 characters or return Name if is lower

i hope you understand what i mean...

thanks

Hi,

You have to use the Case Statement

SELECT PictureId, Case When Len(Name)>16 THEN Left(Name,16)+'...' ELSE Name End AS ShortName From Pictures.

Hope this helps.

|||

thank you v v much

|||

You are welcome. I am glad I could help.

is there any tools in the Business Intelligence Studio which can be used as Rules Engine?

I want to create business Rules from BI Development Studio. This will be a replacement of creating Stored Procedure. Most of the Business Rules are in the form of Stored Procedures since the DB is based on SQL Server 2000, but, it's migrating to 2005 server, so, it would be ideal if something in the BI studio can do Business Rules.Moving to SQL Server Tools General forum.

Is there any sample code to demo the SSB send messages with same sql instance?

Is there any sample code to demo the SSB send messages with same sql instance?

my case is very simple:

I want write a stored procedure to send a xml to another database. The stored procedure is called by tables triggers when some data is changed under the specific conditions.

this should be exactly what you need:

http://www.sqlteam.com/article/centralized-asynchronous-auditing-with-service-broker

Is there any REGEXP library for TSQL?

Hi guys,

Sounds a bit strange, however, if we could put some calculation in
stored procedure it would be quite convenient, just... where can I find
a REGEXP library for matching checking? thanks.

yours,
athos"athos" <athos.liu@.gmail.com> wrote in message
news:1130877928.278288.62290@.g44g2000cwa.googlegro ups.com...
> Hi guys,
> Sounds a bit strange, however, if we could put some calculation in
> stored procedure it would be quite convenient, just... where can I find
> a REGEXP library for matching checking? thanks.
> yours,
> athos

Take a look at the LIKE topic in Books Online to see if it meets your
requirements. Not regex but it does support some simple pattern matching.

--
David Portas
SQL Server MVP
--|||LIKE is not powerful enough. btw, COM is prohibited. thanks.|||athos (athos.liu@.gmail.com) writes:
> Sounds a bit strange, however, if we could put some calculation in
> stored procedure it would be quite convenient, just... where can I find
> a REGEXP library for matching checking? thanks.

It does not sound strange at all. Some DB Engines have SIMILAR TO, and
this might even be in ANSI. I believe this uses some form of regexps.
I've been longing for it myself at times.

But for SQL2000 there is only LIKE which is far from whole covering.
You can use patindex or charindex for some stuff, but in essence it's
all very primitive.

In SQL 2005, there is no better support in T-SQL, but you can call a CLR
routine that uses the RegEx classes in .Net.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> In SQL 2005, there is no better support in T-SQL, but you can call a CLR
> routine that uses the RegEx classes in .Net.

I guess for SQL 2000, you could use a non-COM library as an "extended
procedure"?

--
With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||Martijn Tonies (m.tonies@.upscene-removethis.nospam.com) writes:
>> In SQL 2005, there is no better support in T-SQL, but you can call a CLR
>> routine that uses the RegEx classes in .Net.
> I guess for SQL 2000, you could use a non-COM library as an "extended
> procedure"?

But performance would be awful and the code would be messy.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> >> In SQL 2005, there is no better support in T-SQL, but you can call a
CLR
> >> routine that uses the RegEx classes in .Net.
> > I guess for SQL 2000, you could use a non-COM library as an "extended
> > procedure"?
> But performance would be awful and the code would be messy.

I've never written any extended procedures, so perhaps you could
explain why this would give awful performance?

I imagine the call could be as:

select ...
from ...
where myregexp_match(mycolumn, myexpression, myvalue)

Why would this be any slower than COM or .NET? Isn't this partly
what extended procedures were meant for?

--
With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:11mhdiekuhe26e9@.corp.supernews.com...
> > >> In SQL 2005, there is no better support in T-SQL, but you can call a
> CLR
> > >> routine that uses the RegEx classes in .Net.
> > > > I guess for SQL 2000, you could use a non-COM library as an "extended
> > > procedure"?
> > But performance would be awful and the code would be messy.
> I've never written any extended procedures, so perhaps you could
> explain why this would give awful performance?
> I imagine the call could be as:
> select ...
> from ...
> where myregexp_match(mycolumn, myexpression, myvalue)
> Why would this be any slower than COM or .NET? Isn't this partly
> what extended procedures were meant for?

I'm guessing the main reason is that in SQL 2000, it executes outside of SQL
Server, which means for every call there's delay as it has to call out of
its address space. SQL 2005 CLR code executes within the same memory space
as SQL Server.

> --
> With regards,
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com|||Martijn Tonies (m.tonies@.upscene-removethis.nospam.com) writes:
> I've never written any extended procedures, so perhaps you could
> explain why this would give awful performance?
> I imagine the call could be as:
> select ...
> from ...
> where myregexp_match(mycolumn, myexpression, myvalue)

That's not really how you call extended stored procedure. But you could
encapsulate the XP in a user-defined function to get this syntax. However,
there is a big overhead for calling a UDF in a WHERE clause in SQL 2000
(this overhead has been reduced in SQL 2005). If you then add a call to
extended stored procedure that gives you context switches and all, it's
getting really bad.

Then add to this that if you have a bug in your XP that causes an
access violation or similar, it's not only the XP that crashes. You
blow away the entire SQL Server.

> Why would this be any slower than COM or .NET? Isn't this partly
> what extended procedures were meant for?

The CLR stuff in SQL 2005 is a lot more integrated in SQL Server and there
is far less overhead for invoking CLR. In fact, say that you have a decently
complex operation like some string manipulation that you can perform in
T-SQL, it is very likely to perform better in a CLR UDF. (But if you
start do data access from the CLR, it's a different picture.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> > I've never written any extended procedures, so perhaps you could
> > explain why this would give awful performance?
> > I imagine the call could be as:
> > select ...
> > from ...
> > where myregexp_match(mycolumn, myexpression, myvalue)
> That's not really how you call extended stored procedure. But you could
> encapsulate the XP in a user-defined function to get this syntax. However,
> there is a big overhead for calling a UDF in a WHERE clause in SQL 2000
> (this overhead has been reduced in SQL 2005). If you then add a call to
> extended stored procedure that gives you context switches and all, it's
> getting really bad.

Then when are XPs actually useful?

> Then add to this that if you have a bug in your XP that causes an
> access violation or similar, it's not only the XP that crashes. You
> blow away the entire SQL Server.

I understand this part, seems to be the case with pretty much all
extending to DB engines (unless managed or Java or whatever).|||Martijn Tonies (m.tonies@.upscene-removethis.nospam.com) writes:
>> That's not really how you call extended stored procedure. But you could
>> encapsulate the XP in a user-defined function to get this syntax.
>> However, there is a big overhead for calling a UDF in a WHERE clause in
>> SQL 2000 (this overhead has been reduced in SQL 2005). If you then add
>> a call to extended stored procedure that gives you context switches and
>> all, it's getting really bad.
> Then when are XPs actually useful?

When the stuff you want to do with them are not used to evaluate queries.
For instance, we have an extended stored procedure that performs a loopback
and writes messages to a log table when an error is detected. (The point
with the loopback is that we want the log records to persist even if there
is a rollback.)

Another possible application is some sort of signaling, to inform some
external process "Hey, I've just inserted 10000 rows, you might be
interested in those".

But it is correct that XP:s, as well as sp_OAcreate & co for calling
OLE objects, have limited use, and something you only use for special
cases.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> But it is correct that XP:s, as well as sp_OAcreate & co for calling
> OLE objects, have limited use, and something you only use for special
> cases.

Thanks for the explanation.

--
With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Is there any point in views? vs stored procedures?

I do all my data access through stored procedures. Is there any benefit in creating views that the stored procedure accesses. At the moment, I tend to just write the select and join within the stored procedure.

Do you have to do any manipulation of the data? Are the stored procedures doing anything else than just returning data? Will the data change? Do you need to pass any parameters to the stored procedure?

As you can probably guess by my questions, there isn't a straight forward answer as it really depends on the data and what you are doing with it as to which method you should choose for each function.

|||

An interesting philosophical discussion is no doubt about to erupt.

If you are going to be re-using a very complex join, it makes sense to use a view; kind of like a re-usable business object. Its generally easier to maintain than a sp and is more accessible to people coming behind you. You can more easily make little tweaks if you need to make variations on a theme.

But of course sps are a lot more powerful.

|||I have seperate stored procedures for updating the data. These stored procedures return data. But my real question is, within the stored procedure, is it better to do this:

CREATE PROCEDURE mySp1
@.x int
AS
SELECT *
FROM View_myView
WHERE x = x

Or do this

CREATE PROCEDURE mySp1
@.x int
AS
SELECT *
FROM myTable1
INNER JOIN myTable2 ON ...
INNER JOIN myTable3 ON ...
WHERE x = x|||

jagdipa:

is it better to do this:

CREATE PROCEDURE mySp1
@.x int
AS
SELECT *
FROM View_myView
WHERE x = x

Or do this

CREATE PROCEDURE mySp1
@.x int
AS
SELECT *
FROM myTable1
INNER JOIN myTable2 ON ...
INNER JOIN myTable3 ON ...
WHERE x = x

There isn't really a right answer. You've given a dummy scenario so we can only really offer general advice and we can't say whether one method will be better than the other.

Either method will work, and it really depends on what other objects will be accessing this data as to whether a view is needed or not. You will also have to look at the execution plans to see if there are any performance issues to take note of.

|||"Either method will work, and it really depends on what other objects will be accessing this data as to whether a view is needed or not."

This is really what I want to know. The example I gave is actually what I am doing (with a few more tables).

As far as I understand, a view is optimised. But so is a stored procedure. So, in theory, using either method should have the exact same performance (they are optomised in the same way using the same algorithms). I am probably wrong here.

But there is also the design point of view. Is there a design reason for using views? (exect the obvious putting security on a view seperatly from a stored procedure).|||

jagdipa:

This is really what I want to know.

But we don't know what other objects will be accessing the data. It's your database so only you know the answer to that question.

As I've said there is no right answer. You will have to decide for yourself which is the best approach, based on how often the data gets accessed, what other functions may need the same data and any performance issues that come out of your testing. Sorry I can't give you a direct answer as to which one is best but that's because one isn't simply better than the other. It all boils down to the individual needs of the database and that's something that only you can answer.

jagdipa:

But there is also the design point of view. Is there a design reason for using views?

If the data will be reused in several places then it makes sense to consolidate it into a view. Again, this is just theory and may not be the best solution for your needs.

|||

There are a number of real-world parameters to consider beyond ease of initial coding. Its probably easier to maintain a view in a large organization, plus its a whole lot easier for someone to look at in order to determine whether it's something they need to use (you could set up a view schema to act like a business object library). If you leave, the person coming in behind you can look at the views with a click,as opposed to having to execute the stored procedures.

Generally, the more static an object is, the easier it is to use a view for it's datasource. If you have something that gets databound on page load and doesn't change, a view is fine and dandy. And you could use a view as your base datasource and add filters to it if you are doing cascading ddls, for example.

But if you build a large object library, especially if you build generators for it, it's probably better to go with stored procedures. People following after you will have to be pretty high-speed anyway to be able to work with your code, so accessability really isn't an issue, and users can look at using your business objects as datasources instead of your views. I guess the higher you are scaling, the better sps are versis views.

|||

I stand firmly and forthrightly with those who say, "It depends!"

I would just add these comments (but really I agree with just about everything everyone else has said):

1. If the join is complicated, using a view sure makes it easier to reuse. It's much less error prone.

2. Views sometimes come with an unanticipated consquence, namely that you are guaranteed to access all the tables in the view. Other programmers, who may not know exactly what's in the view, may use a view incorrectly as a result. For example, lets say you have a view that joins 5 tables. Someone else comes along and retrieves data from the view, but really they only need data from 3 of the tables. By using the view, you not only force them to access all 5 but -- more importantly -- it may affect what data gets returned depending on whether you're using inner joins vs outer joins. I've seen this happen.

3. Views also have a nice security implication -- you can control access through views, though in practice this isn't done all that much.

Also, remember that your choice is NOT view vs proc because you can (should, some would argue) use a proc with a view.

|||Thanks for the clarifications. Views are ok, but I think I will use them sparingly. The reason for this is because I use a few user defined functions as well. In a stored procedure, I can create a temp table and pull the data I want into this. Then I can run the function on just that data (instead of all the data).

I think dbland07666 is right - especially with point 2. When I did not have much experience, I stuck everything in views, even when only pulling out very little. The view died very quickly when I added another 5 user defined functions to it !!!|||

Now that you are more experienced, I think you should look into developing a business object layer. It will allow you to do things programatically that would require dozens of lines of code with a single call. For example, you could so something like Personnel.GetList() to create a collection of Personnel objects which you can sort or filter or what-have-you in the object layer as opposed to calling stored procedures to do these various things with parameters. It's faster than going to the database and can be made to reside in cache so it scales up really nicely for multi-user environments.

|||Hi Charles,

I would love to learn more on this. Have you got a good tutorial I can use?
At the moment, I am going towards a sort of middle tier - I am new at this so its not great. I just create classes that access the relevant data (via stored procedures). It has come in very useful at times. But I am only creating web pages for a website that is probably only accessed 5 times an hour !! (its a B2B website).|||

http://aonaware.com/OOP1.htm

You can do stuff like build server-side validation into your objects to protect your database, you can write generic screens that have behaviors that are inherited by child screens which handle specific objects, all sorts of good stuff.

Here's the framework we use at this shop. It's free and its growing...

http://forums.lhotka.net/forums/default.aspx

Is there any examples of inserting/create a namespace in sql

Is there any examples of inserting/create a namespace in t-sql...so my
stored procedure return a <root xmlns="http://ns"></root>
thanks a billion!!!Hello mm,
m> Is there any examples of inserting/create a namespace in t-sql...so
m> my stored procedure return a <root xmlns="http://ns"></root>
In SQL Server 2005, you can use the new "with xmlnamespaces" option to do
that. In SQL Server 2000, no such luck, there's not a good way (at least
that I can recall) to get namespaced XML from for xml query.
with xmlnamespaces(default 'http://ns')
select 'a' as 'text()'
for xml path('v'),root('root'),type
See the topic "Adding Namespaces Using WITH XMLNAMESPACES " in BOL for more
information
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||thanks for answer...now i can move on :)
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad74176758c83649594b73f0@.news.microsoft.com...
> Hello mm,
> m> Is there any examples of inserting/create a namespace in t-sql...so
> m> my stored procedure return a <root xmlns="http://ns"></root>
> In SQL Server 2005, you can use the new "with xmlnamespaces" option to do
> that. In SQL Server 2000, no such luck, there's not a good way (at least
> that I can recall) to get namespaced XML from for xml query.
> with xmlnamespaces(default 'http://ns')
> select 'a' as 'text()'
> for xml path('v'),root('root'),type
> See the topic "Adding Namespaces Using WITH XMLNAMESPACES " in BOL for
> more information
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>

Is there any BPA for procedures ?

Hi all,

Is there any best practice analyzer for analysing the SQL objects like stored procedure, functions and views ?

Thanks in advance,

DBLearner

Unfortunately, there is not.

Coding Standards and Peer review IS the 'best practice analyzer'.

|||

You could use the SQL Server 2000 Best Practices Analyzer:

http://www.microsoft.com/downloads/details.aspx?FamilyID=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en

...or the February CTP version for SQL Server 2005:

http://www.microsoft.com/downloads/details.aspx?FamilyId=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en

Chris

|||They are planning to move something like this in one of the next versions of VSDB.

Jens K. Suessmeyer.

http://www.sqlserver2005.de