Is using Max in varchar a bad thing?

I just had a situation where I was creating a temp table in a stored proc. I
create the temp table and then do a select into it from a couple of other
tables. Problem is, we had changed the size of one of the field in the table
from varchar(250) to varchar(500). But the temp table didn't reflect that so
data was being truncated.
So the easiest way around this would be to make the field varchar(max)
instead of 500 so if and when that field grows later, I don't get an error
in the stored proc.
Question is - does using varchar(max) have any repurcusions? Could I just
use it everywhere and then not have to worry about this problem?
TIA - Jeff.
> So the easiest way around this would be to make the field varchar(max)
> instead of 500 so if and when that field grows later, I don't get an error
> in the stored proc.
Or maybe you can avoid using a temp table for this data?
Or maybe if you change the data type of the base column (which shouldn't
happen very often) you also change the other places it is referenced (e.g.
SP params, variable declarations, etc.)?

> Question is - does using varchar(max) have any repurcusions? Could I just
> use it everywhere and then not have to worry about this problem?
This is like trading in a sub-compact for a minivan because you don't like
the way the tennis racket fits on the seat.
I would strongly recommend only using MAX when you absolutely need to have >
4000 or 8000 characters. In this case, if you changed from 250 to 500 now,
you will probably change it again. I would say envision the largest # of
characters that column will ever need to hold, then double it, and fix it
everywhere once. This drastically reduces the likelihood you will have to
worry about it again.
|||> As an alternative, why not create your temp table from the underlying
> source columns - that way your temp table columns will always match
> the source columns.
That's a possible solution, and I don't know the op's requirements, but I
often opt for CREATE TABLE and then INSERT INTO, in case I need to have
additional columns, or in case I want to define indexes/keys/constraints
etc. BEFORE all of the data is in the destination table...
A
|||I would create the table that way but I actually am doing a couple of
different selects putting data in the table so I need to do insert intos.
<jhofmeyr@.googlemail.com> wrote in message
news:861f7e9e-1970-4538-99c8-e599c1847a0b@.f10g2000hsf.googlegroups.com...
> Hi Jeff,
> I would avoid using varchar(max) unless you actually need to store
> very long values (> 8000 chars)
> As an alternative, why not create your temp table from the underlying
> source columns - that way your temp table columns will always match
> the source columns.
> You can do this very easily by creating the table like this:
> SELECT <col list>
> INTO #tmp_table
> FROM <table list>
> WHERE 1 = 0
> Good luck!
> J
|||>I would create the table that way but I actually am doing a couple of
>different selects putting data in the table so I need to do insert intos.
But the very first one could be a select into, no? I assume that the source
table that drives that column would have the same data type as the column
that fills that data if you have source data from other tables, so all the
tables should be updated if you increase the size again...
A
sql

Is using Max in varchar a bad thing?

