Select row from other table in Stored Procedures!

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 2008 Forums

Transact-SQL (2008)

Select row from other table in Stored Procedures!

Author

Topic

ezramod
Starting Member

12 Posts

Posted - 2013-06-30 : 01:39:27

Hy, i have little procedure for update some row when is activate:

quote:

USE [Ranking]
GO
/****** Object: StoredProcedure [dbo].[TT_DuelScore] Script Date: 06/30/2013 08:31:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TT_DuelScore]
@ServerCode INT,
@AccountID VARCHAR(10),
@Name VARCHAR(10),
@Result INT,
@Average INT
As
Begin
BEGIN TRANSACTION
SET NOCOUNT ON

IF EXISTS (SELECT NULL FROM DUEL_INFO WHERE Account = @AccountID AND Name = @Name AND ServerCode = @ServerCode)
IF(@Result = 1)
UPDATE DUEL_INFO SET Win = Win+1, Average = Average + @Average WHERE Account = @AccountID AND Name = @Name AND ServerCode = @ServerCode
ELSE
UPDATE DUEL_INFO SET Lost = Lost+1, Average = Average + @Average WHERE Account = @AccountID AND Name = @Name AND ServerCode = @ServerCode
ELSE
IF(@Result = 1)
UPDATE MuOnline.dbo.Character SET reputation = reputation+1 WHERE AccountID = @AccountID AND Name = @Name
ELSE
IF(@Result = 1)
INSERT INTO DUEL_INFO (ServerCode, Account, Name, Win, Lost, Average) VALUES (@ServerCode,@AccountID,@Name,1,0,@Average)
ELSE
INSERT INTO DUEL_INFO (ServerCode, Account, Name, Win, Lost, Average) VALUES (@ServerCode,@AccountID,@Name,0,1,@Average)

IF(@@Error <> 0 )
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

SET NOCOUNT OFF
End

but i want to insert new line with new update row , but new row is form oterh table :
- in PHP code for update that row is :

quote:

$guildname=mssql_fetch_array(mssql_query("SELECT G_Name FROM MuOnline.dbo.GuildMember WHERE Name='$char'"));
$g_update = mssql_query("Update MuOnline.dbo.Guild set [G_Score]=G_Score+1 where G_Name='$guildname[G_Name]'");

Now who have ideea how to insert this php script in my store procedure?

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-06-30 : 09:18:50

You can use output clause
http://msdn.microsoft.com/en-us/library/ms177523.aspx

depending on what you want to insert


Update MuOnline.dbo.Guild 
set [G_Score]=G_Score+1 
OUTPUT Inserted.G_Name,Inserted.G_Score Into [anotherTable]
where G_Name=(SELECT G_Name FROM MuOnline.dbo.GuildMember WHERE Name=@Name)

the key line is :
OUTPUT Inserted.G_Name,Inserted.G_Score Into [anotherTable]

[anotherTable] = is your table ;could be a @Tbl or #tbl

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-06-30 : 10:55:15

Something is flawed with your code. There is one line that will never happen!

IF EXISTS (SELECT NULL FROM DUEL_INFO WHERE Account = @AccountID AND Name = @Name AND ServerCode = @ServerCode) 
	IF(@Result = 1)
		UPDATE DUEL_INFO SET Win = Win+1, Average = Average + @Average WHERE Account = @AccountID AND Name = @Name AND ServerCode = @ServerCode
	ELSE
		UPDATE DUEL_INFO SET Lost = Lost+1, Average = Average + @Average WHERE Account = @AccountID AND Name = @Name AND ServerCode = @ServerCode
ELSE
	IF(@Result = 1)
		UPDATE MuOnline.dbo.Character SET reputation = reputation+1 WHERE AccountID = @AccountID AND Name = @Name
	ELSE 
		IF(@Result = 1)
			-- THIS ONE WILL NEVER HAPPEN!!!
			INSERT INTO DUEL_INFO (ServerCode, Account, Name, Win, Lost, Average) VALUES (@ServerCode,@AccountID,@Name,1,0,@Average)
		ELSE
			INSERT INTO DUEL_INFO (ServerCode, Account, Name, Win, Lost, Average) VALUES (@ServerCode,@AccountID,@Name,0,1,@Average)

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

ezramod
Starting Member

12 Posts

Posted - 2013-06-30 : 12:08:24

@SwePeso - that line happen because there search if exist record for each player, if not exist that line create.

ezramod
Starting Member

12 Posts

Posted - 2013-06-30 : 12:32:29

if i start this topic i remember what line now work in my code and that is :
ELSE
IF(@Result = 1)
UPDATE MuOnline.dbo.Character SET reputation = reputation+1 WHERE AccountID = @AccountID AND Name = @Name

and i retry to code and i try in this mode:
CREATE PROCEDURE [dbo].[TT_DuelScore]
@ServerCode INT,
@AccountID VARCHAR(10),
@Name VARCHAR(10),
@Result INT,
@Average INT
As
Begin
BEGIN TRANSACTION
SET NOCOUNT ON

IF EXISTS (SELECT NULL FROM DUEL_INFO WHERE Account = @AccountID AND Name = @Name AND ServerCode = @ServerCode)
IF(@Result = 1)
UPDATE DUEL_INFO SET Win = Win+1, Average = Average + @Average WHERE Account = @AccountID AND Name = @Name AND ServerCode = @ServerCode
Update MuOnline.dbo.Character set reputation = reputation+1 where AccountID = @AccountID AND Name = @Name
ELSE
UPDATE DUEL_INFO SET Lost = Lost+1, Average = Average + @Average WHERE Account = @AccountID AND Name = @Name AND ServerCode = @ServerCode
Update MuOnline.dbo.Character set reputation = reputation-1 where AccountID = @AccountID AND Name = @Name
ELSE
IF(@Result = 1)
INSERT INTO DUEL_INFO (ServerCode, Account, Name, Win, Lost, Average) VALUES (@ServerCode,@AccountID,@Name,1,0,@Average)
ELSE
INSERT INTO DUEL_INFO (ServerCode, Account, Name, Win, Lost, Average) VALUES (@ServerCode,@AccountID,@Name,0,1,@Average)

IF(@@Error <> 0 )
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

SET NOCOUNT OFF
End

but no sql 2008 not accept this code because appear this error:
Msg 156, Level 15, State 1, Procedure TT_DuelScore, Line 16
Incorrect syntax near the keyword 'ELSE'.
Msg 156, Level 15, State 1, Procedure TT_DuelScore, Line 19
Incorrect syntax near the keyword 'ELSE'.

ezramod
Starting Member

12 Posts

Posted - 2013-06-30 : 12:39:29

i find solution for work that line the correct code was:
USE [Ranking]
GO
/****** Object: StoredProcedure [dbo].[TT_DuelScore] Script Date: 06/30/2013 19:35:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TT_DuelScore]
@ServerCode INT,
@AccountID VARCHAR(10),
@Name VARCHAR(10),
@Result INT,
@Average INT
As
Begin
BEGIN TRANSACTION
SET NOCOUNT ON

IF EXISTS (SELECT NULL FROM DUEL_INFO WHERE Account = @AccountID AND Name = @Name AND ServerCode = @ServerCode)
IF(@Result = 1)
UPDATE DUEL_INFO SET Win = Win+1, Average = Average + @Average WHERE Account = @AccountID AND Name = @Name AND ServerCode = @ServerCode
ELSE
UPDATE DUEL_INFO SET Lost = Lost+1, Average = Average + @Average WHERE Account = @AccountID AND Name = @Name AND ServerCode = @ServerCode

IF EXISTS (SELECT NULL FROM DUEL_INFO WHERE Account = @AccountID AND Name = @Name AND ServerCode = @ServerCode)
IF(@Result = 1)
UPDATE Muonline.dbo.Character set reputation = reputation+1 where AccountID = @AccountID AND Name = @Name
ELSE
UPDATE Muonline.dbo.Character set reputation = reputation-1 where AccountID = @AccountID AND Name = @Name

ELSE
IF(@Result = 1)
INSERT INTO DUEL_INFO (ServerCode, Account, Name, Win, Lost, Average) VALUES (@ServerCode,@AccountID,@Name,1,0,@Average)
ELSE
INSERT INTO DUEL_INFO (ServerCode, Account, Name, Win, Lost, Average) VALUES (@ServerCode,@AccountID,@Name,0,1,@Average)

IF(@@Error <> 0 )
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

SET NOCOUNT OFF
End

now need to find how to update row with GUILD SCORE:D

ezramod
Starting Member

12 Posts

Posted - 2013-06-30 : 12:52:03

Thx for idee admin can close this topic because result is :

USE [Ranking]
GO
/****** Object:  StoredProcedure [dbo].[TT_DuelScore]    Script Date: 06/30/2013 19:41:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE	[dbo].[TT_DuelScore]
	@ServerCode		INT,
	@AccountID		VARCHAR(10),
	@Name			VARCHAR(10),
	@Result			INT,
	@Average		INT
As
Begin
	BEGIN TRANSACTION
	SET NOCOUNT ON	

	IF EXISTS (SELECT NULL FROM DUEL_INFO WHERE Account = @AccountID AND Name = @Name AND ServerCode = @ServerCode) 
		IF(@Result = 1)
		UPDATE DUEL_INFO SET Win = Win+1, Average = Average + @Average WHERE Account = @AccountID AND Name = @Name AND ServerCode = @ServerCode
		ELSE
		UPDATE DUEL_INFO SET Lost = Lost+1, Average = Average + @Average WHERE Account = @AccountID AND Name = @Name AND ServerCode = @ServerCode

IF EXISTS (SELECT NULL FROM DUEL_INFO WHERE Account = @AccountID AND Name = @Name AND ServerCode = @ServerCode) 
		IF(@Result = 1)
		UPDATE Muonline.dbo.Character set reputation = reputation+1 where AccountID = @AccountID AND Name = @Name
		ELSE
		UPDATE Muonline.dbo.Character set reputation = reputation-1 where AccountID = @AccountID AND Name = @Name
IF EXISTS (SELECT NULL FROM DUEL_INFO WHERE Account = @AccountID AND Name = @Name AND ServerCode = @ServerCode) 
		IF(@Result = 1)
		UPDATE MuOnline.dbo.Guild set G_Score = G_Score+1 where G_Name = (SELECT G_Name FROM MuOnline.dbo.GuildMember WHERE Name = @Name)
		ELSE
		UPDATE MuOnline.dbo.Guild set G_Score = G_Score-2 where G_Name = (SELECT G_Name FROM MuOnline.dbo.GuildMember WHERE Name = @Name)		
	ELSE 
		IF(@Result = 1)
			INSERT INTO DUEL_INFO (ServerCode, Account, Name, Win, Lost, Average) VALUES (@ServerCode,@AccountID,@Name,1,0,@Average)
		ELSE
			INSERT INTO DUEL_INFO (ServerCode, Account, Name, Win, Lost, Average) VALUES (@ServerCode,@AccountID,@Name,0,1,@Average)

	IF(@@Error <> 0 )
		ROLLBACK TRANSACTION
	ELSE	
		COMMIT TRANSACTION

	SET NOCOUNT OFF	
End

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources