computed field syntax

Hello:
I am trying to set the value of a computed field in a stored procedure to
the value returned by another stored procedure but can't seem to find the
proper syntax:
CREATE PROCEDURE PlanListGet // syntax invalid
AS
SELECT
Plans.PlanID,
Plans.[Name],
Plans.[Description],
IsPlanEstablished = EXEC PlanIsEstablished PlanID
FROM Plans
In the above code, IsPlanEstablished is the computed field,
PlanIsEstablished is a stored procedure that returns an integer value, and
PlanID is a parameter for the PlanIsEstablished stored procedure.
Any suggestions?
Thanks!
ChrisYOu cant do that in a select but you can retrieve the Value to store it in
a temptable an retrieve this from that.
alte Procedure Testint
(
@.Valuetopass int
)
AS
SELECT @.Valuetopass*5
CREATE TABLE #TempTable
(
ValueToReturn INT
)
INSERT INTO #TempTable
EXEC Testint 5
SELECT * from #TempTable
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"ChrisB" <pleasereplytogroup@.thanks.com> schrieb im Newsbeitrag
news:%237Cut3xWFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Hello:
> I am trying to set the value of a computed field in a stored procedure to
> the value returned by another stored procedure but can't seem to find the
> proper syntax:
> CREATE PROCEDURE PlanListGet // syntax invalid
> AS
> SELECT
> Plans.PlanID,
> Plans.[Name],
> Plans.[Description],
> IsPlanEstablished = EXEC PlanIsEstablished PlanID
> FROM Plans
> In the above code, IsPlanEstablished is the computed field,
> PlanIsEstablished is a stored procedure that returns an integer value, and
> PlanID is a parameter for the PlanIsEstablished stored procedure.
> Any suggestions?
> Thanks!
> Chris
>
>|||You can't use a stored procedure as an expression for a computed column. Per
haps you can convert the
proc into a user defined scalar function?
CREATE FUNCTION f() RETURNS INT AS BEGIN RETURN 1 END
GO
CREATE TABLE t(c1 int, c2 AS dbo.f())
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisB" <pleasereplytogroup@.thanks.com> wrote in message
news:%237Cut3xWFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Hello:
> I am trying to set the value of a computed field in a stored procedure to
the value returned by
> another stored procedure but can't seem to find the proper syntax:
> CREATE PROCEDURE PlanListGet // syntax invalid
> AS
> SELECT
> Plans.PlanID,
> Plans.[Name],
> Plans.[Description],
> IsPlanEstablished = EXEC PlanIsEstablished PlanID
> FROM Plans
> In the above code, IsPlanEstablished is the computed field, PlanIsEstablis
hed is a stored
> procedure that returns an integer value, and PlanID is a parameter for the
PlanIsEstablished
> stored procedure.
> Any suggestions?
> Thanks!
> Chris
>
>|||Looks like I'll have to take a different approach.
Thanks for the input!
Chris
"ChrisB" <pleasereplytogroup@.thanks.com> wrote in message
news:%237Cut3xWFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Hello:
> I am trying to set the value of a computed field in a stored procedure to
> the value returned by another stored procedure but can't seem to find the
> proper syntax:
> CREATE PROCEDURE PlanListGet // syntax invalid
> AS
> SELECT
> Plans.PlanID,
> Plans.[Name],
> Plans.[Description],
> IsPlanEstablished = EXEC PlanIsEstablished PlanID
> FROM Plans
> In the above code, IsPlanEstablished is the computed field,
> PlanIsEstablished is a stored procedure that returns an integer value, and
> PlanID is a parameter for the PlanIsEstablished stored procedure.
> Any suggestions?
> Thanks!
> Chris
>
>

Computed field references

I am currently developing a stored procedure that includes a number of computed fields. Is it possible to reference a computed value, (eg. FLdA), or do I need to CREATE a temp file and then reference the FldA and FldB values. I have simplified my code, it is much more extensive in that there are numerous WHEN clauses attached to each FldA and FldB computation.

SELECT FldA = CASE
WHEN ... THEN CurQty * 1.5
WHEN ... THEN CurQty * 1.75 ELSE 0 END),
FldB = CASE ....
NewValue = CASE
WHEN ... THEN FldA * CurValue
WHEN ... THEN FldB * CurValue
etc.I'm not sure I understand the question...Do you want to reference the value again inside the sproc?

Then Yes...use a local table variable...

If it's being part of a result set being passed back, then you're already refrencing it...

I'm confused...|||I want to reference the value within the sproc and pass only those records where the OldValue is not equal to the NewValue. In the case I mentioned, I am trying to reference FldA and FldB to compute the NewValue from within the same SELECT stmt, but SQL does not let me reference the FldA and FldB computed values. Is that as clear as mud?|||Reference them, where? In the same query? Or later on in the sproc.

If it's later on in the sproc

SELECT <whatever> INTO #TEMP FROM <whatever>

Then just query the local temp table...

Is that what you mean?|||I'm trying to reference them in the same query.

The INSERT .. INTO stmt seems cumbersome as it appears I would have to define each field as part of the CREATE TABLE stmt. Can't see why it doesn't just pickup the data types from the TABLE.|||Well it's not data type is it...it's column names

Well do this...Keep your computed stuff isolated...and join to a derived table

SELECT * FROM (SELECT <your derived columns> FROM table join table ect) AS A
LEFT JOIN B ON a.key = b.key
WHERE <now you can reference the derived column name> = 'bananas'

Whatever...

I fyou make the derivation this derived table you'll be able to reference the column names you made up...|||Why so complicated?

select * from (
SELECT FldA = CASE
WHEN ... THEN CurQty * 1.5
WHEN ... THEN CurQty * 1.75 ELSE 0 END),
FldB = CASE ....
NewValue = CASE
WHEN ... THEN CASE
WHEN ... THEN CurQty * 1.5
WHEN ... THEN CurQty * 1.75 ELSE 0 END * CurValue
WHEN ... THEN CASE .... * CurValue
) x
where OldValue != NewValue

In other words, instead of trying to reference FldA, use its CASE...END when calculating NewValue. Same with FldB.|||I had mentioned earlier that the code was simplified. The CASE logic is fairly complex, could be up to 20 lines of code. That would mean that I would have to repeat the code everytime the field ('FldA') was referenced. I may just leave the logic in VBA code as it seems a lot easier to manipulate fields in code. My goal was to restrict the query ouput lines so the Access code would run quicker.|||Thanks Brett ... I'll give it a go.|||Here's a model

USE Northwind
GO
SELECT SUM(OutOfBusinessDays) AS VacationDays
FROM (
SELECT ShipLate-ShipDelay AS OutOfBusinessDays
FROM (
SELECT DATEDIFF(dd,OrderDate,ShippedDate) As ShipDelay
, DATEDIFF(dd,OrderDate,RequiredDate) As ShipLate
FROM Orders
) AS XXX
) AS DerivedTableName

composite stored procedure

Hi,
I am trying to write a procedure in SQL which will composite values in a table. I have an increment list, which increments by 0.1, I need to average values so I produce composites for 0-1, 1-2 etc grouping by id.
The script below produces a table then a procedure, but I'm stuck on the syntax and get the error

Column 'Increment.increment' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

The script will run on the master database.
The end result should look like the following:

thanks for any help

id inc_from inc_to comp_val
-
1a 0 1 4.6

1a 1 2 4

1b 0 1 3.9

1b 1 2 4.5

--

drop table increment

create table increment(id varchar(10),increment float,value float)

insert into increment(id,increment,value) values('1a','0','6')
insert into increment(id,increment,value) values('1a','0.1','7')
insert into increment(id,increment,value) values('1a','0.2','4')
insert into increment(id,increment,value) values('1a','0.3','6')
insert into increment(id,increment,value) values('1a','0.4','2')
insert into increment(id,increment,value) values('1a','0.5','5')
insert into increment(id,increment,value) values('1a','0.6','8')
insert into increment(id,increment,value) values('1a','0.7','5')
insert into increment(id,increment,value) values('1a','0.8','1')
insert into increment(id,increment,value) values('1a','0.9','2')
insert into increment(id,increment,value) values('1a','1','3')
insert into increment(id,increment,value) values('1a','1.1','5')
insert into increment(id,increment,value) values('1a','1.2','4')
insert into increment(id,increment,value) values('1a','1.3','3')
insert into increment(id,increment,value) values('1a','1.4','6')
insert into increment(id,increment,value) values('1a','1.5','2')
insert into increment(id,increment,value) values('1a','1.6','1')
insert into increment(id,increment,value) values('1a','1.7','6')
insert into increment(id,increment,value) values('1a','1.8','6')
insert into increment(id,increment,value) values('1a','1.9','4')
insert into increment(id,increment,value) values('1a','2','2')
insert into increment(id,increment,value) values('1b','0','4')
insert into increment(id,increment,value) values('1b','0.1','7')
insert into increment(id,increment,value) values('1b','0.2','2')
insert into increment(id,increment,value) values('1b','0.3','1')
insert into increment(id,increment,value) values('1b','0.4','3')
insert into increment(id,increment,value) values('1b','0.5','5')
insert into increment(id,increment,value) values('1b','0.6','6')
insert into increment(id,increment,value) values('1b','0.7','4')
insert into increment(id,increment,value) values('1b','0.8','3')
insert into increment(id,increment,value) values('1b','0.9','6')
insert into increment(id,increment,value) values('1b','1','3')
insert into increment(id,increment,value) values('1b','1.1','5')
insert into increment(id,increment,value) values('1b','1.2','4')
insert into increment(id,increment,value) values('1b','1.3','3')
insert into increment(id,increment,value) values('1b','1.4','6')
insert into increment(id,increment,value) values('1b','1.5','8')
insert into increment(id,increment,value) values('1b','1.6','1')
insert into increment(id,increment,value) values('1b','1.7','6')
insert into increment(id,increment,value) values('1b','1.8','6')
insert into increment(id,increment,value) values('1b','1.9','4')
insert into increment(id,increment,value) values('1b','2','2')

go

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'usp_composite' AND type = 'FN')
DROP FUNCTION [dbo].[usp_composite]
GO

/*******************************************************************************
Usage: exec usp_composite
********************************************************************************/

Create Procedure usp_composite
As
Select Id,
Ceiling(Increment) - 1 As Inc_From,
Ceiling(Increment) As Inc_To,
Avg(Value * 1.0) as comp_val
From Increment
Group By Id, Ceiling(Increment),Increment.increment
Order By Id, Ceiling(Increment)

You could do something like below:

select id, inc_from, inc_to, avg(value)
from (
select id
, case when ceiling(increment) - 1 < 0 then 0 else ceiling(increment) - 1 end as inc_from
, case when ceiling(increment) - 1 < 0 then ceiling(increment) + 1 else ceiling(increment) end as inc_to
, value
from increment
) as t
group by id, inc_from, inc_to;

You are getting the error because your GROUP BY clause doesn't include the expressions that are in any of the aggregate functions. So it will work if you include ceiling(Increment) -1 also in the GROUP BY clause.

|||

Thanks for you help.

How do I modify the sql if the last depth value is correct, at the moment the sql returns 1 - 2 and not 1 - 1.95, see the code below with the create table.

drop table increment

create table increment(id varchar(10),increment float,value float)

insert into increment(id,increment,value) values('1a','0','6')
insert into increment(id,increment,value) values('1a','0.1','7')
insert into increment(id,increment,value) values('1a','0.2','4')
insert into increment(id,increment,value) values('1a','0.3','6')
insert into increment(id,increment,value) values('1a','0.4','2')
insert into increment(id,increment,value) values('1a','0.5','5')
insert into increment(id,increment,value) values('1a','0.6','8')
insert into increment(id,increment,value) values('1a','0.7','5')
insert into increment(id,increment,value) values('1a','0.8','1')
insert into increment(id,increment,value) values('1a','0.9','2')
insert into increment(id,increment,value) values('1a','1','3')
insert into increment(id,increment,value) values('1a','1.1','5')
insert into increment(id,increment,value) values('1a','1.2','4')
insert into increment(id,increment,value) values('1a','1.3','3')
insert into increment(id,increment,value) values('1a','1.4','6')
insert into increment(id,increment,value) values('1a','1.5','2')
insert into increment(id,increment,value) values('1a','1.6','1')
insert into increment(id,increment,value) values('1a','1.7','6')
insert into increment(id,increment,value) values('1a','1.8','6')
insert into increment(id,increment,value) values('1a','1.9','4')
insert into increment(id,increment,value) values('1a','2','2')
insert into increment(id,increment,value) values('1b','0','4')
insert into increment(id,increment,value) values('1b','0.1','7')
insert into increment(id,increment,value) values('1b','0.2','2')
insert into increment(id,increment,value) values('1b','0.3','1')
insert into increment(id,increment,value) values('1b','0.4','3')
insert into increment(id,increment,value) values('1b','0.5','5')
insert into increment(id,increment,value) values('1b','0.6','6')
insert into increment(id,increment,value) values('1b','0.7','4')
insert into increment(id,increment,value) values('1b','0.8','3')
insert into increment(id,increment,value) values('1b','0.9','6')
insert into increment(id,increment,value) values('1b','1','3')
insert into increment(id,increment,value) values('1b','1.1','5')
insert into increment(id,increment,value) values('1b','1.2','4')
insert into increment(id,increment,value) values('1b','1.3','3')
insert into increment(id,increment,value) values('1b','1.4','6')
insert into increment(id,increment,value) values('1b','1.5','8')
insert into increment(id,increment,value) values('1b','1.6','1')
insert into increment(id,increment,value) values('1b','1.7','6')
insert into increment(id,increment,value) values('1b','1.8','6')
insert into increment(id,increment,value) values('1b','1.9','4')
insert into increment(id,increment,value) values('1b','1.95','2')

go

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'usp_composite')

DROP PROCEDURE usp_composite

GO

/*******************************************************************************
Usage: exec usp_composite
********************************************************************************/

Create Procedure usp_composite
As
select id, inc_from, inc_to, avg(value)
from (
select id
, case when ceiling(increment) - 1 < 0 then 0 else ceiling(increment) - 1 end as inc_from
, case when ceiling(increment) - 1 < 0 then ceiling(increment) + 1 else ceiling(increment) end as inc_to
, value
from increment
) as t
group by id, inc_from, inc_to
go

|||

CELING will give the next closest integer value. So that value may not be in your table. You could check each max or min value against the values in your table. But it becomes complicated if you want to do it for each boundary value like 0 or 1 in 0-1, 1 or 2 in 1-2 and so on. And depending on how many possible values you have in your table it is going to be difficult. Can you answer some of the questions below?

1. What is the min/max range for the values in your Value column?

2. Do you want each range to be only the values in the column (like 0-1, 1-2, 2-3 and so on)?

