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.

Composing a date from date parts

I have three date parts namely,
Year
Month
Date/Day
as integer values stored in one column each in a table in a SQL Server
2000 database. I need a function to serialize/compose/create a datetime
type out of them so I could use that in a query (pseudosyntax) as
below:
SELECT CreateDate(iYear, iMonth, iDay) As TheDateIWant FROM ThatTable
and then later on, I probably want to use an aggregation/computation on
that like, the MAX function, may be:
SELECT MAX(CreateDate(iYear, iMonth, iDay)) As TheDateIWant FROM
ThatTable
Thanks!DECLARE @.Year int
DECLARE @.Month int
DECLARE @.Day int
SET @.Year = 2005
SET @.Month = 02
SET @.Day = 27
SELECT
CAST(
CAST(@.Year AS char(4))
+RIGHT('0' + CAST(@.Month AS varchar(2)), 2)
+RIGHT('0' + CAST(@.Day AS varchar(2)), 2)
AS datetime)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<aroraamit81@.gmail.com> wrote in message
news:1132746277.481717.168850@.g49g2000cwa.googlegroups.com...
>I have three date parts namely,
> Year
> Month
> Date/Day
> as integer values stored in one column each in a table in a SQL Server
> 2000 database. I need a function to serialize/compose/create a datetime
> type out of them so I could use that in a query (pseudosyntax) as
> below:
>
> SELECT CreateDate(iYear, iMonth, iDay) As TheDateIWant FROM ThatTable
>
> and then later on, I probably want to use an aggregation/computation on
> that like, the MAX function, may be:
>
> SELECT MAX(CreateDate(iYear, iMonth, iDay)) As TheDateIWant FROM
> ThatTable
>
> Thanks!
>|||Here is another solution. This will work only with 4 digit year.:
declare @.year int, @.month int, @.day int
set @.year = 2005
set @.month = 11
set @.day = 23
select cast(convert(char(8),@.year * 10000 + @.month * 100 + @.day) as datetime
)
"aroraamit81@.gmail.com" wrote:

> I have three date parts namely,
> Year
> Month
> Date/Day
> as integer values stored in one column each in a table in a SQL Server
> 2000 database. I need a function to serialize/compose/create a datetime
> type out of them so I could use that in a query (pseudosyntax) as
> below:
>
> SELECT CreateDate(iYear, iMonth, iDay) As TheDateIWant FROM ThatTable
>
> and then later on, I probably want to use an aggregation/computation on
> that like, the MAX function, may be:
>
> SELECT MAX(CreateDate(iYear, iMonth, iDay)) As TheDateIWant FROM
> ThatTable
>
> Thanks!
>|||Or for fun
DECLARE @.y INT,@.m INT,@.d INT
SET @.y=2005
SET @.m=2
SET @.d=27
select cast(rtrim(@.y*10000+@.m*100+@.d) as datetime)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uK5YPUC8FHA.3880@.TK2MSFTNGP12.phx.gbl...
> DECLARE @.Year int
> DECLARE @.Month int
> DECLARE @.Day int
> SET @.Year = 2005
> SET @.Month = 02
> SET @.Day = 27
> SELECT
> CAST(
> CAST(@.Year AS char(4))
> +RIGHT('0' + CAST(@.Month AS varchar(2)), 2)
> +RIGHT('0' + CAST(@.Day AS varchar(2)), 2)
> AS datetime)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <aroraamit81@.gmail.com> wrote in message
> news:1132746277.481717.168850@.g49g2000cwa.googlegroups.com...
>|||Thanks a tonne, mate.|||> DECLARE @.y INT,@.m INT,@.d INT
> SET @.y=2005
> SET @.m=2
> SET @.d=27
> select cast(rtrim(@.y*10000+@.m*100+@.d) as datetime)
Nevermind how sick and twisted that is, but just for fun clarification,
that's not portable or future-proof, correct? It would seem to me that if
MS ever changes the underlying way that dates are stored, this would break.
Right?
--
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||Mike
> MS ever changes the underlying way that dates are stored, this would
> break.
What does make think so? As as I know ,dates are stored in the same way in
SQL Server 2005 too.

> that's not portable or future-proof, correct?
It is just mathematics, that's all
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:OvEFlUD8FHA.2576@.TK2MSFTNGP12.phx.gbl...
> Nevermind how sick and twisted that is, but just for fun clarification,
> that's not portable or future-proof, correct? It would seem to me that if
> MS ever changes the underlying way that dates are stored, this would
> break. Right?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||>> break.
> What does make think so? As as I know ,dates are stored in the same way in
> SQL Server 2005 too.
>
> It is just mathematics, that's all
I dunno, it just sounds dangerous. Sort of like the same way that C
programmers use bizarre pointer arithmetic to iterate over an array instead
of just iterating over the array like a normal human would.
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uK5YPUC8FHA.3880@.TK2MSFTNGP12.phx.gbl...
> DECLARE @.Year int
> DECLARE @.Month int
> DECLARE @.Day int
> SET @.Year = 2005
> SET @.Month = 02
> SET @.Day = 27
> SELECT
> CAST(
> CAST(@.Year AS char(4))
> +RIGHT('0' + CAST(@.Month AS varchar(2)), 2)
> +RIGHT('0' + CAST(@.Day AS varchar(2)), 2)
> AS datetime)
>
...or
select dateadd(d, @.day-1, dateadd(m, @.month-1, dateadd(yyyy, @.year % 1900,
'19000101')))|||C programmers are not normal humans.
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eUkQKgD8FHA.1140@.tk2msftngp13.phx.gbl...
> I dunno, it just sounds dangerous. Sort of like the same way that C
> programmers use bizarre pointer arithmetic to iterate over an array
> instead of just iterating over the array like a normal human would.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>

Composing a date from date parts

I have three date parts namely,
Year
Month
Date/Day
as integer values stored in one column each in a table in a SQL Server
2000 database. I need a function to serialize/compose/create a datetime
type out of them so I could use that in a query (pseudosyntax) as
below:
SELECT CreateDate(iYear, iMonth, iDay) As TheDateIWant FROM ThatTable
and then later on, I probably want to use an aggregation/computation on
that like, the MAX function, may be:
SELECT MAX(CreateDate(iYear, iMonth, iDay)) As TheDateIWant FROM
ThatTable
Thanks!
DECLARE @.Year int
DECLARE @.Month int
DECLARE @.Day int
SET @.Year = 2005
SET @.Month = 02
SET @.Day = 27
SELECT
CAST(
CAST(@.Year AS char(4))
+RIGHT('0' + CAST(@.Month AS varchar(2)), 2)
+RIGHT('0' + CAST(@.Day AS varchar(2)), 2)
AS datetime)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<aroraamit81@.gmail.com> wrote in message
news:1132746277.481717.168850@.g49g2000cwa.googlegr oups.com...
>I have three date parts namely,
> Year
> Month
> Date/Day
> as integer values stored in one column each in a table in a SQL Server
> 2000 database. I need a function to serialize/compose/create a datetime
> type out of them so I could use that in a query (pseudosyntax) as
> below:
>
> SELECT CreateDate(iYear, iMonth, iDay) As TheDateIWant FROM ThatTable
>
> and then later on, I probably want to use an aggregation/computation on
> that like, the MAX function, may be:
>
> SELECT MAX(CreateDate(iYear, iMonth, iDay)) As TheDateIWant FROM
> ThatTable
>
> Thanks!
>
|||Here is another solution. This will work only with 4 digit year.:
declare @.year int, @.month int, @.day int
set @.year = 2005
set @.month = 11
set @.day = 23
select cast(convert(char(8),@.year * 10000 + @.month * 100 + @.day) as datetime)
"aroraamit81@.gmail.com" wrote:

> I have three date parts namely,
> Year
> Month
> Date/Day
> as integer values stored in one column each in a table in a SQL Server
> 2000 database. I need a function to serialize/compose/create a datetime
> type out of them so I could use that in a query (pseudosyntax) as
> below:
>
> SELECT CreateDate(iYear, iMonth, iDay) As TheDateIWant FROM ThatTable
>
> and then later on, I probably want to use an aggregation/computation on
> that like, the MAX function, may be:
>
> SELECT MAX(CreateDate(iYear, iMonth, iDay)) As TheDateIWant FROM
> ThatTable
>
> Thanks!
>
|||Or for fun
DECLARE @.y INT,@.m INT,@.d INT
SET @.y=2005
SET @.m=2
SET @.d=27
select cast(rtrim(@.y*10000+@.m*100+@.d) as datetime)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uK5YPUC8FHA.3880@.TK2MSFTNGP12.phx.gbl...
> DECLARE @.Year int
> DECLARE @.Month int
> DECLARE @.Day int
> SET @.Year = 2005
> SET @.Month = 02
> SET @.Day = 27
> SELECT
> CAST(
> CAST(@.Year AS char(4))
> +RIGHT('0' + CAST(@.Month AS varchar(2)), 2)
> +RIGHT('0' + CAST(@.Day AS varchar(2)), 2)
> AS datetime)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <aroraamit81@.gmail.com> wrote in message
> news:1132746277.481717.168850@.g49g2000cwa.googlegr oups.com...
>
|||Thanks a tonne, mate.
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uK5YPUC8FHA.3880@.TK2MSFTNGP12.phx.gbl...
> DECLARE @.Year int
> DECLARE @.Month int
> DECLARE @.Day int
> SET @.Year = 2005
> SET @.Month = 02
> SET @.Day = 27
> SELECT
> CAST(
> CAST(@.Year AS char(4))
> +RIGHT('0' + CAST(@.Month AS varchar(2)), 2)
> +RIGHT('0' + CAST(@.Day AS varchar(2)), 2)
> AS datetime)
>
...or
select dateadd(d, @.day-1, dateadd(m, @.month-1, dateadd(yyyy, @.year % 1900,
'19000101')))
|||Very nice Uri Dimant
Madhivanan
|||On Wed, 23 Nov 2005 09:15:27 -0500, Raymond D'Anjou wrote:

>"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
>message news:uK5YPUC8FHA.3880@.TK2MSFTNGP12.phx.gbl...
>...or
>select dateadd(d, @.day-1, dateadd(m, @.month-1, dateadd(yyyy, @.year % 1900,
>'19000101')))
>
Oh boy.
We're all barely recovered from the shocks and horrors of Y2K, and now
you are already laying foundation for a huge Y3K8 problem.
:-)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:jg9co1p6aj6ho6e5ijar3nbi79dtmk06rl@.4ax.com...
> Oh boy.
> We're all barely recovered from the shocks and horrors of Y2K, and now
> you are already laying foundation for a huge Y3K8 problem.
> :-)
> Best, Hugo
People wrote code in the 80s without any thought of the year 2000.
At least my code is good for another 1795 years.
Hopefully, I won't be around to see the problems. :-)

Composing a date from date parts

I have three date parts namely,
Year
Month
Date/Day
as integer values stored in one column each in a table in a SQL Server
2000 database. I need a function to serialize/compose/create a datetime
type out of them so I could use that in a query (pseudosyntax) as
below:
SELECT CreateDate(iYear, iMonth, iDay) As TheDateIWant FROM ThatTable
and then later on, I probably want to use an aggregation/computation on
that like, the MAX function, may be:
SELECT MAX(CreateDate(iYear, iMonth, iDay)) As TheDateIWant FROM
ThatTable
Thanks!DECLARE @.Year int
DECLARE @.Month int
DECLARE @.Day int
SET @.Year = 2005
SET @.Month = 02
SET @.Day = 27
SELECT
CAST(
CAST(@.Year AS char(4))
+RIGHT('0' + CAST(@.Month AS varchar(2)), 2)
+RIGHT('0' + CAST(@.Day AS varchar(2)), 2)
AS datetime)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<aroraamit81@.gmail.com> wrote in message
news:1132746277.481717.168850@.g49g2000cwa.googlegroups.com...
>I have three date parts namely,
> Year
> Month
> Date/Day
> as integer values stored in one column each in a table in a SQL Server
> 2000 database. I need a function to serialize/compose/create a datetime
> type out of them so I could use that in a query (pseudosyntax) as
> below:
>
> SELECT CreateDate(iYear, iMonth, iDay) As TheDateIWant FROM ThatTable
>
> and then later on, I probably want to use an aggregation/computation on
> that like, the MAX function, may be:
>
> SELECT MAX(CreateDate(iYear, iMonth, iDay)) As TheDateIWant FROM
> ThatTable
>
> Thanks!
>|||Here is another solution. This will work only with 4 digit year.:
declare @.year int, @.month int, @.day int
set @.year = 2005
set @.month = 11
set @.day = 23
select cast(convert(char(8),@.year * 10000 + @.month * 100 + @.day) as datetime
)
"aroraamit81@.gmail.com" wrote:

> I have three date parts namely,
> Year
> Month
> Date/Day
> as integer values stored in one column each in a table in a SQL Server
> 2000 database. I need a function to serialize/compose/create a datetime
> type out of them so I could use that in a query (pseudosyntax) as
> below:
>
> SELECT CreateDate(iYear, iMonth, iDay) As TheDateIWant FROM ThatTable
>
> and then later on, I probably want to use an aggregation/computation on
> that like, the MAX function, may be:
>
> SELECT MAX(CreateDate(iYear, iMonth, iDay)) As TheDateIWant FROM
> ThatTable
>
> Thanks!
>|||Or for fun
DECLARE @.y INT,@.m INT,@.d INT
SET @.y=2005
SET @.m=2
SET @.d=27
select cast(rtrim(@.y*10000+@.m*100+@.d) as datetime)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uK5YPUC8FHA.3880@.TK2MSFTNGP12.phx.gbl...
> DECLARE @.Year int
> DECLARE @.Month int
> DECLARE @.Day int
> SET @.Year = 2005
> SET @.Month = 02
> SET @.Day = 27
> SELECT
> CAST(
> CAST(@.Year AS char(4))
> +RIGHT('0' + CAST(@.Month AS varchar(2)), 2)
> +RIGHT('0' + CAST(@.Day AS varchar(2)), 2)
> AS datetime)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <aroraamit81@.gmail.com> wrote in message
> news:1132746277.481717.168850@.g49g2000cwa.googlegroups.com...
>|||Thanks a tonne, mate.|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uK5YPUC8FHA.3880@.TK2MSFTNGP12.phx.gbl...
> DECLARE @.Year int
> DECLARE @.Month int
> DECLARE @.Day int
> SET @.Year = 2005
> SET @.Month = 02
> SET @.Day = 27
> SELECT
> CAST(
> CAST(@.Year AS char(4))
> +RIGHT('0' + CAST(@.Month AS varchar(2)), 2)
> +RIGHT('0' + CAST(@.Day AS varchar(2)), 2)
> AS datetime)
>
...or
select dateadd(d, @.day-1, dateadd(m, @.month-1, dateadd(yyyy, @.year % 1900,
'19000101')))|||Very nice Uri Dimant
Madhivanan|||On Wed, 23 Nov 2005 09:15:27 -0500, Raymond D'Anjou wrote:

>"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
>message news:uK5YPUC8FHA.3880@.TK2MSFTNGP12.phx.gbl...
>...or
>select dateadd(d, @.day-1, dateadd(m, @.month-1, dateadd(yyyy, @.year % 1900,
>'19000101')))
>
Oh boy.
We're all barely recovered from the shocks and horrors of Y2K, and now
you are already laying foundation for a huge Y3K8 problem.
:-)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:jg9co1p6aj6ho6e5ijar3nbi79dtmk06rl@.
4ax.com...
> Oh boy.
> We're all barely recovered from the shocks and horrors of Y2K, and now
> you are already laying foundation for a huge Y3K8 problem.
> :-)
> Best, Hugo
People wrote code in the 80s without any thought of the year 2000.
At least my code is good for another 1795 years.
Hopefully, I won't be around to see the problems. :-)

Composing a date from date parts

I have three date parts namely,
Year
Month
Date/Day
as integer values stored in one column each in a table in a SQL Server
2000 database. I need a function to serialize/compose/create a datetime
type out of them so I could use that in a query (pseudosyntax) as
below:
SELECT CreateDate(iYear, iMonth, iDay) As TheDateIWant FROM ThatTable
and then later on, I probably want to use an aggregation/computation on
that like, the MAX function, may be:
SELECT MAX(CreateDate(iYear, iMonth, iDay)) As TheDateIWant FROM
ThatTable
Thanks!DECLARE @.Year int
DECLARE @.Month int
DECLARE @.Day int
SET @.Year = 2005
SET @.Month = 02
SET @.Day = 27
SELECT
CAST(
CAST(@.Year AS char(4))
+RIGHT('0' + CAST(@.Month AS varchar(2)), 2)
+RIGHT('0' + CAST(@.Day AS varchar(2)), 2)
AS datetime)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<aroraamit81@.gmail.com> wrote in message
news:1132746277.481717.168850@.g49g2000cwa.googlegroups.com...
>I have three date parts namely,
> Year
> Month
> Date/Day
> as integer values stored in one column each in a table in a SQL Server
> 2000 database. I need a function to serialize/compose/create a datetime
> type out of them so I could use that in a query (pseudosyntax) as
> below:
>
> SELECT CreateDate(iYear, iMonth, iDay) As TheDateIWant FROM ThatTable
>
> and then later on, I probably want to use an aggregation/computation on
> that like, the MAX function, may be:
>
> SELECT MAX(CreateDate(iYear, iMonth, iDay)) As TheDateIWant FROM
> ThatTable
>
> Thanks!
>|||Here is another solution. This will work only with 4 digit year.:
declare @.year int, @.month int, @.day int
set @.year = 2005
set @.month = 11
set @.day = 23
select cast(convert(char(8),@.year * 10000 + @.month * 100 + @.day) as datetime)
"aroraamit81@.gmail.com" wrote:
> I have three date parts namely,
> Year
> Month
> Date/Day
> as integer values stored in one column each in a table in a SQL Server
> 2000 database. I need a function to serialize/compose/create a datetime
> type out of them so I could use that in a query (pseudosyntax) as
> below:
>
> SELECT CreateDate(iYear, iMonth, iDay) As TheDateIWant FROM ThatTable
>
> and then later on, I probably want to use an aggregation/computation on
> that like, the MAX function, may be:
>
> SELECT MAX(CreateDate(iYear, iMonth, iDay)) As TheDateIWant FROM
> ThatTable
>
> Thanks!
>|||Or for fun
DECLARE @.y INT,@.m INT,@.d INT
SET @.y=2005
SET @.m=2
SET @.d=27
select cast(rtrim(@.y*10000+@.m*100+@.d) as datetime)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uK5YPUC8FHA.3880@.TK2MSFTNGP12.phx.gbl...
> DECLARE @.Year int
> DECLARE @.Month int
> DECLARE @.Day int
> SET @.Year = 2005
> SET @.Month = 02
> SET @.Day = 27
> SELECT
> CAST(
> CAST(@.Year AS char(4))
> +RIGHT('0' + CAST(@.Month AS varchar(2)), 2)
> +RIGHT('0' + CAST(@.Day AS varchar(2)), 2)
> AS datetime)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <aroraamit81@.gmail.com> wrote in message
> news:1132746277.481717.168850@.g49g2000cwa.googlegroups.com...
>>I have three date parts namely,
>> Year
>> Month
>> Date/Day
>> as integer values stored in one column each in a table in a SQL Server
>> 2000 database. I need a function to serialize/compose/create a datetime
>> type out of them so I could use that in a query (pseudosyntax) as
>> below:
>>
>> SELECT CreateDate(iYear, iMonth, iDay) As TheDateIWant FROM ThatTable
>>
>> and then later on, I probably want to use an aggregation/computation on
>> that like, the MAX function, may be:
>>
>> SELECT MAX(CreateDate(iYear, iMonth, iDay)) As TheDateIWant FROM
>> ThatTable
>>
>> Thanks!
>|||Thanks a tonne, mate.|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uK5YPUC8FHA.3880@.TK2MSFTNGP12.phx.gbl...
> DECLARE @.Year int
> DECLARE @.Month int
> DECLARE @.Day int
> SET @.Year = 2005
> SET @.Month = 02
> SET @.Day = 27
> SELECT
> CAST(
> CAST(@.Year AS char(4))
> +RIGHT('0' + CAST(@.Month AS varchar(2)), 2)
> +RIGHT('0' + CAST(@.Day AS varchar(2)), 2)
> AS datetime)
>
...or
select dateadd(d, @.day-1, dateadd(m, @.month-1, dateadd(yyyy, @.year % 1900,
'19000101')))|||Very nice Uri Dimant
Madhivanan|||On Wed, 23 Nov 2005 09:15:27 -0500, Raymond D'Anjou wrote:
>"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
>message news:uK5YPUC8FHA.3880@.TK2MSFTNGP12.phx.gbl...
>> DECLARE @.Year int
>> DECLARE @.Month int
>> DECLARE @.Day int
>> SET @.Year = 2005
>> SET @.Month = 02
>> SET @.Day = 27
>> SELECT
>> CAST(
>> CAST(@.Year AS char(4))
>> +RIGHT('0' + CAST(@.Month AS varchar(2)), 2)
>> +RIGHT('0' + CAST(@.Day AS varchar(2)), 2)
>> AS datetime)
>...or
>select dateadd(d, @.day-1, dateadd(m, @.month-1, dateadd(yyyy, @.year % 1900,
>'19000101')))
>
Oh boy.
We're all barely recovered from the shocks and horrors of Y2K, and now
you are already laying foundation for a huge Y3K8 problem.
:-)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:jg9co1p6aj6ho6e5ijar3nbi79dtmk06rl@.4ax.com...
>>select dateadd(d, @.day-1, dateadd(m, @.month-1, dateadd(yyyy, @.year % 1900,
>>'19000101')))
> Oh boy.
> We're all barely recovered from the shocks and horrors of Y2K, and now
> you are already laying foundation for a huge Y3K8 problem.
> :-)
> Best, Hugo
People wrote code in the 80s without any thought of the year 2000.
At least my code is good for another 1795 years.
Hopefully, I won't be around to see the problems. :-)

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 use of apostrophes

Hello,

could someone help with this query in a stored proc.?

SET @.SQL='SET '''+ @.avgwgt+''' = '

'(SELECT AVG(AverageWeight)

FROM CageFishHistory where CageID IN ('

+ @.cagearray+')

and ItemDate ='''