I just had a situation where I was creating a temp table in a stored proc. I
create the temp table and then do a select into it from a couple of other
tables. Problem is, we had changed the size of one of the field in the table
from varchar(250) to varchar(500). But the temp table didn't reflect that so
data was being truncated.
So the easiest way around this would be to make the field varchar(max)
instead of 500 so if and when that field grows later, I don't get an error
in the stored proc.
Question is - does using varchar(max) have any repurcusions? Could I just
use it everywhere and then not have to worry about this problem?
TIA - Jeff.Hi Jeff,
I would avoid using varchar(max) unless you actually need to store
very long values (> 8000 chars)
As an alternative, why not create your temp table from the underlying
source columns - that way your temp table columns will always match
the source columns.
You can do this very easily by creating the table like this:
SELECT <col list>
INTO #tmp_table
FROM <table list>
WHERE 1 = 0
Good luck!
J|||> So the easiest way around this would be to make the field varchar(max)
> instead of 500 so if and when that field grows later, I don't get an error
> in the stored proc.
Or maybe you can avoid using a temp table for this data?
Or maybe if you change the data type of the base column (which shouldn't
happen very often) you also change the other places it is referenced (e.g.
SP params, variable declarations, etc.)?
> Question is - does using varchar(max) have any repurcusions? Could I just
> use it everywhere and then not have to worry about this problem?
This is like trading in a sub-compact for a minivan because you don't like
the way the tennis racket fits on the seat.
I would strongly recommend only using MAX when you absolutely need to have >
4000 or 8000 characters. In this case, if you changed from 250 to 500 now,
you will probably change it again. I would say envision the largest # of
characters that column will ever need to hold, then double it, and fix it
everywhere once. This drastically reduces the likelihood you will have to
worry about it again.|||> As an alternative, why not create your temp table from the underlying
> source columns - that way your temp table columns will always match
> the source columns.
That's a possible solution, and I don't know the op's requirements, but I
often opt for CREATE TABLE and then INSERT INTO, in case I need to have
additional columns, or in case I want to define indexes/keys/constraints
etc. BEFORE all of the data is in the destination table...
A|||I would create the table that way but I actually am doing a couple of
different selects putting data in the table so I need to do insert intos.
<jhofmeyr@.googlemail.com> wrote in message
news:861f7e9e-1970-4538-99c8-e599c1847a0b@.f10g2000hsf.googlegroups.com...
> Hi Jeff,
> I would avoid using varchar(max) unless you actually need to store
> very long values (> 8000 chars)
> As an alternative, why not create your temp table from the underlying
> source columns - that way your temp table columns will always match
> the source columns.
> You can do this very easily by creating the table like this:
> SELECT <col list>
> INTO #tmp_table
> FROM <table list>
> WHERE 1 = 0
> Good luck!
> J|||>I would create the table that way but I actually am doing a couple of
>different selects putting data in the table so I need to do insert intos.
But the very first one could be a select into, no? I assume that the source
table that drives that column would have the same data type as the column
that fills that data if you have source data from other tables, so all the
tables should be updated if you increase the size again...
A

Is this the right place for a question about creating a report in VB.net?

I am working in a new environment and I only have a fraction of the BI Project
Templates when I create a new Project. I remember loading these the first time
I worked in here, but don't remember where I loaded them from. Can someone tell
me, or guide me to something that will refresh my memory about creating reports
in VS.NET 2005, for SQL 2005. Thanks...when you click File, New Project, you should have a Business Intelligence
Project grouping that contains Report Server Project Wizard, Report Server
Project, Report Model Project, and possible Analysis Service Project,
Integration Service Project and Import Analysis Service 9.0 Database.
"WANNABE" <SameAsB4> wrote in message
news:OsOdrS0wHHA.3944@.TK2MSFTNGP06.phx.gbl...
>I am working in a new environment and I only have a fraction of the BI
>Project
> Templates when I create a new Project. I remember loading these the first
> time
> I worked in here, but don't remember where I loaded them from. Can
> someone tell
> me, or guide me to something that will refresh my memory about creating
> reports
> in VS.NET 2005, for SQL 2005. Thanks...
>|||Try reinstalling the BI studio. If you installed VS (or VB.Net 2005) after
BI Studio then you lost the integration with VS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dean" <deanl144@.hotmail.com.nospam> wrote in message
news:Ook4sJJxHHA.4668@.TK2MSFTNGP06.phx.gbl...
> when you click File, New Project, you should have a Business Intelligence
> Project grouping that contains Report Server Project Wizard, Report Server
> Project, Report Model Project, and possible Analysis Service Project,
> Integration Service Project and Import Analysis Service 9.0 Database.
>
> "WANNABE" <SameAsB4> wrote in message
> news:OsOdrS0wHHA.3944@.TK2MSFTNGP06.phx.gbl...
>>I am working in a new environment and I only have a fraction of the BI
>>Project
>> Templates when I create a new Project. I remember loading these the
>> first time
>> I worked in here, but don't remember where I loaded them from. Can
>> someone tell
>> me, or guide me to something that will refresh my memory about creating
>> reports
>> in VS.NET 2005, for SQL 2005. Thanks...
>>
>sql

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 such a thing as creating an unbound report with Crystal Reports?

I am using Crystal Reports in the .NET 2005 environment. Let's say I want to create a simple one page report (like a memo) and I want one field on the report (this is not exactly what I need to do but this example addresses the concept I am looking for). Let's say I have no database and my data is in a string variable. how the heck can I dynamically set the value of the field on the report to that variable without implementing some crazy pseudo-binding strategy?

To me, it seems as though this is the most basic report possible and yet from all of my reading and searching it seems that this cannot be done with some simple syntax such as:

ReportSource.ReportObjects("Field1").Text = stringVariable;