If you always want only the actual min/max value within each ID as part of the min/max range buckets (inc_from , inc_to) then you can do something like below. The code will adjust the min/max values of each id based on the values in the table:

select id, inc_from, inc_to, avg(value)
from (
select i.id

/* adjust min if necessary based on the actual min value */
, case when ceiling(i.increment) - 1 < 0
then (case when 0 < i2.min_inc then i2.min_inc else 0 end)
else (case when ceiling(i.increment) - 1 < i2.min_inc then i2.min_inc else ceiling(i.increment) - 1 end)
end as inc_from
/* adjust min if necessary based on the actual max value */

, case when ceiling(i.increment) - 1 < 0
then (case when ceiling(i.increment) + 1 > i2.max_inc then i2.max_inc else ceiling(i.increment) + 1 end)
else (case when ceiling(i.increment) > i2.max_inc then i2.max_inc else ceiling(i.increment) end)
end as inc_to
, i.value
from increment as i
join (
select i1.id, min(i1.increment) as min_inc, max(i1.increment) as max_inc
from increment as i1
group by i1.id
) as i2
on i2.id = i.id
) as t
group by id, inc_from, inc_to;

|||

Many thanks, this works fine. I need to still average the data between 0-1,1-2,2-3 and so on but I need to display the correct increment values i.e. 1-1.95 and also if for example 0.5-1 rather than 0-1.

thanks

|||Put the increment value in your derived table. Now in you outer select, add Min(increment) and Max(increment). You get both the to and from values and the actual increments in your table.

Complex where clause?

Hi,

I have a stored procedure with a few parameters. One of them is @.ProviderParam and it defaults to Null. If a value is passed into that parameter when the sp is called, I need to include a check for the provider in the where clause, like this:

Where <some other stuff> AND Provider = @.ProviderParam

But if nothing is passed into that parameter (and it defaults to Null), I need to do nothing with provider in the where clause. The where clause would look like this:

Where <some other stuff>

I believe the solution is to use a CASE statement in the where clause somehow, but I'm not sure how to proceed. Can anyone please help?

Thanks.

One way is to do something like:

AND ( @.ProviderParam is null or
Provider = @.ProviderParam)

If your PROVIDER column is a non-null column you also might be able to use:

AND Provider = ISNULL (@.ProviderParam, Provider)

Again, the column must be a NOT NULL column or this filter will not work correctly whenever both @.ProviderParam and Provider are null.

|||

If I understand your issue correctly, you wish to optionally provide a value for the @.ProviderParam, use it in the WHERE clause if it is available, otherwise if it is NULL, ignore @.ProviderParam. If so, this may work for you:

AND Provider = coalesce( @.ProviderParam, Provider )

As Kent indicated, Provider MUST be a NOT NULL column for this approach to work properly.

|||The advantage of coalesce is that it will also work with DB2 whereas ISNULL will not.|||

Great stuff. Thank you, both.

Provider is a null column. DB2 is not a factor. Kent's 1st solution is really elegant. It doesn't involve a function call, which is efficient, and it's so simple. Just too much elegance for me to pass by! :)

Thanks, again, for these insights.

|||

Just a note to say that the other advantage of COALESCE is that it can take an arbitrary number of arguments and returns the first one that is not null.

SET A_Value = COALESCE(First_Choice, Second_Choice, Desperate_Choice, Default_Value)

This can be useful if you have something like 3 different names you could use before giving up and using 'UNKNOWN'.

complex stored procedure on history table

Hi All,

I have a table that hold status history records for cases. In this table is a status field with values, opened, assigned, or complete. Each case can be assigned a number of times before it is complete, and can be reassigned. I have the need to run a query that will get each case that is still assigned, and not yet complete. I wrote a stored procedure that contains a cursor containing each case, and get the last status history record for each case and puts it into a temp table to return to the user, but is hurting performance as there are .5 million records here. Does anyone know of a better way of doing this?

Thanks in advance : )

Found an answer elsewhere using correlated sub queries. thanks

Complex Select statement advise needed please

Hey there. I was wondering if someone could advise me whether or not i've
writted an effective/efficent stored procedure.
Basically the below procedure will be run from a .net application and the
values passed the the parameters will be 1 or null. It will allow the user
to select as many or as little options with like from a checkbox list and
based on what is entered a 1 or null value will be passed in and a results
set passed back.
I was wondering if this is the best way to do such a query or am i on the
wrong track below works fine but I don't know if its the best way to go abou
t
things.
I'd also like to try and order the results somehow but i'm not sure how to
do this as I've know way of knowing how many of the results are part of each
group. The table i'm querying looks like this.
u_forname u_surname b_publications b_consultation b_freedom b_policyt
etc
Stephen Cairns 0 1
0 0
Steve Jones 1 0
1 0
Laura McCall 1 0
0 0
Andrea Jones 0 0
0 1
etc.................
Basically when users can search the table for results equal to 1 from the
fields which they select in the checkbox list.
I hope someone is able to advise me. Thanks for your help
Here is the stored procedure
CREATE PROCEDURE [RegisteredUsers_SpecificSubscribers]
@.publications int,
@.consultation int,
@.freedom int,
@.judgments int,
@.legislation int,
@.policy int,
@.press int,
@.questions int,
@.strategies int,
@.targets int,
@.using int,
@.judgment int ,
@.sentence int,
@.practice int,
@.family int
AS
SET NOCOUNT ON
SELECT u_logon_name, u_firstname, u_surname, u_account_name
FROM UserObject
WHERE
[b_publications] = @.publications OR
([b_consultation] = @.consultation) OR
([b_freedom] = @.freedom) OR
([b_judgments] = @.judgments) OR
([b_legislation] = @.legislation) OR
([b_policy] = @.policy) OR
([b_press] = @.press) OR
([b_questions] = @.questions) OR
([b_strategies] = @.strategies) OR
([b_targets] = @.targets) OR
([b_using] = @.using) OR
([b_judgment] = @.judgment) OR
([b_sentence] = @.sentence) OR
([b_practice] = @.practice) OR
([b_family] = @.family)
GOStephen
Read up this article
http://www.sommarskog.se/dyn-search.html
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:E693AF7A-FA77-4E90-8F5B-4F944E0E3C91@.microsoft.com...
> Hey there. I was wondering if someone could advise me whether or not i've
> writted an effective/efficent stored procedure.
> Basically the below procedure will be run from a .net application and the
> values passed the the parameters will be 1 or null. It will allow the
user
> to select as many or as little options with like from a checkbox list and
> based on what is entered a 1 or null value will be passed in and a results
> set passed back.
> I was wondering if this is the best way to do such a query or am i on the
> wrong track below works fine but I don't know if its the best way to go
about
> things.
> I'd also like to try and order the results somehow but i'm not sure how to
> do this as I've know way of knowing how many of the results are part of
each
> group. The table i'm querying looks like this.
> u_forname u_surname b_publications b_consultation b_freedom b_policyt
> etc
> Stephen Cairns 0 1
> 0 0
> Steve Jones 1 0
> 1 0
> Laura McCall 1 0
> 0 0
> Andrea Jones 0 0
> 0 1
> etc.................
> Basically when users can search the table for results equal to 1 from the
> fields which they select in the checkbox list.
> I hope someone is able to advise me. Thanks for your help
> Here is the stored procedure
> CREATE PROCEDURE [RegisteredUsers_SpecificSubscribers]
> @.publications int,
> @.consultation int,
> @.freedom int,
> @.judgments int,
> @.legislation int,
> @.policy int,
> @.press int,
> @.questions int,
> @.strategies int,
> @.targets int,
> @.using int,
> @.judgment int ,
> @.sentence int,
> @.practice int,
> @.family int
> AS
> SET NOCOUNT ON
> SELECT u_logon_name, u_firstname, u_surname, u_account_name
> FROM UserObject
> WHERE
> [b_publications] = @.publications OR
> ([b_consultation] = @.consultation) OR
> ([b_freedom] = @.freedom) OR
> ([b_judgments] = @.judgments) OR
> ([b_legislation] = @.legislation) OR
> ([b_policy] = @.policy) OR
> ([b_press] = @.press) OR
> ([b_questions] = @.questions) OR
> ([b_strategies] = @.strategies) OR
> ([b_targets] = @.targets) OR
> ([b_using] = @.using) OR
> ([b_judgment] = @.judgment) OR
> ([b_sentence] = @.sentence) OR
> ([b_practice] = @.practice) OR
> ([b_family] = @.family)
> GO

Complex Procedure / Query

PAYROLL
PID|PNAME|STARTDATE|ENDDATE
1|BATCH1|2004-01-01|2004-01-04
2|BATCH2|2004-01-01|2004-01-04

TIMEENTRY
TID|PID|USERID|DATE|HOURS
1|1|49|2004-01-01|7
2|1|49|2004-01-02|8
3|1|49|2004-01-03|8
4|1|49|2004-01-04|6
.
.
.
11|1|50|2004-01-01|5
12|1|50|2004-01-02|2
13|1|50|2004-01-03|8
14|1|50|2004-01-04|8

21|2|49|2004-01-01|4
22|2|49|2004-01-02|2
23|2|49|2004-01-03|2
24|2|49|2004-01-04|8
.
.
.
31|2|50|2004-01-01|8
32|2|50|2004-01-02|8
33|2|50|2004-01-03|8
34|2|50|2004-01-04|8

- contains timeentry(HOURS) for different users(USERID) FOR different payroll batches(PID) for different dates(DATE)

the query/procedure should return data this way

Userid |PID |2004-01-01 |2004-01-02 |2004-01-03 |2004-01-04 '<== HEADER ROW
1 |49 |7 |8 |8 |6
1 |50 |5 |2 |8 |8

2 |49 |5 |2 |8 |8
2 |50 |8 |8 |8 |8

Im not sure how to start cause the dates in the headers are the start date and end date of the Payroll

Any suggestions or help is appreciated

thanksHopefully you can avoid dynamic SQL on this one. A lot depends on how the data is going to be displayed. Are you piping it to Crystal Reports or some other reporting tool?

Are the number of payroll periods fixed, or can a maximum number be set? For example; 12 for the year.

You should try to design your report so that instead of dates as headers you display month numbers. You then return the starting month in your dataset and have your reporting tool concoct the headers based on the month numbers and the starting date. This way you don't have different headers every time you execute, which Crystal Reports would choke on as fast as George Bush eating a pretzel.

complex insert statement

I have this stored procedure that returns a rowid, distance. It has a latitude, longitude, and range as inputs, it takes the latitude and longitude and computes a distance with every lat/long in a table PL_CustomerGeocode. Once that distance is computed it compares that distance with the range, and then returns the rowid, distance if the distance is <= range. I have the SELECT statement down, but now i just need to enter this information into a seperate table PL_Distance with (rowid, distance) as columns. The sql statement is as follows, and i cant figure out where the rowid part is an the distance part is:

DECLARE @.DegreesToRadians float

SET @.DegreesToRadians = Pi()/180

SELECT rowid, Cast(distance As numeric(9,3)) AS distance
FROM (SELECT rowid, CASE WHEN @.srcLat = geocodeLat And @.srcLong = geocodeLong THEN 0.0
WHEN ABS(Arc) > 1 THEN 0.0
ELSE 3963.1 * 2 * asin(Power(Arc, 0.5)) END AS distance
FROM (SELECT Power(sin(DLat/2),2) + cos(@.srcLat*@.DegreesToRadians)*cos(geocodeLat*@.DegreesToRadians)*Power(sin(DLong/2),2) AS Arc, rowid,geocodeLat,geocodeLong
FROM (SELECT @.srcLong*@.DegreesToRadians-geocodeLong*@.DegreesToRadians AS DLong,
@.srcLat*@.DegreesToRadians-geocodeLat*@.DegreesToRadians AS DLat,
rowid,
geocodeLat,
geocodeLong
FROM dbo.PL_CustomerGeoCode) AS x) AS y) AS z
WHERE distance <= @.range

Can't you just insert the rows returned by your query?

DECLARE @.DegreesToRadians float
SET @.DegreesToRadians = Pi()/180
INSERT INTO PL_Distance (rowid, distance)
SELECT rowid, Cast(distance As numeric(9,3)) AS distance
FROM (SELECT rowid, CASE WHEN @.srcLat = geocodeLat And @.srcLong = geocodeLong THEN 0.0
WHEN ABS(Arc) > 1 THEN 0.0
ELSE 3963.1 * 2 * asin(Power(Arc, 0.5)) END AS distance
FROM (SELECT Power(sin(DLat/2),2) + cos(@.srcLat*@.DegreesToRadians)*cos(geocodeLat*@.DegreesToRadians)*Power(sin(DLong/2),2) AS Arc, rowid,geocodeLat,geocodeLong
FROM (SELECT @.srcLong*@.DegreesToRadians-geocodeLong*@.DegreesToRadians AS DLong,
@.srcLat*@.DegreesToRadians-geocodeLat*@.DegreesToRadians AS DLat,
rowid,
geocodeLat,
geocodeLong
FROM dbo.PL_CustomerGeoCode) AS x) AS y) AS z
WHERE distance <= @.range

Compiled plans and syscacheobjects

Hi,
I'm wondering if someone knows what causes a compiled plan to get
dropped from the cache. We've got one fairly complicated procedure
that gets removed from cache every few minutes, and then compiled again
the next time its run. The compile takes 10-15 seconds of cpu. The
procedure is used 10-20 times per minute, so i dont think it's because
of lack of use. And we have other procedures called as often that dont
get dropped so frequently. As well, we have the identical procedure,
named differently (called from a different program) that shows the same
behavior, it gets dropped from the cache frequently.
Could it be related to the data the query is looking it?
Any ideas appreciated. Oh yeah, SQL Server 2000, SP3 i think.
Thanks,
Trevortdunsford@.gmail.com wrote:
> Hi,
> I'm wondering if someone knows what causes a compiled plan to get
> dropped from the cache. We've got one fairly complicated procedure
> that gets removed from cache every few minutes, and then compiled again
> the next time its run. The compile takes 10-15 seconds of cpu. The
> procedure is used 10-20 times per minute, so i dont think it's because
> of lack of use. And we have other procedures called as often that dont
> get dropped so frequently. As well, we have the identical procedure,
> named differently (called from a different program) that shows the same
> behavior, it gets dropped from the cache frequently.
> Could it be related to the data the query is looking it?
> Any ideas appreciated. Oh yeah, SQL Server 2000, SP3 i think.
> Thanks,
> Trevor
>
Does this procedure create temp tables?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||No, it uses table variables:
declare table @.tbl (...)
insert into @.tbl (...)
select ...
select * from @.tbl
Tracy McKibben wrote:
> tdunsford@.gmail.com wrote:
> > Hi,
> >
> > I'm wondering if someone knows what causes a compiled plan to get
> > dropped from the cache. We've got one fairly complicated procedure
> > that gets removed from cache every few minutes, and then compiled again
> > the next time its run. The compile takes 10-15 seconds of cpu. The
> > procedure is used 10-20 times per minute, so i dont think it's because
> > of lack of use. And we have other procedures called as often that dont
> > get dropped so frequently. As well, we have the identical procedure,
> > named differently (called from a different program) that shows the same
> > behavior, it gets dropped from the cache frequently.
> >
> > Could it be related to the data the query is looking it?
> >
> > Any ideas appreciated. Oh yeah, SQL Server 2000, SP3 i think.
> >
> > Thanks,
> >
> > Trevor
> >
> Does this procedure create temp tables?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||tdunsford@.gmail.com wrote:
> No, it uses table variables:
> declare table @.tbl (...)
> insert into @.tbl (...)
> select ...
> select * from @.tbl
>
Have you reviewed this KB article?
http://support.microsoft.com/kb/243586
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||>From what i can understand, this talks about recompiles. I may be
mistaken, but I dont think thats what i'm seeing here. There is no
sp:recompile event. All the cpu/delay occurs before any of the
statements are executed (ie before SP:Starting).
I see 2 options, KEEP PLAN and KEEPFIXED PLAN. Would this help keep
the compile plan in the cache as well, not just the execution plan?
Because recompiling the execution plan takes no time at all...its the
compile plan thats the killer.
Thanks,
Trevor
Tracy McKibben wrote:
> tdunsford@.gmail.com wrote:
> > No, it uses table variables:
> >
> > declare table @.tbl (...)
> >
> > insert into @.tbl (...)
> > select ...
> >
> > select * from @.tbl
> >
> Have you reviewed this KB article?
> http://support.microsoft.com/kb/243586
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Yes you should look into these. The execution plan is based off the compiled
plan so you can't have an executionplan without the compiled plan.
--
Andrew J. Kelly SQL MVP
<tdunsford@.gmail.com> wrote in message
news:1161355332.974366.216790@.e3g2000cwe.googlegroups.com...
> >From what i can understand, this talks about recompiles. I may be
> mistaken, but I dont think thats what i'm seeing here. There is no
> sp:recompile event. All the cpu/delay occurs before any of the
> statements are executed (ie before SP:Starting).
> I see 2 options, KEEP PLAN and KEEPFIXED PLAN. Would this help keep
> the compile plan in the cache as well, not just the execution plan?
> Because recompiling the execution plan takes no time at all...its the
> compile plan thats the killer.
> Thanks,
> Trevor
>
> Tracy McKibben wrote:
>> tdunsford@.gmail.com wrote:
>> > No, it uses table variables:
>> >
>> > declare table @.tbl (...)
>> >
>> > insert into @.tbl (...)
>> > select ...
>> >
>> > select * from @.tbl
>> >
>> Have you reviewed this KB article?
>> http://support.microsoft.com/kb/243586
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>

Compilation error on store procedure

Hi all,
Here is my error: Server: Msg 245, Level 16, State 1, Procedure NewAcctTypeSP, Line 10
Syntax error converting the varchar value'The account type is already exist' to a column of data type int.
Here is my procedure:
ALTER PROC NewAcctTypeSP
(@.acctType VARCHAR(20), @.message VARCHAR (40) OUT)
AS
BEGIN
--checks if the new account type is already exist
IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @.acctType)
BEGIN
SET @.message = 'The account type is already exist'
RETURN @.message
END

BEGIN TRANSACTION
INSERT INTO AcctTypeCatalog (acctType) VALUES (@.acctType)
--if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction
IF @.@.error <> 0 OR @.@.rowcount <> 1
BEGIN
ROLLBACK TRANSACTION
SET @.message = 'Insertion failure on AcctTypeCatalog table.'
RETURN @.message

END
ELSE
BEGIN

COMMIT TRANSACTION
END

RETURN @.@.ROWCOUNT
END
GO

--execute the procedure
DECLARE @.message VARCHAR (40);
EXEC NewAcctTypeSP 'CDs', @.message;
I am not quite sure where I got a type converting error in my code and anyone can help me solve it?
(p.s. I want to return the @.message value to my .aspx page)
Thanks.

You should probably get rid of the RETURN @.@.ROWCOUNT line. It may be getting confused that sometimes you return a varchar and other times int (the rowcount).
Marcie|||Hi Marcie,
I have get rid of the @.@.RowCount, however, I didn't get value from my @.message, it just returned 'undefined'.
do u know why, and how can i fix it?
Thanks.|||Where is it undefined, from your code or still running your test proc?
Marcie|||