+CONVERT(varchar(23),@.startdate)+''')'

EXEC @.SQL

I'm trying to get an average value across dynamically selected rows. (I'm using a list array to deliver the selection to the stored proc). I need to re-use the average value within the procedure,so it's not enough to output it as a column of the resultset - EG. 'Select AVG(AverageWeight) as AvgWgt' . If I take out the @.avgwgt line it works fine, but otherwise I'm getting this error:

"Incorrect syntax near '(SELECT AVG(AverageWeight)

FROM CageFishHistory where CageID IN ('."

It may be that I can access a column of the resultset in the rest of the procedure, and that would help avoid the use of pesky apostrophes, but I don't know how to do it.

please, try like this.

SELECT @.avgwgt=AVG(AverageWeight) FROM CageFishHistory where CageID IN ('+ @.cagearray+') and ItemDate ='''+CONVERT(varchar(23),@.startdate)+''')'

Regards,

Omer Kamal

www.friendspoint.de

|||

I tried this Omer,and got

Conversion failed when converting the varchar value ' + @.cagearray + ' to data type int.

|||

Seems to be working now with the Split() Function I found - I'm posting in case it will help others...

SELECT

@.avgwgt=AVG(AverageWeight)FROM CageFishHistorywhere CageIDIN(SELECT ItemFROM dbo.Split(@.cagearray,','))and ItemDate=''+CONVERT(varchar(23),@.startdate)

====================================================================

set

ANSI_NULLSON

set

QUOTED_IDENTIFIERON

go

CREATE

FUNCTION [dbo].[Split]

(

@.ItemList

NVARCHAR(4000),

@.delimiter

CHAR(1)

)

RETURNS

@.IDTableTABLE(ItemVARCHAR(50))

AS

BEGIN

DECLARE @.tempItemListNVARCHAR(4000)SET @.tempItemList= @.ItemListDECLARE @.iINTDECLARE @.ItemNVARCHAR(4000)SET @.tempItemList=REPLACE(@.tempItemList,' ','')SET @.i=CHARINDEX(@.delimiter, @.tempItemList)WHILE(LEN(@.tempItemList)> 0)BEGINIF @.i= 0SET @.Item= @.tempItemListELSESET @.Item=LEFT(@.tempItemList, @.i- 1)INSERTINTO @.IDTable(Item)VALUES(@.Item)IF @.i= 0SET @.tempItemList=''ELSESET @.tempItemList=RIGHT(@.tempItemList,LEN(@.tempItemList)- @.i)SET @.i=CHARINDEX(@.delimiter, @.tempItemList)ENDRETURN

END

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 SELECT QUERY using look-up tables

Hi gang,

I'm trying to find the best way to get a SELECT query to return field values for a table that are stored in another lookup table. Here's a basic example that will illustrate what I'm trying to do.

Assume three tables: tblItem, tblCustomFieldNames, tblCustomFieldValues.

The schemas/columns for the tables are as follows:

tblItem
id
itemName

tblCustomFieldName
id
customFieldName

tblCustomFieldValue
id
customFieldValue
customFieldID
itemID references tblItem(id)

Further assume, that the tables contain the following data:

tblItem
id|itemName
(1,CPU)
(2,Motherboard)

tblCustomFieldName
id|customFieldName
(1,Manufacturer)
(2,Price)
(3,Qty)

tblCustomFieldValue
id|customFieldValue|customFieldID|itemID
(1,AMD,1,1)
(2,$99,2,1)
(3,2,3,1)
(4,ASUS,1,2)
(5,$79,2,2)
(6,1,3,2)

My question is what does my SQL "SELECT query" syntax need to be such that I am able to return a result with the following form:

tblItem.id|Manufacturer|Price|Qty
(1, Intel, $99, 2)
(2, ASUS, $79, 1)

Note: It's not an option for me to re-design the database schema, as it's someone else's database. I simply need to be able to obtain the above resultset using a single SELECT query.

Thanks in advance! :)
-EHere you are:SELECT id, MAX(manufacturer) manufacturer, MAX(price) price, MAX(qty) qty
FROM
(
SELECT i.id,
CASE n.id
WHEN 1 THEN v.customfieldvalue
ELSE NULL
END manufacturer ,
CASE n.id
WHEN 2 THEN v.customfieldvalue
ELSE NULL
END price,
CASE n.id
WHEN 3 THEN v.customfieldvalue
ELSE NULL
END qty
FROM TBLITEM i, TBLCUSTOMFIELDNAME n, TBLCUSTOMFIELDVALUE v
WHERE v.customfieldid = n.id
AND i.id = v.itemid
)
GROUP BY id;

complex query help needed

Hello all,

I'm stuck with this one:

Step 1
I have a stored proc like this

SELECT this, that, another, ToDo0, ..., ToDo8 FROM table1 LEFT OUTER JOIN view1 on table1.id = view1.id
WHERE (bunch of criteria)

view one basically returns ToDo0 ... ToDo8

Everything works fine.

Step 2

As I have different ToDo's depending on who is logged on, there are view2 ... view6 returning the ToDo's accordingly. So I have

If @.grp = 1
SELECT this, that, another, ToDo0, ..., ToDo8 FROM table1 LEFT OUTER JOIN view1 on table1.id = view1.id
WHERE (bunch of criteria)
else if @.grp = 2
SELECT this, that, another, ToDo0, ..., ToDo8 FROM table1 LEFT OUTER JOIN view2 on table1.id = view2.id
WHERE (same bunch of criteria)
else ... (you get the point :)

works fine, though a little slow.

Step 3

To keep things maintainable (I'm not the only one working on that) and somewhat modular, I'd like to have something like

SELECT this, that, another, ToDo0, ..., ToDo8 FROM table1 LEFT OUTER JOIN just-take-the-right-view-please as Yep on table1.id = Yep.id
WHERE (bunch of criteria)

No Go ...

I tried:

1.
Create #ttbl_ToDo (...)
if @.grp = 1
Insert #ttbl_ToDo SELECT * from view1
else ...

and then joining on the #ttbl

I got timeouts (view1 ... 6 are quite expensive).

2.
Built a stored proc that already returns ToDo1 .. 8 for the right group but then I can't access the resultset from the calling sp.

3.
Try to build dynamic SQL with EXEC (expected timeouts there, too) - the SQL string exceeds maximum length (as things are a little more complex in reality)

I'm using MSSQL 7 (no option to migrate to 2000 an use functions yet :( )

Some more explanation why I'm not happy with Step2 (which at least is working):
1. the where clause is kind of complex an needs to be adopted from time to time. It's just a pain to do this 6 times.
2. Other developers should be able to add ToDo-groups without changing the query itself. Changing the part with the temp table wouldn't be perfect but acceptable, but changing the whole thing is not what we want.

Any hints are appreciated.

TIA, ChrisTo simplify you could use dynamic SQL, which as you stated cause timeouts, so I don't know if this will help.

DECLARE @.view varchar(35)

SELECT @.view = CASE
WHEN @.grp=1 THEN "view1"
WHEN @.grp=2 THEN "view2"
WHEN @.grp=3 THEN "view3"
WHEN @.grp=4 THEN "view4"
WHEN @.grp=5 THEN "view5"
ELSE "view6"
END

EXEC ("SELECT ... FROM... " + @.view + " WHERE...")

If your views only differed by a WHERE clause like this "@.grp" value then you could combine the views into one view and leave off the WHERE criteria until you use it.

CREATE VIEW view1 AS
SELECT .....
WHERE grp = 1

CREATE VIEW view2 AS
SELECT .....
WHERE grp = 2

etc.

Change to

CREATE VIEW view AS
SELECT .....

Then
SELECT this, that, another, ToDo0, ..., ToDo8 FROM table1 LEFT OUTER JOIN view on table1.id = view.id
WHERE (bunch of criteria)
AND view.grp = @.grp <-- Add here

This would not require dynamic SQL.

Also you can execute a stored procedure and have it's result go into a table.

INSERT table EXEC myProc

Note:
You have to watch out with views they are not a performance saver. If the view is a 6 table JOIN then when you execute it, it is a 6 table JOIN.

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

Completed(?) and urgent Triggers and Transactions question

I use the VB.NET transaction to update an sql server 2000 database. I call a number of stored procedures within this transaction.

The stored procedures will update tables. These tables use triggers..

My question is, when does the trigger get called? Is it after the each stored procedure, or is it after the whole transaction?

Jag

the trigger will execute when

- new records is being inserted into the table

- records get update

- records get deleted

from the table

|||

In my opinion triggers fire as soon as the database updated, and transaction update your table as soon as a query fires. Having triggers do too much is a typical mistake. Triggers should be
left to handle only simple tasks.

cheers

|||

How would you define too much?

At the moment, one of my trigger calls a stored procedure that is doing about 4 selects on a table (one with an MAX()), and maybe a 2 updates.

The stored procedure should be optimised, so it should case too much problems later (?)

EDIT: I am expecting the number of selects to increase to about 10, and the number of updates to 3. The selects will be small selects that mostly pull out ids.

Jag

|||

Hi,

The trigger will be fired immediately after the the update was executed. And the transaction will continue after trigger gets executed.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

What I was afraid of was that the triggers would not get fired during the transaction, and would be fired after the commit. But after a little testing, I found this was not true.

I am still trying to find out how much I can put into a trigger before a performance becomes an issue. I am not expecting heavy usage - I'm not even expecting moderate usage on the site. Maybe about 10 inputs/edits a day, that's it.

I have written a stored procedure and the trigger calls this if needed. The stored procedure will have upto 20 select statements to pull out values from the database, and then a couple of updates. Is this too much? I don't see it being too much as I am not expecting too much input to be going on.

Jag

|||

Just another quick question.

Same example - there is a transaction with a number of updates/inserts. The tables these apply to have triggers on then. The triggers are fired as soon as the update/insert happens, and not at the end of the transaction.

My question is, if the transaction fails and the changes are reversed, will the changes made by the triggers also be reversed. Common sense says that it should happen, but I need to ensure that it does.

Thanks in advance

Jag

Compiling SQL statement in stored proc

(I'm sure this is possible, so maybe someone can just mention the topic that covers it and I'll research it from there.)

In essence, I have two SQL statements that are 98% the same, but I want to substitute a few different clauses according to the variable passed in.

I can get the following simple example to work fine, as long as the CASE statement adds a simple string.
But syntax errors appear when trying to use more complex SQL statements, such as FROM or JOIN clauses, in the CASE statement. I think it doesn't like keywords next to 'WHEN'.

If it's possible to compile the SQL statement as one long string while substituting the conditional clauses, that would work too. That's what I already do in VBScript on the ASP page querying the DB, but I'd like to remove the 1.5-page-long SQL statement from the code to the database, and just pass in one of two options.

The commented-out lines represent my failed attempt. Clues?

CREATE PROCEDURE dbo.tstsp
@.usr char(1)

AS

SET NOCOUNT ON
BEGIN

--DECLARE @.strSQL varchar(3000)
--SET @.strSQL = 'SELECT * FROM dbo.sur_followup WHERE '

SELECT * FROM dbo.sur_followup WHERE USERNAME =

CASE @.usr
-- WHEN 'a' THEN SET @.strSQL = @.strSQL + 'USERNAME = '"ASnow" '
-- WHEN 'b' THEN SET @.strSQL = @.strSQL + ''response_id = 214'
WHEN 'a' THEN 'ASnow'
WHEN 'b' THEN 'JUser'
END

--EXEC @.strSQL

SET NOCOUNT OFF
END
(This example just shows the idea...my query is actually much more complex.)
Thanks!

Hi,

I'm not sure I fully understand your problem, but I have a few initial responses

1) As Umachandar recently explained in the post on "Paging large result sets", you should be using sp_execsql so that you have an opportunity for plan caching.

2) If you're attempting to use "more complex SQL statements, such as FROM or JOIN clauses, in the CASE statement" then they need to evaluate to scalar expressions. You must enclose subqueries in () when the result is needed in a scalar expression context. Here is a silly, simplified example that illustrates the point:

DECLARE @.id int
SELECT @.id = 2
SELECT * FROM sysobjects
WHERE name = CASE @.id
WHEN 1 THEN (SELECT name FROM sysobjects WHERE id=@.id)
WHEN 2 THEN (SELECT name FROM sysobjects WHERE id=@.id)
-- etc
ELSE '?'
END

Does this answer your question?!

Regards,
Clifford Dibble|||

Thanks for the reply, Clifford.

Perhaps a snippet of VBScript code will explain better. This is what I want to do in the stored proc:

[...lots of SQL before this...]
If strRptType = "naddr" Then strSQL = strSQL & "FROM SUR_RESPONSE "

If strRptType = "yaddr" Then
strSQL = strSQL & "FROM SUR_FOLLOWUP "
strSQL = strSQL & "LEFT JOIN SUR_RESPONSE "
End If

strSQL = strSQL & "LEFT JOIN SUR_RESPONSE_ANSWER ON SUR_RESPONSE.RESPONSE_ID = SUR_RESPONSE_ANSWER.RESPONSE_ID "
strSQL = strSQL & "LEFT JOIN SUR_ITEM ON SUR_RESPONSE_ANSWER.ITEM_ID = SUR_ITEM.ITEM_ID "

If strRptType = "yaddr" Then strSQL = strSQL & "ON SUR_FOLLOWUP.response_id = SUR_RESPONSE.RESPONSE_ID "

strSQL = strSQL & "WHERE "

[...lots of SQL after this...]
I think what I want to do is called 'dynamic SQL.' I basically would need to compile the SQL string into a variable, but substitute certain clauses according to the parameter passed into the stored proc. I tried using CASE/WHEN instead of IF/THEN but I guess I don't know the proper syntax for all this.

|||Since you're stuck on doing dynamic SQL, just do the string manipulation in your language of choice (since it will undoubtedly handle strings better/easier) and pass the whole thing on to SQL server as the text of a command object and lose the stored procedure since it's not gaining you anything anyway (be sure to parse the strings of the where clause to avoid SQL injection attacks).

When I'm writing stored procedures with multiple where clauses driven by parameters, I take the laborious route of actually writing the whole stored proc with loads of if statements. E.g.

create procedure db.SomeTableGet(
@.p_lVar1 integer = null,
@.p_lVar2 integer = null,
@.p_lVar3 integer = null)

if @.p_lVar1 is null

if @.p_lVar2 is null

if @.p_lVar3 is null

select
t.Col1,
t.Col2,
t.Col3
from
Table t

else

select
t.Col1,
t.Col2,
t.Col3
from
Table t
where
t.Col3 = @.p_lVar3

else

if @.p_lVar3 is null

select
t.Col1,
t.Col2,
t.Col3
from
Table t
where
t.Col2 = @.p_lVar2

else

select
t.Col1,
t.Col2,
t.Col3
from
Table t
where
t.Col1 = @.p_lVar1
and t.Col2 = @.p_lVar2

else

if @.p_lVar2 is null

if @.p_lVar3 is null

select
t.Col1,
t.Col2,
t.Col3
from
Table t
where
t.Col1 = @.p_lVar1

else

select
t.Col1,
t.Col2,
t.Col3
from
Table t
where
t.Col3 = @.p_lVar3
and t.Col1 = @.p_lVar1

else

if @.p_lVar3 is null

select
t.Col1,
t.Col2,
t.Col3
from
Table t
where
t.Col2 = @.p_lVar2
and t.Col1 = @.p_lVar1

else

select
t.Col1,
t.Col2,
t.Col3
from
Table t
where
t.Col1 = @.p_lVar1
and t.Col2 = @.p_lVar2
and t.Col1 = @.p_lVar1

As you can see, the sp's get long in a hurry, but you can copy and paste most of it, and if you're really enterprising, you can either write a tool to do most of it, or use CodeSmith scripts to do it for you. Also, this kind of thing messes with SQL Server's parameter guessing logic "stuff", but oh well. I wonder if anyone has any ideas on a better way to do it while keeping it in a stored procedure and avoiding exec'ing SQL strings? The main purpose here is to keep the sorting and filtering on the SQL server instead of pushing it off into the business tier that isn't very good at filtering or sorting and doesn't have the advantage of SQL Server's table indexes (and to keep from transferring larger-than-necessary recordsets from the SQL box to the business tier box).|||

Hi Robert
I always write my SPs with multiple where clauses driven by parameters as follows

create procedure dbo.SomeTableGet
@.p_lVar1 integer = 0, -- zeros not nulls cos null <> null
@.p_lVar2 integer = 0,
@.p_lVar3 integer = 0
as
select t.Col1,t.Col2,t.Col3
from Table t
where (case when @.p_lVar1 = 0 then 0 else t.Col1 end )= @.p_lVar1
And (case when @.p_lVar2 = 0 then 0 else t.Col2 end )= @.p_lVar2
And (case when @.p_lVar3 = 0 then 0 else t.Col3 end )= @.p_lVar3

|||

Hi DN,

I think I finally understand your issue. The key point for me was "tried using CASE/WHEN instead of IF/THEN"

Here is the syntax for the CASE expression.

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

Notice that (a) this is an expression, so it must produce a value and (b) the same holds for result_expression.

But, SET is not an expression. To see this, try this in a query window:

DECLARE @.x char(1)
SET @.x = 'a'

and notice that no value is produced.

Therefore, this is an illegal expression and so you get a syntax error

SELECT
CASE @.x
WHEN 'a' THEN SET @.y = '1'
WHEN 'b' THEN SET @.y = '2'
END
Server: Msg 156, Level 15, State 1, Line 7

Does this answer your question?!

Regards,
Clifford Dibble

compile SP after creation?

Hello
Is it possible to force SQL Server to compile stored procedures after
creation? I mean, when I execute something like:
CREATE PROC SP1 AS
select * from X
where table X does not exist, I want to get error or warning. Now the proc
is created and the error is thrown only when it is executed. We have more
then 1500 stored procedures in the database now scripted out into sql
scripts. We want to ensure that scripts (and all objects like SPs, UDFs,..)
are valid after developers updates but by simply running the script to creat
e
DB and all its content does not produce any warning/error. How can we
accomplish that?
Thanks
eXavierHi
i can think about adding IF OBJECT_ID('Table') IS NULL which means if the
NULL then object does not exist and your error will be thrown
"eXavier" <eXavier@.community.nospam> wrote in message
news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation? I mean, when I execute something like:
> CREATE PROC SP1 AS
> select * from X
> where table X does not exist, I want to get error or warning. Now the proc
> is created and the error is thrown only when it is executed. We have more
> then 1500 stored procedures in the database now scripted out into sql
> scripts. We want to ensure that scripts (and all objects like SPs,
> UDFs,..)
> are valid after developers updates but by simply running the script to
> create
> DB and all its content does not produce any warning/error. How can we
> accomplish that?
> Thanks
> eXavier|||Hi,
I'm not sure if I understand what you mean. Do you mean to write some sql
script parser, extract all table names from all stored procedures and then
call your IF check? This is not acceptable for us. In fact we want the serve
r
to compile procedures to get eventual errors.. SP is compiled when executed
first but we cannot automatically execute them all as they have different
parameters..
Any other idea?
Thanks
eXavier
"Uri Dimant" wrote:

> Hi
> i can think about adding IF OBJECT_ID('Table') IS NULL which means if the
> NULL then object does not exist and your error will be thrown
>
>
> "eXavier" <eXavier@.community.nospam> wrote in message
> news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
>
>|||Specify WITH RECOMPILE in your stored procedure. The procedure will not be
cached and will recompile at runtime.
--
MG
"eXavier" wrote:

> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation? I mean, when I execute something like:
> CREATE PROC SP1 AS
> select * from X
> where table X does not exist, I want to get error or warning. Now the proc
> is created and the error is thrown only when it is executed. We have more
> then 1500 stored procedures in the database now scripted out into sql
> scripts. We want to ensure that scripts (and all objects like SPs, UDFs,..
)
> are valid after developers updates but by simply running the script to cre
ate
> DB and all its content does not produce any warning/error. How can we
> accomplish that?
> Thanks
> eXavier|||Hi,
WITH RECOMPILE does exactly what you wrote. But it does not help in creation
time - you can still create procedure referencing non-existant tables.
The error is thrown only when the proc is executed, coming back to our
problem. (Further, if the SP compiles we want it to be cached.)
"MGeles" wrote:
[vbcol=seagreen]
> Specify WITH RECOMPILE in your stored procedure. The procedure will not b
e
> cached and will recompile at runtime.
> --
> MG
>
> "eXavier" wrote:
>|||I'm afraid you cannot do that
"eXavier" <eXavier@.community.nospam> wrote in message
news:4BBE6A41-D987-4A5C-A953-1BD71A01A972@.microsoft.com...[vbcol=seagreen]
> Hi,
> I'm not sure if I understand what you mean. Do you mean to write some sql
> script parser, extract all table names from all stored procedures and then
> call your IF check? This is not acceptable for us. In fact we want the
> server
> to compile procedures to get eventual errors.. SP is compiled when
> executed
> first but we cannot automatically execute them all as they have different
> parameters..
> Any other idea?
> Thanks
> eXavier
> "Uri Dimant" wrote:
>|||"eXavier" <eXavier@.community.nospam> wrote in message
news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation?
Wayyy back in the day (SQL 6.5 I believe) this was possible, since SQL
checked for the existence of objects before creating stored procedures.
Apparently this caused a bunch of headaches, especially with database object
scripts that weren't created in the proper order, so "deferred name
resolution" was introduced. Table names are not resolved until run-time
instead of at creation time. Your best bet might be to 1) Do what Uri
suggested and explicitly check for the existence of tables yourself before
running the CREATE SP statement, or 2) Do what MS suggests below and test
your code after creation. Uri's suggestion should not be all that
difficult. Maybe a single script that tests for the existence of *all*
tables that are supposed to be in the database. You could run this script
before you try to run any SP creation scripts, and abort the mission based
on the result.
Here's what MS has to say on deferred name resolution:
"Deferred Name Resolution. Deferred name resolution allows procedure
compilation without all table references being present. Deferred name
resolution works in much the same way as the object-oriented concept of late
binding. At compile time, the compiler attempts to resolve all table names
that the procedure references. But if a table does not yet exist, the
compiler defers this name resolution until execution time.
For developers who have used temporary tables within their stored procedures
or triggers, this subtle new feature is long overdue. Although this feature
is useful, it has a side effect that many developers might initially
miss-the compiler no longer reliably catches table-name typos. Yes, you now
must test your code. This statement might sound funny at first, but if you
are not aware of deferred name resolution, you might find yourself wondering
why the compiler missed this error."
(From
http://www.microsoft.com/technet/pr...loy/migrat.mspx)|||Hi eXavier,
xyz's suggestion is reasonable. Appreciate your understanding that this
requirement is individual and actually limited by the design of SQL Server.
It is impossible for us to change the native behavior. I noticed that you
just wanted to ensure that scripts are valid after developers updates but
by simply running the script to create DB and all its content does not
produce any warning/error. You may consider to find a way from management.
For example, setup a test database environment which is same as the
development database then script a file to execute all the SPs or UDFs
that you want to check. This may require a standard process that the
developer of a SP or a UDF should provide a SQL test statement which can be
directly copied into the script file for checking at runtime. If some
errors are thrown out, please check and correct both the test database and
the development database. It is important to keep the identical environment
between the test database and the development database.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||You can catch most issues by executing the proc with SET FMTONLY ON like the
example below (passing any needed parameters as NULL values). However, some
errors can only be found by actually executing the proc.
SET FMTONLY ON
GO
EXEC dbo.SP1
GO
SET FMTONLY OFF
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"eXavier" <eXavier@.community.nospam> wrote in message
news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation? I mean, when I execute something like:
> CREATE PROC SP1 AS
> select * from X
> where table X does not exist, I want to get error or warning. Now the proc
> is created and the error is thrown only when it is executed. We have more
> then 1500 stored procedures in the database now scripted out into sql
> scripts. We want to ensure that scripts (and all objects like SPs,
> UDFs,..)
> are valid after developers updates but by simply running the script to
> create
> DB and all its content does not produce any warning/error. How can we
> accomplish that?
> Thanks
> eXavier

compile SP after creation?

Hello
Is it possible to force SQL Server to compile stored procedures after
creation? I mean, when I execute something like:
CREATE PROC SP1 AS
select * from X
where table X does not exist, I want to get error or warning. Now the proc
is created and the error is thrown only when it is executed. We have more
then 1500 stored procedures in the database now scripted out into sql
scripts. We want to ensure that scripts (and all objects like SPs, UDFs,..)
are valid after developers updates but by simply running the script to create
DB and all its content does not produce any warning/error. How can we
accomplish that?
Thanks
eXavierHi
i can think about adding IF OBJECT_ID('Table') IS NULL which means if the
NULL then object does not exist and your error will be thrown
"eXavier" <eXavier@.community.nospam> wrote in message
news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation? I mean, when I execute something like:
> CREATE PROC SP1 AS
> select * from X
> where table X does not exist, I want to get error or warning. Now the proc
> is created and the error is thrown only when it is executed. We have more
> then 1500 stored procedures in the database now scripted out into sql
> scripts. We want to ensure that scripts (and all objects like SPs,
> UDFs,..)
> are valid after developers updates but by simply running the script to
> create
> DB and all its content does not produce any warning/error. How can we
> accomplish that?
> Thanks
> eXavier|||Hi,
I'm not sure if I understand what you mean. Do you mean to write some sql
script parser, extract all table names from all stored procedures and then
call your IF check? This is not acceptable for us. In fact we want the server
to compile procedures to get eventual errors.. SP is compiled when executed
first but we cannot automatically execute them all as they have different
parameters..
Any other idea?
Thanks
eXavier
"Uri Dimant" wrote:
> Hi
> i can think about adding IF OBJECT_ID('Table') IS NULL which means if the
> NULL then object does not exist and your error will be thrown
>
>
> "eXavier" <eXavier@.community.nospam> wrote in message
> news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> > Hello
> > Is it possible to force SQL Server to compile stored procedures after
> > creation? I mean, when I execute something like:
> >
> > CREATE PROC SP1 AS
> > select * from X
> >
> > where table X does not exist, I want to get error or warning. Now the proc
> > is created and the error is thrown only when it is executed. We have more
> > then 1500 stored procedures in the database now scripted out into sql
> > scripts. We want to ensure that scripts (and all objects like SPs,
> > UDFs,..)
> > are valid after developers updates but by simply running the script to
> > create
> > DB and all its content does not produce any warning/error. How can we
> > accomplish that?
> >
> > Thanks
> > eXavier
>
>|||Specify WITH RECOMPILE in your stored procedure. The procedure will not be
cached and will recompile at runtime.
--
MG
"eXavier" wrote:
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation? I mean, when I execute something like:
> CREATE PROC SP1 AS
> select * from X
> where table X does not exist, I want to get error or warning. Now the proc
> is created and the error is thrown only when it is executed. We have more
> then 1500 stored procedures in the database now scripted out into sql
> scripts. We want to ensure that scripts (and all objects like SPs, UDFs,..)
> are valid after developers updates but by simply running the script to create
> DB and all its content does not produce any warning/error. How can we
> accomplish that?
> Thanks
> eXavier|||Hi,
WITH RECOMPILE does exactly what you wrote. But it does not help in creation
time - you can still create procedure referencing non-existant tables.
The error is thrown only when the proc is executed, coming back to our
problem. (Further, if the SP compiles we want it to be cached.)
"MGeles" wrote:
> Specify WITH RECOMPILE in your stored procedure. The procedure will not be
> cached and will recompile at runtime.
> --
> MG
>
> "eXavier" wrote:
> > Hello
> > Is it possible to force SQL Server to compile stored procedures after
> > creation? I mean, when I execute something like:
> >
> > CREATE PROC SP1 AS
> > select * from X
> >
> > where table X does not exist, I want to get error or warning. Now the proc
> > is created and the error is thrown only when it is executed. We have more
> > then 1500 stored procedures in the database now scripted out into sql
> > scripts. We want to ensure that scripts (and all objects like SPs, UDFs,..)
> > are valid after developers updates but by simply running the script to create
> > DB and all its content does not produce any warning/error. How can we
> > accomplish that?
> >
> > Thanks
> > eXavier|||I'm afraid you cannot do that
"eXavier" <eXavier@.community.nospam> wrote in message
news:4BBE6A41-D987-4A5C-A953-1BD71A01A972@.microsoft.com...
> Hi,
> I'm not sure if I understand what you mean. Do you mean to write some sql
> script parser, extract all table names from all stored procedures and then
> call your IF check? This is not acceptable for us. In fact we want the
> server
> to compile procedures to get eventual errors.. SP is compiled when
> executed
> first but we cannot automatically execute them all as they have different
> parameters..
> Any other idea?
> Thanks
> eXavier
> "Uri Dimant" wrote:
>> Hi
>> i can think about adding IF OBJECT_ID('Table') IS NULL which means if
>> the
>> NULL then object does not exist and your error will be thrown
>>
>>
>> "eXavier" <eXavier@.community.nospam> wrote in message
>> news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
>> > Hello
>> > Is it possible to force SQL Server to compile stored procedures after
>> > creation? I mean, when I execute something like:
>> >
>> > CREATE PROC SP1 AS
>> > select * from X
>> >
>> > where table X does not exist, I want to get error or warning. Now the
>> > proc
>> > is created and the error is thrown only when it is executed. We have
>> > more
>> > then 1500 stored procedures in the database now scripted out into sql
>> > scripts. We want to ensure that scripts (and all objects like SPs,
>> > UDFs,..)
>> > are valid after developers updates but by simply running the script to
>> > create
>> > DB and all its content does not produce any warning/error. How can we
>> > accomplish that?
>> >
>> > Thanks
>> > eXavier
>>|||"eXavier" <eXavier@.community.nospam> wrote in message
news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation?
Wayyy back in the day (SQL 6.5 I believe) this was possible, since SQL
checked for the existence of objects before creating stored procedures.
Apparently this caused a bunch of headaches, especially with database object
scripts that weren't created in the proper order, so "deferred name
resolution" was introduced. Table names are not resolved until run-time
instead of at creation time. Your best bet might be to 1) Do what Uri
suggested and explicitly check for the existence of tables yourself before
running the CREATE SP statement, or 2) Do what MS suggests below and test
your code after creation. Uri's suggestion should not be all that
difficult. Maybe a single script that tests for the existence of *all*
tables that are supposed to be in the database. You could run this script
before you try to run any SP creation scripts, and abort the mission based
on the result.
Here's what MS has to say on deferred name resolution:
"Deferred Name Resolution. Deferred name resolution allows procedure
compilation without all table references being present. Deferred name
resolution works in much the same way as the object-oriented concept of late
binding. At compile time, the compiler attempts to resolve all table names
that the procedure references. But if a table does not yet exist, the
compiler defers this name resolution until execution time.
For developers who have used temporary tables within their stored procedures
or triggers, this subtle new feature is long overdue. Although this feature
is useful, it has a side effect that many developers might initially
miss-the compiler no longer reliably catches table-name typos. Yes, you now
must test your code. This statement might sound funny at first, but if you
are not aware of deferred name resolution, you might find yourself wondering
why the compiler missed this error."
(From
http://www.microsoft.com/technet/prodtechnol/sql/70/deploy/migrat.mspx)|||Hi eXavier,
xyz's suggestion is reasonable. Appreciate your understanding that this
requirement is individual and actually limited by the design of SQL Server.
It is impossible for us to change the native behavior. I noticed that you
just wanted to ensure that scripts are valid after developers updates but
by simply running the script to create DB and all its content does not
produce any warning/error. You may consider to find a way from management.
For example, setup a test database environment which is same as the
development database then script a file to execute all the SPs or UDFs
that you want to check. This may require a standard process that the
developer of a SP or a UDF should provide a SQL test statement which can be
directly copied into the script file for checking at runtime. If some
errors are thrown out, please check and correct both the test database and
the development database. It is important to keep the identical environment
between the test database and the development database.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||You can catch most issues by executing the proc with SET FMTONLY ON like the
example below (passing any needed parameters as NULL values). However, some
errors can only be found by actually executing the proc.
SET FMTONLY ON
GO
EXEC dbo.SP1
GO
SET FMTONLY OFF
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"eXavier" <eXavier@.community.nospam> wrote in message
news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation? I mean, when I execute something like:
> CREATE PROC SP1 AS
> select * from X
> where table X does not exist, I want to get error or warning. Now the proc
> is created and the error is thrown only when it is executed. We have more
> then 1500 stored procedures in the database now scripted out into sql
> scripts. We want to ensure that scripts (and all objects like SPs,
> UDFs,..)
> are valid after developers updates but by simply running the script to
> create
> DB and all its content does not produce any warning/error. How can we
> accomplish that?
> Thanks
> eXavier

compile a store proc again

Hi there!!

Can we force stored procs in a database to recompile and see if there is any compile time error in the stored procs then and there.

Something like sp_recompile which marks Stored Proc for recompilation and compilation occurs later on.

I am looking for something which will force the existing stored procs to compile and let me see the error if any.

Best Regards

Rahul Kumar, Software Engineer, India

SP_RECOMPILE is helpful to recompile the next time they are run.

It will remove all the chache Plan for the current object when you run the SP it will compile it.

You can do the following step to get the compile errors...

Exec Sp_recompile 'MySp'
Exec @.R = MySp
If @.R = 0
Print 'Success'
Else
Print 'Failed on Compile'

But the drawback here is you can't find the error is occured by compiler or runtime execution.

|||

This can be done for one stored proc, but we cant have this approach if we want to recompile all the strored procs in the database- as they may be having different parameters.

|||

You can try calling DBCC FREEPROCCACHE

This will flush the procedure cache and the cached plans. Next time you execute the procedures, they will compile and you should be able to hit the compilation error.

|||

It all depends on what you mean by "recompile". I know that I was really keen that the meant what it sounded like, which would be to take the source code, build an executable module, then a plan, and get things ready.

When you run sp_recompile, all it does is bump an internal value that is used to tell objects to recompile because something has changed. It will not take existing source code and rebuild the executable.

What is the purpose you are trying to achieve? Find where structures have changed? This is something you need to do with source control. Either by having documentation you can search (using a tool, or just manually searching) or just rebuild your project from scratch. Even recompiling is not foolproof unfortunately because of delayed name resolution, meaning that, in a procedure, if it comes to a table name that does not exist, it assumes that you know what you are doing and that the object will be created later. Man, I hate delayed name resolution, except when I need it (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124490)

|||

Okay let me be bit specific what I am trying to achieve.--will also explain what i mean by "recompile"

I have a database in Sql Server 2000 and I am trying to copy it on Sql server 2005.Now we have many changes like sort of =* join which we have to do in stored procs.So I am trying to get list of stored procs which wont compile successfully on sql server 2005.

One way to get this list is with SQL Server 2005 Ugrade Advisor, I am surprise but it didnt give me an exhaustive list.

So I thought I would be better if I could recompile all the stored procs, and that what i am trying to achieve.

Best Regards

Rahul

|||

For that you would need to script out the stored procedures, and try to compile them. As long as object names are still the same (mitigating the whole question of delayed name resolution) then that will work.

If you know the pattern you are looking for, like =* or *=, you could use a search to find these cases:

DECLARE @.value nvarchar(128)
SET @.value = '=*'
SELECT cast(schema_name(schema_id) + '.' + name AS varchar(60)) AS name,
cast(type_desc AS varchar(20)) AS type , create_date,
modify_date,
char(13) + char(10)
+ '--select object_definition(' + cast(object_id as varchar(10)) + ') as [' + name + ']'
FROM sys.objects
WHERE charindex(@.value,replace(replace(object_definition(object_id),'[',''),']','')) > 0

--http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1139.entry

Object_definition is a new function in 2005 that gives you the text of the object in a nice package.

But, no, there is no way to have this kind of syntax check done automatically for you if the Upgrade Advisor misses it.

|||

Thanks Louis

this '=*' was just one issue I cited for example, there are and can (which i dont know) many more, which give error on sql server 2005 and even SQL Server 2005 Ugrade Advisor didnt catch.

Can we rewrite your given code as

select object_name(id) from syscomments where text like '%=*%'

@. Louis -- But, no, there is no way to have this kind of syntax check done automatically for you if the Upgrade Advisor misses it.

Does this mean that we can not identify affected stored proc if Upgrade Advisor misses them, expect by executing them.

Regards

Rahul

|||Install the SQL Server Best Practices Analyzer and run it against your SQL Server 2000 database. This has a rule to check for the older-style outer joins.|||

Yeah, anything that the program Umachandar mentions or the upgrade advisor miss will not be found. I was just giving an alternative for searching through your code if you find things you need to.

And no, syscomments cannot be relied upon for a precise search, since it is chunked into 4000 character chunks. Object_definition returns the full text as a varchar(max) that you can use the like on.

My experience iwth the Upgrade Advisor was pretty pleasant. We had very little trouble taking our 2000 databases from 2005, but then again, we try to keep up and usually make those kind of changes ahead of time (not that that helps you :)

|||

Just to site one example which made me wondering--

Here is a line of code from my stored proc

tsequal(TmStp, convert(varbinary(8), convert(bigint, @.xTmStp)))

Now the irony is my stored proc is got compiled in sql server 2000 and when i moved it to sql server 2005 and compiled it ther it gave me following error:-

Msg 102, Level 15, State 1, Procedure rsp_UpdPolSchdTmStp, Line 43

Incorrect syntax near 'TSEQUAL'.

Regards

Rahul Kumar

|||

Yep, TSEQUAL is gone for good in 2005 (http://sqljunkies.com/Forums/ShowPost.aspx?PostID=2534) but it wasn't documented for quite a while.

It isn't necessary, so you can change:

tsequal(TmStp, convert(varbinary(8), convert(bigint, @.xTmStp)))

to

TmStp = convert(varbinary(8), convert(bigint, @.xTmStp)))

though I am not sure why you are doing all of the converting. if @.xTmStp is of varbinary(8) type (or rowversion/timestamp type) you can just do:

TmStp = @.xTmStp