Stored Proc Update Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Proc Update Query

Author

Topic

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-11-24 : 09:00:41

I am changing my queries from Access into SQL Server and come accross a problem I cant see why...

This is my Access Query which would up date 22 out of 4964 records

UPDATE PARAMED_temp SET PARAMED_temp.ContactServiceID = "DNS"
WHERE (((PARAMED_temp.ContactServiceID) Is Null) And ((PARAMED_temp.StaffID) Like "D*" Or (PARAMED_temp.StaffID) Like "N*"));

and this is the same query in SQL Server through my Stored Procedure. My problem here is that it will update 4985 out of 4964 records and cant understand the massive difference and why I cant get the same results as I do from Access which is what I need...

ALTER PROCEDURE [jez].[sp_PARA_UpdateBlnkSpec0406]
@ContServID VARCHAR(25),
@StaffID1 VARCHAR(10),
@StaffID2 VARCHAR(10)
AS
UPDATE PARAMED_TEMP_BUILD
SET PARAMED_TEMP_BUILD.ContactServiceID = @ContServID
WHERE (((ContactServiceID IS NULL)
AND StaffID LIKE @StaffID1)
OR StaffID LIKE @StaffID2)

Where have I gone astray or wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 09:03:53

i think its because of missing paranthesis

ALTER PROCEDURE [jez].[sp_PARA_UpdateBlnkSpec0406]
@ContServID VARCHAR(25),
@StaffID1 VARCHAR(10),
@StaffID2 VARCHAR(10)
AS
UPDATE PARAMED_TEMP_BUILD
SET PARAMED_TEMP_BUILD.ContactServiceID = @ContServID
WHERE ((ContactServiceID IS NULL)
AND (StaffID LIKE @StaffID1
OR StaffID LIKE @StaffID2))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 09:04:58

No missing paranthesises. They are just at the wrong places.

ALTER PROCEDURE jez.sp_PARA_UpdateBlnkSpec0406
(
	@ContServID VARCHAR(25),
	@StaffID1 VARCHAR(10),
	@StaffID2 VARCHAR(10)
)
AS

SET NOCOUNT ON

UPDATE	PARAMED_TEMP_BUILD 
SET	ContactServiceID = @ContServID
WHERE	ContactServiceID IS NULL
	AND (StaffID LIKE @StaffID1 OR StaffID LIKE @StaffID2)

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 09:05:55

Also will you be passing % also as value with @StaffID1 & @StaffID2?
else it should be this


ALTER PROCEDURE [jez].[sp_PARA_UpdateBlnkSpec0406]
@ContServID VARCHAR(25),
@StaffID1 VARCHAR(10),
@StaffID2 VARCHAR(10)
AS
UPDATE PARAMED_TEMP_BUILD
SET PARAMED_TEMP_BUILD.ContactServiceID = @ContServID
WHERE ((ContactServiceID IS NULL)
AND (StaffID LIKE @StaffID1 + '%'
OR StaffID LIKE @StaffID2+'%'))

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-11-24 : 09:14:50

Excellent Thanks for that :-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 09:16:49

No problem

you're welcome

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-11-24 : 10:46:45

Again I'm having the same issue as my first thread, but this time I dont have any Paremeters to pass through. This is just a straight UPDATE.
Why in Access would I UPDATE 6444 records but in SQL I UPDATE 7838, I'm not sure where I have gone astray

Access
UPDATE WRONG_TSLC_ROWS LEFT JOIN PARAMED_temp
ON WRONG_TSLC_ROWS.EventID = PARAMED_temp.EventID
SET PARAMED_temp.TH_SOR_LOC_CAT = WRONG_TSLC_ROWS.dbo_Ref_Th_Sor_Loc_Cat_Matrix_TH_SOR_LOC_CAT;