when I executed my .aspx page, I input a duplicated value in a field intentionally and since it should return an error message that I have defined in my dtore procedure, however, I just got 'undefined' instead of my error message...
so, here is my .aspx code:
public function SubmitClick (sender:Object, e:EventArgs) : void
{
if (Page.IsValid)
{
myConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"));
var acctTypeDA : SqlDataAdapter = new SqlDataAdapter ("select * from AcctTypeCatalog", myConnection);

acctTypeDA.InsertCommand = new SqlCommand("NewAcctType", myConnection);
acctTypeDA.InsertCommand.CommandType = CommandType.StoredProcedure;
acctTypeDA.InsertCommand.Parameters.Add(new SqlParameter("@.acctType", SqlDbType.VarChar, 20)).Value = accountType.Text;

var myParm : SqlParameter = acctTypeDA.InsertCommand.Parameters.Add("@.message", SqlDbType.VarChar, 40);
myParm.Direction = ParameterDirection.Output;

<%--try to get value from @.message and assign it to the variable --%>
var msg : String = acctTypeDA.InsertCommand.Parameters("@.message").Value;
msgLabel.Text = msg;

BindGrid();

}
else
{
msgLabel.Text = "The Page contains error!"
}
}
my store procedure:
ALTER PROC NewAcctTypeSP
(@.acctType VARCHAR(20), @.message VARCHAR (40) OUT)
AS
BEGIN
--checks if the new account type is already exist
IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @.acctType)
BEGIN
SET @.message = 'The account type is already exist'
RETURN
END

BEGIN TRANSACTION
INSERT INTO AcctTypeCatalog (acctType) VALUES (@.acctType)

--if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction
IF @.@.error <> 0 OR @.@.rowcount <> 1
BEGIN
ROLLBACK TRANSACTION
SET @.message = 'Insertion failure on AcctTypeCatalog table.'
RETURN
END
ELSE
BEGIN
SET @.message = 'Insertion Successful!'
COMMIT TRANSACTION
END
RETURN
END
GO
I have tested my procedure, it works fine in SQL server, however, don't know why I couldn't get the value from @.message on my .aspx page.
Any idea?
Thanks

|||What language is that? You won't be able to get the value of your OUTPUT parameter until right after the command has executed, something like an ExecuteNonQuery line.
Marcie|||hi Marcie,
I used JScript to create my page with using the store procedure. (JScipt is very similar with C#)
I have reviewed the tutorial from the ASP.net and it doesn't have any ExecuteNonQuery statement on the following example:

<script language="JScript" runat="server">
publicfunction GetEmployees_Click(sender : Object, e : EventArgs) :void
{
var myConnection : SqlConnection =new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("NWString"));
var myCommand : SqlDataAdapter =new SqlDataAdapter("SalesByCategory", myConnection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@.CategoryName", SqlDbType.NVarChar, 15));
myCommand.SelectCommand.Parameters("@.CategoryName").Value = SelectCategory.Value;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@.OrdYear", SqlDbType.NVarChar, 4));
myCommand.SelectCommand.Parameters("@.OrdYear").Value = SelectYear.Value;
var ds : DataSet =new DataSet();
myCommand.Fill(ds, "Sales");
MyDataGrid.DataSource=ds.Tables("Sales").DefaultView;
MyDataGrid.DataBind();
}
</script>

So, should I use the ExecuteNonQuery statement to get my output parameter? any example you could show me for getting back the output parameter value with using store procedure(it's ok if that's written in VB or C#)?
i have tried to find some material about this problem, but can't get any of it ...
Thanks again.
|||In the example you show here, the Command gets executed in the myCommand.Fill line, your code didn't have anything like that.
Since you have your command object set up as the InsertCommand of a DataAdapter object, I believe your command would automatically fire when the .Update method is called.
Marcie|||The problem is that return parameters in T-SQL arealways integers. That is whay you got the compilation error, and why you are not getting the value you expect in your client-side code.
Even though you have @.message defined as an output parameter, the RETURN @.message is causing you to error out.|||

Thanks pjmcb,
I already got my problem fixed.

Comparing two strings inside a stored procedure

Basically I have two strings. Both strings will contain similar data because the 2nd string is the first string after an update of the first string takes place. Both strings are returned in my Stored Procedure

For example:
String1 = "Here is some data. lets type some more data"
String2 = "Here's some data. Lets type some data here"

I would want to change string2 (inside my Stored Procedure) to show the changed/added text highlighted and the deleted text with a strike though.

So I would want string2 to look like this

string2 = "Here<font color = \"#00FF00\">'s</font> <strike>is</strike> some data. <font color = \"#00FF00\">L</font>ets type some <strike>more</strike> data <font color = \"#00FF00\">here</font>"

Is there an way to accomplish this inside a stored procedure?

First, you'll have to decide what algorithm determines what matches and what is different. For example, if you just compare the strings by the same position, you'll get the first part the same, the end of the first string as deleted and the end of the second string as inserted. I don't know how to implement the algorithm but I can think of some possibilities:

1. Regular expressions may have some support for this.

2. Track the user's changes character by character on the client.

3. Find a differencing algorithm / tool somewhere.

Once you've solved that, I suggest you pass the difference string to your procedure. You'll have much more flexibility and support in .NET than in SQL.

Sorry I couldn't help more. Good Luck.

|||Yes, you can. However, that is best left up to an application as it's a matter of presentation and application logic.