Did Crystal leave that most basic and fundamental property out of their data model for a reason?

Should I look at alternative reporting products that are less database centric?

I used to use DataDynamics ActiveReports and it worked just like that above, however I would prefer to use Crystal as it is more industry standard.

Any help would be appeciated. Thanks.

-KevinI always love it when people answer their own questions! Well here is the answer to mine. I found it indirectly on another thread! Go dev-archive!

JournalMemo journal = new JournalMemo();
journal.DataDefinition.FormulaFields["UnboundString1"].Text = "\"It Worked\"";

That is easy enough although it would have been more intuitive if I could have done that from the ReportObjects collection.

JournalMemo is a Crystal Report that I created with the designer in the IDE. The actual JournalMemo.cs class is created by Crystal Reports when creating an embedded report in the designer and it inherits the ReportClass class.

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 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 option to create a formula object by copy paste

Friends,
crystal report 8.0
I really fed with creating formulas in crystal report. there have hundreds of formula. and almost same formula is there but I couldn't copy this to new.
please help me. thanks in advance.Can you give more information on where you are having problem?

Is there any good idea of creating KPI report on SSRS2005 for Analysis Services?

Hi, experts,

Thanks for your kind attention.

Do you have any good idea of creating reports for KPI from Analysis Services 2005 OLAP cubes? And is it worth of creating KPI reports from SSRS?

I am looking forward to hearing from you and thanks a lot.

With kind regards,

Hello Helen! Can this link be of help?

http://www.databasejournal.com/features/mssql/article.php/3627351

HTH

Thomas Ivarsson

|||

Hi,

It′s a good idea to use the SSRS or Excel 2007 to delivery your reports.

Regards|||

Hi, Thomas,

Thanks for your suggestion of the article. But as we have to struggle to import the KPI indicators for the KPI status and trends, I cant think of any way to change the KPI indicators dynamically according to the change of the status and trends?

Thanks again and I am looking forward to hearing from you for your further advices.

With kind regards,

Yours sincerely,

|||

Hi, Lucas,

Thanks for that.

Yes, I do use Excel and see the fantastic capability of viewing KPI within Excel 2007.

But with SSRS we rather can easily view the KPI values instead of indicators of KPI if we dont struggle to import any KPI indicators?

Thanks again.

With kind regards,

Yours sincerely,

|||

OK! You are talking about the Business Scorecard Server?

Regards

Thomas Ivarsson

|||

No, I am talking about SSRS still. As there are no KPI indicators residing in SSRS2005 you will have to always import static KPI indicators which wont change according to the change of the KPI values?

Hope it is clear for your help.

With kind regards,

Yours sincerely,

|||

OK! I have sent a link to an article that will help you with that. SSAS2005 status and trend graphics(KPI) are not supported in Reporting Services 2005.

You will have to build them from scratch in SSRS2005.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks a lot for your help.

With kindest regards,

Yours sincerely,

Is there any good idea of creating KPI report on SSRS2005 for Analysis Services?

Hi, experts,

Thanks for your kind attention.

Do you have any good idea of creating reports for KPI from Analysis Services 2005 OLAP cubes? And is it worth of creating KPI reports from SSRS?

I am looking forward to hearing from you and thanks a lot.

With kind regards,

Hello Helen! Can this link be of help?

http://www.databasejournal.com/features/mssql/article.php/3627351

HTH

Thomas Ivarsson

|||

Hi,

It′s a good idea to use the SSRS or Excel 2007 to delivery your reports.

Regards|||

Hi, Thomas,

Thanks for your suggestion of the article. But as we have to struggle to import the KPI indicators for the KPI status and trends, I cant think of any way to change the KPI indicators dynamically according to the change of the status and trends?

Thanks again and I am looking forward to hearing from you for your further advices.

With kind regards,

Yours sincerely,

|||

Hi, Lucas,

Thanks for that.

Yes, I do use Excel and see the fantastic capability of viewing KPI within Excel 2007.

But with SSRS we rather can easily view the KPI values instead of indicators of KPI if we dont struggle to import any KPI indicators?

Thanks again.

With kind regards,

Yours sincerely,

|||

OK! You are talking about the Business Scorecard Server?

Regards

Thomas Ivarsson

|||

No, I am talking about SSRS still. As there are no KPI indicators residing in SSRS2005 you will have to always import static KPI indicators which wont change according to the change of the KPI values?

Hope it is clear for your help.

With kind regards,

Yours sincerely,

|||

OK! I have sent a link to an article that will help you with that. SSAS2005 status and trend graphics(KPI) are not supported in Reporting Services 2005.

You will have to build them from scratch in SSRS2005.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks a lot for your help.

With kindest regards,

Yours sincerely,

Is there any book (books?) to help us creating a Sync tool (Bidirectional)

Hi guys,

We have been looking for books which cover the DB synchronisation subject. We have started creating a plugin to our socket server but we quickly realised how much of a challenge it would be (We are creating it "from scratch"). The conflict problems, the order to tables (rows) be synchronised (Child - Parent) and how to delete rows were some of the problems we found. The main goal is to synchronise devices in the field (Compact framework) to the server using less bandwich as possible.
We are trying to be "agnostic" about the DB in both Server and Client side.

We are pretty sure there are books out there which cover this subject, we just couldn't find them!

We know this will be a challenge but we also know it will be a great feature to add to the server.

Does anyone could point me out to any book?

Cheers

Jean Baro
Dynamic Devices Ltd

Instead of reinventing the wheel, maybe you should have a look at ADO.NET Synchronization Services. http://blogs.msdn.com/Synchronizer/

|||Thanks Erik!

Is there any book (books?) to help us creating a Sync tool (Bidirectional)

Hi guys,

We have been looking for books which cover the DB synchronisation subject. We have started creating a plugin to our socket server but we quickly realised how much of a challenge it would be (We are creating it "from scratch"). The conflict problems, the order to tables (rows) be synchronised (Child - Parent) and how to delete rows were some of the problems we found. The main goal is to synchronise devices in the field (Compact framework) to the server using less bandwich as possible.
We are trying to be "agnostic" about the DB in both Server and Client side.

We are pretty sure there are books out there which cover this subject, we just couldn't find them!

We know this will be a challenge but we also know it will be a great feature to add to the server.

Does anyone could point me out to any book?

Cheers

Jean Baro
Dynamic Devices Ltd

Instead of reinventing the wheel, maybe you should have a look at ADO.NET Synchronization Services. http://blogs.msdn.com/Synchronizer/

|||Thanks Erik!

Is there any advantage for creating a default instance vs. a named

Hello, DBA outthere.
I understand that SQL Server 2000 allows 1 default instance and up to 15
named instances per server. I believe the default instance needs to be
created first.
Is there any reason to have more than 1 instance per server? Does having
more than one instance affect the overall SQL server performance? SQL
2000 server also allows server alias if users to have different server
naming.
I usually create named instance for new installation instead of letting
the SQL setup create the default instance. I had some run-ins with using
named instance. Some not-so-well-prepared application setups looks into
the registry for default instance
(HKL\Software\Microsoft\MSSQLServer\MSSQLServer). If whatever they looks
for is not there, the application will fail. Developers need to take
into account that as SQL Server allows DBA to create either default or
named instance or both.
In any event, give share your thoughts on using default or named instance.
Thanks.
JJ.Hi
Having multiple instances will stretch your resources more. You may want to
create multiple instances if you have a packaged application that require
specific settings or maybe because of issues of security.
I don't think you have to create the default instance first although I have
never tried doing otherwise!
John
"John Joe" <yukondba@.hotmail-lessspam.com> wrote in message
news:%234L5alHiEHA.592@.TK2MSFTNGP11.phx.gbl...
> Hello, DBA outthere.
> I understand that SQL Server 2000 allows 1 default instance and up to 15
> named instances per server. I believe the default instance needs to be
> created first.
> Is there any reason to have more than 1 instance per server? Does having
> more than one instance affect the overall SQL server performance? SQL
> 2000 server also allows server alias if users to have different server
> naming.
> I usually create named instance for new installation instead of letting
> the SQL setup create the default instance. I had some run-ins with using
> named instance. Some not-so-well-prepared application setups looks into
> the registry for default instance
> (HKL\Software\Microsoft\MSSQLServer\MSSQLServer). If whatever they looks
> for is not there, the application will fail. Developers need to take
> into account that as SQL Server allows DBA to create either default or
> named instance or both.
> In any event, give share your thoughts on using default or named instance.
> Thanks.
> JJ.

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?