SQL Server
ALTER PROCEDURE [jez].[sp_PARA_Updt_WrongTSLC_WithCorrections]
AS
UPDATE PARAMED_TEMP_BUILD
SET PARAMED_TEMP_BUILD.TH_SOR_LOC_CAT = PARA_WRONG_TSLC_ROWS.TH_SOR_LOC_CAT_MATRIX
FROM PARA_WRONG_TSLC_ROWS LEFT JOIN PARAMED_TEMP_BUILD
ON PARA_WRONG_TSLC_ROWS.EventID = PARAMED_TEMP_BUILD.EventID

Am I being daft and missed something so simple

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 10:50:15

[code]ALTER PROCEDURE jez.sp_PARA_Updt_WrongTSLC_WithCorrections
AS

UPDATE x
SET x.TH_SOR_LOC_CAT = r.TH_SOR_LOC_CAT_MATRIX
FROM PARA_WRONG_TSLC_ROWS AS r
LEFT JOIN PARAMED_TEMP_BUILD AS x ON x.EventID = r..EventID[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 10:50:50

whats the actual requirement? the above update just updates all rows in PARAMED_TEMP_BUILD whether or not it has matching records in PARA_WRONG_TSLC_ROWS. is it what you want?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 10:52:35

quote:

Originally posted by Peso

ALTER PROCEDURE jez.sp_PARA_Updt_WrongTSLC_WithCorrections
AS

UPDATE		x
SET		x.TH_SOR_LOC_CAT = r.TH_SOR_LOC_CAT_MATRIX
FROM		PARA_WRONG_TSLC_ROWS AS r
LEFT JOIN	PARAMED_TEMP_BUILD AS x ON x.EventID = r..EventID

E 12°55'05.63"
N 56°04'39.26"

fixed typo (extra .)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 10:54:39

See this example

DECLARE	@Sample TABLE
	(
		i INT,
		j INT
	)

INSERT	@Sample
	(
		i,
		j
	)
SELECT	1, 1 UNION ALL
SELECT	3, 0

DECLARE	@Temp TABLE
	(
		i INT
	)

INSERT	@Temp
	(
		i
	)
SELECT	1 UNION ALL
SELECT	1 UNION ALL
SELECT	1 UNION ALL
SELECT	2

UPDATE		s
SET		s.j = 1
FROM		@Sample AS s
LEFT JOIN	@Temp AS t ON t.i = s.i

SELECT	@@ROWCOUNT AS [rowCount]

SELECT * FROM @Sample

DELETE
FROM	@Sample

INSERT	@Sample
	(
		i,
		j
	)
SELECT	1, 1 UNION ALL
SELECT	3, 0

UPDATE		s
SET		s.j = 2
FROM		@Temp AS t
LEFT JOIN	@Sample AS s ON s.i = t.i

SELECT	@@ROWCOUNT AS [rowCount]

SELECT * FROM @Sample

E 12°55'05.63"
N 56°04'39.26"

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-11-24 : 10:56:08

I have tried that and it still gives me same results as I had. Why would the Access Query show far less results using same data?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 10:57:37

whats your exact requirement? can you explain?

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-11-24 : 11:04:59

quote:
Originally posted by visakh16

whats the actual requirement? the above update just updates all rows in PARAMED_TEMP_BUILD whether or not it has matching records in PARA_WRONG_TSLC_ROWS. is it what you want?

All I am trying to do is replicate my Queries from Access into the SQL Server. From this I cant understand why there is a big discrepancy between the two queries.
I am just wanting to update all the matching records between the two tables using the LEFT JOIN.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 11:05:04

See example posted 11/24/2008 : 10:54:39
In one case the rwocount is 1 and the other case the rowcount is 2.

E 12°55'05.63"
N 56°04'39.26"

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-11-24 : 11:06:06

quote:
Originally posted by Peso

See example posted 11/24/2008 : 10:54:39
In one case the rwocount is 1 and the other case the rowcount is 2.

E 12°55'05.63"
N 56°04'39.26"

Ok, I'll have a go at that. Thanks :-)

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-11-25 : 05:02:48

Looking at Thread - Posted - 11/24/2008 : 10:54:39 could it be explained what it does...

This is what I ave written, have I not understood what its doing at all?

DECLARE @Sample PARA_WRONG_TSLC_ROWS
(
i INT,
j INT
)
INSERT @Sample
(
i,
j
)
SELECT 1, 1 UNION ALL
SELECT 3, 0
DECLARE @Temp PARAMED_TEMP_BUILD
(
i INT
)
INSERT @Temp
(
i
)
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 2
UPDATE s
SET s.j = 1
FROM @Sample AS s
LEFT JOIN @Temp AS t ON t.i = s.i
SELECT @@ROWCOUNT AS [rowCount]
SELECT * FROM @Sample
DELETE
FROM @Sample
INSERT @Sample
(
i,
j
)
SELECT 1, 1 UNION ALL
SELECT 3, 0
UPDATE s
SET s.j = 2
FROM @Temp AS t
LEFT JOIN @Sample AS s ON s.i = t.i
SELECT @@ROWCOUNT AS [rowCount]
SELECT * FROM @Sample

These are the errors from it
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
Msg 1087, Level 15, State 2, Line 6
Must declare the table variable "@Sample".
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near 'i'.
Msg 1087, Level 15, State 2, Line 18
Must declare the table variable "@Temp".
Msg 1087, Level 15, State 2, Line 28
Must declare the table variable "@Sample".
Msg 1087, Level 15, State 2, Line 31
Must declare the table variable "@Sample".
Msg 1087, Level 15, State 2, Line 33
Must declare the table variable "@Sample".
Msg 1087, Level 15, State 2, Line 34
Must declare the table variable "@Sample".
Msg 1087, Level 15, State 2, Line 43
Must declare the table variable "@Temp".
Msg 1087, Level 15, State 2, Line 46
Must declare the table variable "@Sample".

Where have I gone wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 05:26:11

quote:
Originally posted by JezLisle

Looking at Thread - Posted - 11/24/2008 : 10:54:39 could it be explained what it does...

This is what I ave written, have I not understood what its doing at all?

DECLARE @Sample ~~PARA_WRONG_TSLC_ROWS~~ TABLE (
i INT,
j INT
)
INSERT @Sample
(
i,
j
)
SELECT 1, 1 UNION ALL
SELECT 3, 0
DECLARE @Temp ~~PARAMED_TEMP_BUILD~~ TABLE (
i INT
)
INSERT @Temp
(
i
)
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 2
UPDATE s
SET s.j = 1
FROM @Sample AS s
LEFT JOIN @Temp AS t ON t.i = s.i
SELECT @@ROWCOUNT AS [rowCount]
SELECT * FROM @Sample
DELETE
FROM @Sample
INSERT @Sample
(
i,
j
)
SELECT 1, 1 UNION ALL
SELECT 3, 0
UPDATE s
SET s.j = 2
FROM @Temp AS t
LEFT JOIN @Sample AS s ON s.i = t.i
SELECT @@ROWCOUNT AS [rowCount]
SELECT * FROM @Sample

These are the errors from it
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
Msg 1087, Level 15, State 2, Line 6
Must declare the table variable "@Sample".
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near 'i'.
Msg 1087, Level 15, State 2, Line 18
Must declare the table variable "@Temp".
Msg 1087, Level 15, State 2, Line 28
Must declare the table variable "@Sample".
Msg 1087, Level 15, State 2, Line 31
Must declare the table variable "@Sample".
Msg 1087, Level 15, State 2, Line 33
Must declare the table variable "@Sample".
Msg 1087, Level 15, State 2, Line 34
Must declare the table variable "@Sample".
Msg 1087, Level 15, State 2, Line 43
Must declare the table variable "@Temp".
Msg 1087, Level 15, State 2, Line 46
Must declare the table variable "@Sample".

Where have I gone wrong?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources