Database

3-Mar-10 21:32

I would guess that triggers are disabled for batch updates then, probably as a database default. This is a sensible idea, you do NOT want triggers firing in volume and you should be managing your data not relying on a trigger. I hate the bloody things, always causing trouble, hiding under the hood and causing issues bah!

Never underestimate the power of human stupidity
RAH

Re: ADO.NET Batch Update

MB_KSU4-Mar-10 3:56

MB_KSU

4-Mar-10 3:56

Good idea. I'm not a DBA so do you know for sure if there is an admin option that disable a trigger for batch updates?

Re: ADO.NET Batch Update

Mycroft Holmes4-Mar-10 11:14

calling stored procedure in case statement - Sql Server 2000

4-Mar-10 11:14

MB_KSU wrote:
do you know

No idea, especially on Oracle

Never underestimate the power of human stupidity
RAH

vamsimohan212-Mar-10 23:41

vamsimohan21

2-Mar-10 23:41

Can any one please tell me how to call a stored procedure from case statement.

Thanks In Advance

Re: calling stored procedure in case statement - Sql Server 2000

The Man from U.N.C.L.E.3-Mar-10 1:43

The Man from U.N.C.L.E.

3-Mar-10 1:43

A Case statement in SQL is an embedded part of a Select statement so you can't call a stored procedure in the middle, only a function. However I guess you mean something more like the following:

SQL

If @Test = 1
   Execute [Procedure1];
Else If @Test = 2
   Execute [Procedure2];
Else If @Test = 3
   Execute [Procedure3];
Else
   Execute [Procedure4];

If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
[My Articles] [My Website]

Re: calling stored procedure in case statement - Sql Server 2000

Pranay Rana3-Mar-10 19:04

Pranay Rana

3-Mar-10 19:04

you can also use case when for this.

Re: calling stored procedure in case statement - Sql Server 2000

Mycroft Holmes3-Mar-10 21:29

Re: calling stored procedure in case statement - Sql Server 2000

3-Mar-10 21:29

You are still not going to be able to exec another proc from withing the case statement! Case and Exec() do not go together!

Never underestimate the power of human stupidity
RAH

The Man from U.N.C.L.E.3-Mar-10 22:48

The Man from U.N.C.L.E.

3-Mar-10 22:48

No you can't.

To quote the Micosoft documentation[^]:

The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures. For a list of control-of-flow methods, see Control-of-Flow Language (Transact-SQL)[^].

The question was regarding calling stored procedures and that is clearly not possible from within a case statement.

If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
[My Articles] [My Website]

Whats wrong with SQL statement?

gengel2-Mar-10 19:47

Re: Whats wrong with SQL statement? [modified]

2-Mar-10 19:47

Table 1 (UNITS)
ScanID ScanCount OrderID SerialNo ScanStatus ProductionNo
1981 1294 2 147639NZ301774 SHIPPED 10007627289
There are 1300 SerialNo's for ProductionNo That will end with status 'SHIPPED'

Table 2 (Unit_Logs)
LogID SerialNo ProductionNo Old_Status New_Status
1 147639NZ301774 10007627289 PACKED SHIPPED
There will be 1300 records with status changes from PACKED to SHIPPED

Needed RESULT:
ProductionNo PACKED SHIPPED
10007627289 1300 1300

This is what I have tried:
SELECT UNITS.ProductionNo, COUNT(UNITS.SerialNo) AS SHIPPED,
COUNT(UNIT_Logs.SerialNo) AS PACKED
FROM UNITS JOIN UNIT_Logs
ON UNITS.ProductionNo = UNIT_Logs.ProductionNo
GROUP BY UNITS.ProductionNo, UNITS.ScanStatus, UNIT_Logs.Old_Status
HAVING (UNITS.ScanStatus = 'SHIPPED') AND (UNIT_Logs.Old_Status = 'PACKED')

I added 1 log entry to the table, but the query returns nothing.
Think might need sub query, but not sure where to go from here.

Jörgen Andersson2-Mar-10 21:48

Jörgen Andersson

2-Mar-10 21:48

HAVING is only meant to be used with aggregate functions.
Try something similar to this:

SELECT UNITS.ProductionNo, COUNT(UNITS.SerialNo) AS SHIPPED, 
COUNT(UNIT_Logs.SerialNo) AS PACKED
FROM UNITS JOIN UNIT_Logs
ON UNITS.ProductionNo = UNIT_Logs.ProductionNo
WHERE (UNITS.ScanStatus = 'SHIPPED') AND (UNIT_Logs.Old_Status = 'PACKED')
GROUP BY UNITS.ProductionNo, UNITS.ScanStatus, UNIT_Logs.Old_Status

<edit>fixed layout</edit>

"When did ignorance become a point of view" - Dilbert

modified on Wednesday, March 3, 2010 3:54 AM

i.j.russell2-Mar-10 22:04

i.j.russell

2-Mar-10 22:04

SELECT UNITS.ProductionNo, 
   COUNT(UNITS.SerialNo) AS SHIPPED,
   COUNT(UNIT_Logs.SerialNo) AS PACKED
FROM UNITS 
   JOIN UNIT_Logs
      ON UNITS.ProductionNo = UNIT_Logs.ProductionNo
WHERE (UNITS.ScanStatus = 'SHIPPED') AND (UNIT_Logs.Old_Status = 'PACKED')
GROUP BY UNITS.ProductionNo

gengel2-Mar-10 22:46

Re: Whats wrong with SQL statement? [modified]

2-Mar-10 22:46

Hi,

Thanks for the replies.

This query only seems to make a 1 to 1 join.

So if there is only one log entry it returns both SHIPPED and PACKED as 1 instead of 1294.

ProductionNo SHIPPED PACKED
10007627289 1 1

if i remove:
WHERE (UNITS.ScanStatus = 'SHIPPED') AND (UNIT_Logs.Old_Status = 'PACKED')

ProductionNo SHIPPED PACKED
10007627289 1294 1294

I tried this mess. Will try a few more options with this.
SELECT UNITS.ProductionNo, COUNT(UNITS.SerialNo) AS SHIPPED
FROM UNITS
WHERE UNITS.Scanstatus='SHIPPED' AND
(SELECT COUNT(UNIT_Logs.SerialNo) AS PACKED FROM UNIT_Logs WHERE UNIT_Logs.Old_Status='PACKED')
GROUP BY UNITS.ProductionNo

i.j.russell2-Mar-10 23:35

i.j.russell

2-Mar-10 23:35

Try this one.

SELECT u.ProductionNo,
   u.SHIPPED,
   l.PACKED
FROM 
(
   SELECT ProductionNo, 
      COUNT(SerialNo) AS SHIPPED
   FROM UNITS 
   WHERE ScanStatus = 'SHIPPED'
   GROUP BY ProductionNo
) u
LEFT OUTER JOIN 
(
   SELECT ProductionNo, 
      COUNT(SerialNo) AS PACKED
   FROM UNIT_Logs 
   WHERE Old_Status = 'PACKED'
   GROUP BY ProductionNo  
) l
ON l.ProductionNo = u.ProductionNo

[Fixed typo]

modified on Wednesday, March 3, 2010 7:56 AM

gengel3-Mar-10 1:14

3-Mar-10 1:14

Wow. did not even know you could do this.

I get this error though:
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near 'l'.

I am going to break it down into components to make sure the parts work and then try and connect it back together again.

Thanks

gengel3-Mar-10 1:17

3-Mar-10 1:17

Sorry found the problem. , before the FROM in the first bracket.

It works perfectly. Thanks for the help.

i.j.russell3-Mar-10 2:00

i.j.russell

3-Mar-10 2:00

You're welcome.

Permission sp_rename

Clas Andersson2-Mar-10 7:23

Clas Andersson

2-Mar-10 7:23

I am creating a dynamic pivot-table with web - sql-server 2008
When the pivot-table is created i want to rename columnname.
I am execute a stored procedure, sp_rename..... from the web-page.

Web-page - select variablesnames
Web-page - variablesname --> short columnname
SQl-server - create pivot table (dynamic sql, short columnnames - 4000 char limit)
Web-page/Sql-server - rename short columnnames to variablesnames

The problem is the permission to execute sp_rename .

How/where can I adjust permission for the the "webuser" in sql-server ?

Thanks in advance
Clas

Re: Permission sp_rename

Mycroft Holmes2-Mar-10 10:52