SQL Stored Procedure problem
SQL Stored Procedure problem
SQL Stored Procedure problem
SQL Stored Procedure problem
SQL Stored Procedure problem
SQL Stored Procedure problem SQL Stored Procedure problem SQL Stored Procedure problem SQL Stored Procedure problem SQL Stored Procedure problem SQL Stored Procedure problem SQL Stored Procedure problem SQL Stored Procedure problem
SQL Stored Procedure problem SQL Stored Procedure problem
SQL Stored Procedure problem
Go Back  Xtreme Visual Basic Talk > > > SQL Stored Procedure problem


Reply
 
Thread Tools Display Modes
  #1  
Old 08-27-2012, 03:56 PM
FabioRochaPT FabioRochaPT is offline
Newcomer
 
Join Date: Mar 2010
Posts: 22
Default SQL Stored Procedure problem


Hey

I have this sql stored procedure:
Code:
CREATE PROCEDURE [dbo].[InsertTransaction]
@id INT,
@document VARCHAR(50),
@Doc_Date DATE,
@Origin VARCHAR(50),
@Destiny VARCHAR(50),
@Amount DECIMAL(9,2),
@Info VARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;
         INSERT INTO Transactions( [id], [Document], [Doc_Date], [Origin], [Destiny], [Amount], [Info])
VALUES( @Id, @Document, @Doc_Date, @Origin, @Destiny, @Amount, @Info)
END
And i use this on Vb.net:

Code:
cmd.CommandType = CommandType.StoredProcedure
                                        cmd.CommandText = "InsertTransaction"
                                        cmd.Parameters.Add("@ID", SqlDbType.Int).Value = Identify
                                        cmd.Parameters("@ID").Direction = ParameterDirection.Input
                                        cmd.Parameters.Add("@Document", SqlDbType.NChar).Value = frmMain.Documento
                                        cmd.Parameters("@Document").Direction = ParameterDirection.Input
                                        cmd.Parameters.Add("@Doc_date", SqlDbType.Date).Value = DPdate.Value.Date
                                        cmd.Parameters("@Doc_date").Direction = ParameterDirection.Input
                                        cmd.Parameters.Add("@Origin", SqlDbType.NChar).Value = accnamefrom
                                        cmd.Parameters("@Origin").Direction = ParameterDirection.Input
                                        cmd.Parameters.Add("@Destiny", SqlDbType.NChar).Value = accnameto
                                        cmd.Parameters("@Destiny").Direction = ParameterDirection.Input
                                        cmd.Parameters.Add("@Amount", SqlDbType.Decimal, 9).Value = txtAmount.Text
                                        cmd.Parameters("@Amount").Direction = ParameterDirection.Input
                                        cmd.Parameters.Add("@Info", SqlDbType.NChar).Value = txtInfo.Text
                                        cmd.Parameters("@Info").Direction = ParameterDirection.Input
                                        Dim x As Integer = cmd.ExecuteNonQuery()
To me it all seems good but the ExecuteNonQuery is returning the value -1 even tho everything goes good and all the records are stored on the table as it was supposed to.

Ive tried to figure out what was wrong without success. If anyone can help id be thankfull.
Reply With Quote
  #2  
Old 08-27-2012, 04:08 PM
PlausiblyDamp's Avatar
PlausiblyDampSQL Stored Procedure problem PlausiblyDamp is offline
Ultimate Contributor

Forum Leader
* Expert *
 
Join Date: Nov 2003
Location: Newport, Wales
Posts: 2,058
Default

I notice that at the start of your stored procedure you are using
Code:
SET NOCOUNT ON
This effectively stops SQL Server counting the number of modified rows, ExecuteNonQuery relies on this to detect if the update worked or not.
__________________
Intellectuals solve problems; geniuses prevent them.
-- Albert Einstein

Posting Guidelines Forum Rules Use the code tags
Reply With Quote
  #3  
Old 08-27-2012, 04:25 PM
FabioRochaPT FabioRochaPT is offline
Newcomer
 
Join Date: Mar 2010
Posts: 22
Default

Quote:
Originally Posted by PlausiblyDamp View Post
I notice that at the start of your stored procedure you are using
Code:
SET NOCOUNT ON
This effectively stops SQL Server counting the number of modified rows, ExecuteNonQuery relies on this to detect if the update worked or not.
Thanks for the answer. I removed it but nothing changed.

Using the SQL ERROR handle i got to understand that the vb.net is not passing any parameters???? Please check:

Code:
CREATE PROCEDURE [dbo].[InsertTransaction]


@id int,
@Document VARCHAR(50),
@Doc_Date DATE,
@Origin VARCHAR(50),
@Destiny VARCHAR(50),
@Amount DECIMAL(9,2),
@Info VARCHAR(100)

AS
DECLARE @RC int
EXEC @RC = InsertTransaction
PRINT 'Return value = ' + CONVERT(varchar(10), @RC)
DECLARE @myERROR int -- Local @@ERROR
       , @myRowCount int -- Local @@ROWCOUNT
BEGIN TRAN
         INSERT INTO Transactions(id, Document, Doc_Date, Origin, Destiny, Amount, Info)
VALUES( @id, @Document, @Doc_Date, @Origin, @Destiny, @Amount, @Info)
SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT
    IF @myERROR != 0 GOTO HANDLE_ERROR
HANDLE_ERROR:
    ROLLBACK TRAN
    RETURN @myERROR
Error found: Msg 201, Level 16, State 4, Procedure InsertTransaction, Line 0
Procedure or function 'InsertTransaction' expects parameter '@id', which was not supplied.


(1 row(s) affected)

(1 row(s) affected)

edit: The problem has to be within the SQL Procedure. When i run the procedure inside the sql and provide the parameters it still gives me the same error.

Last edited by FabioRochaPT; 08-27-2012 at 04:42 PM.
Reply With Quote
  #4  
Old 08-27-2012, 05:27 PM
PlausiblyDamp's Avatar
PlausiblyDampSQL Stored Procedure problem PlausiblyDamp is offline
Ultimate Contributor

Forum Leader
* Expert *
 
Join Date: Nov 2003
Location: Newport, Wales
Posts: 2,058
Default

My SQL is a bit rusty these days but isn't the line
Code:
EXEC @RC = InsertTransaction
going to attempt to execute the stored proc again? If so that would explain the error as you are trying to call it with no parameters.

You might want to check your error handler as well, if you detect an error you jump to HANDLE_ERROR, however if there is no error you still drop into the error handler anyway.
__________________
Intellectuals solve problems; geniuses prevent them.
-- Albert Einstein

Posting Guidelines Forum Rules Use the code tags
Reply With Quote
  #5  
Old 08-28-2012, 03:28 AM
DrPunk's Avatar
DrPunkSQL Stored Procedure problem DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

Quote:
Originally Posted by FabioRochaPT View Post
Hey

I have this sql stored procedure:
Code:
CREATE PROCEDURE [dbo].[InsertTransaction]
@id INT,
@document VARCHAR(50),
@Doc_Date DATE,
@Origin VARCHAR(50),
@Destiny VARCHAR(50),
@Amount DECIMAL(9,2),
@Info VARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;
         INSERT INTO Transactions( [id], [Document], [Doc_Date], [Origin], [Destiny], [Amount], [Info])
VALUES( @Id, @Document, @Doc_Date, @Origin, @Destiny, @Amount, @Info)
END
And i use this on Vb.net:

Code:
cmd.CommandType = CommandType.StoredProcedure
                                        cmd.CommandText = "InsertTransaction"
                                        cmd.Parameters.Add("@ID", SqlDbType.Int).Value = Identify
                                        cmd.Parameters("@ID").Direction = ParameterDirection.Input
                                        cmd.Parameters.Add("@Document", SqlDbType.NChar).Value = frmMain.Documento
                                        cmd.Parameters("@Document").Direction = ParameterDirection.Input
                                        cmd.Parameters.Add("@Doc_date", SqlDbType.Date).Value = DPdate.Value.Date
                                        cmd.Parameters("@Doc_date").Direction = ParameterDirection.Input
                                        cmd.Parameters.Add("@Origin", SqlDbType.NChar).Value = accnamefrom
                                        cmd.Parameters("@Origin").Direction = ParameterDirection.Input
                                        cmd.Parameters.Add("@Destiny", SqlDbType.NChar).Value = accnameto
                                        cmd.Parameters("@Destiny").Direction = ParameterDirection.Input
                                        cmd.Parameters.Add("@Amount", SqlDbType.Decimal, 9).Value = txtAmount.Text
                                        cmd.Parameters("@Amount").Direction = ParameterDirection.Input
                                        cmd.Parameters.Add("@Info", SqlDbType.NChar).Value = txtInfo.Text
                                        cmd.Parameters("@Info").Direction = ParameterDirection.Input
                                        Dim x As Integer = cmd.ExecuteNonQuery()
To me it all seems good but the ExecuteNonQuery is returning the value -1 even tho everything goes good and all the records are stored on the table as it was supposed to.

Ive tried to figure out what was wrong without success. If anyone can help id be thankfull.
The parameters in your SQLCommand don't match those in the stored procedure.

Code:
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "InsertTransaction"

cmd.Parameters.Add("@ID", SqlDbType.Int).Value = Identify
                                        
cmd.Parameters.Add("@Document", SqlDbType.VarChar, 50).Value = frmMain.Documento
                                        
cmd.Parameters.Add("@Doc_date", SqlDbType.Date).Value = DPdate.Value.Date
                                        
cmd.Parameters.Add("@Origin", SqlDbType.Varchar, 50).Value = accnamefrom
                                        
cmd.Parameters.Add("@Destiny", SqlDbType.varchar, 50).Value = accnameto
                                        
cmd.Parameters.Add("@Amount", SqlDbType.Decimal, 9).Value = txtAmount.Text
                                        
cmd.Parameters.Add("@Info", SqlDbType.varchar, 100).Value = txtInfo.Text
                                        
Dim x As Integer = cmd.ExecuteNonQuery()
__________________
There are no computers in heaven!

Last edited by DrPunk; 08-28-2012 at 03:57 AM.
Reply With Quote
  #6  
Old 08-28-2012, 04:47 AM
FabioRochaPT FabioRochaPT is offline
Newcomer
 
Join Date: Mar 2010
Posts: 22
Default

So i dont need to tell that the arguments are input?
Reply With Quote
  #7  
Old 08-28-2012, 04:54 AM
DrPunk's Avatar
DrPunkSQL Stored Procedure problem DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

They are Input by default so you don't REALLY need to specify it. No harm in doing so. Just gets tiresome typing it out.
__________________
There are no computers in heaven!
Reply With Quote
  #8  
Old 08-28-2012, 05:39 AM
FabioRochaPT FabioRochaPT is offline
Newcomer
 
Join Date: Mar 2010
Posts: 22
Default

Quote:
Originally Posted by DrPunk View Post
They are Input by default so you don't REALLY need to specify it. No harm in doing so. Just gets tiresome typing it out.
Today the stored procedure is working. The only change i did was removing the Set Nocount ON. Yesterday wasn't working because i probably had to reboot the sql server. Since i turned the computer off yesterday, today all was working like a charm.

I'm sorry for the newbie questions, it's my first stored procedure and i'm just really trying to learn and understand how it works.

Now if you could help me trying to get the stored procedure to return sql errors that would be excellent.

Thanks everyone for all the help.
Reply With Quote
  #9  
Old 08-28-2012, 05:44 AM
FabioRochaPT FabioRochaPT is offline
Newcomer
 
Join Date: Mar 2010
Posts: 22
Default

Quote:
CREATE PROCEDURE [dbo].[InsertTransaction]

@id int,
@Document VARCHAR(50),
@Doc_Date DATE,
@Origin VARCHAR(50),
@Destiny VARCHAR(50),
@Amount DECIMAL(9,2),
@Info VARCHAR(100)

AS

BEGIN Try
INSERT INTO Transactions(id, Document, Doc_Date, Origin, Destiny, Amount, Info)
VALUES( @id, @Document, @Doc_Date, @Origin, @Destiny, @Amount, @Info)

End Try
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
This is how it's working. Could you please check if this should be ok?

Im using sql server 2012 and VS 2012.
Reply With Quote
  #10  
Old 08-28-2012, 05:51 AM
DrPunk's Avatar
DrPunkSQL Stored Procedure problem DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

I don't really understand some of the logic going on. Firstly let's not get confused by a database TRANSACTION and your transaction table. Where I refer to TRANSACTION I'm talking about the TRANSACTION that is begun and then commited or rolled back.

Part of the problem is that I don't see where the TRANSACTION that you are commiting or rolling back is begun.

Because if the TRANSACTION is began just to add this record then it is a waste of time and not required. If the INSERT fails then the record does not get added so there is nothing to rollback.

If the TRANSACTION is began somewhere else then that isn't good practice because this StoredProcedure won't work without whatever starts the TRANSACTION.

Knowing more about what you're trying to do with the TRANSACTION would be useful instead of me giving poor advice.
__________________
There are no computers in heaven!
Reply With Quote
  #11  
Old 08-28-2012, 06:14 AM
DrPunk's Avatar
DrPunkSQL Stored Procedure problem DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

Quote:
Originally Posted by FabioRochaPT View Post
Now if you could help me trying to get the stored procedure to return sql errors that would be excellent.
Either don't Catch them in the stored procedure, or you have to Throw it again in the Catch
Code:
BEGIN TRY
     -- Try doing something to the database
END TRY

BEGIN CATCH
    -- Do something when it has errored
    
    -- Raise the error again for whatever called this to handle
    THROW;
END CATCH;
__________________
There are no computers in heaven!
Reply With Quote
  #12  
Old 08-28-2012, 10:04 AM
FabioRochaPT FabioRochaPT is offline
Newcomer
 
Join Date: Mar 2010
Posts: 22
Default

Quote:
Originally Posted by DrPunk View Post
I don't really understand some of the logic going on. Firstly let's not get confused by a database TRANSACTION and your transaction table. Where I refer to TRANSACTION I'm talking about the TRANSACTION that is begun and then commited or rolled back.

Part of the problem is that I don't see where the TRANSACTION that you are commiting or rolling back is begun.

Because if the TRANSACTION is began just to add this record then it is a waste of time and not required. If the INSERT fails then the record does not get added so there is nothing to rollback.

If the TRANSACTION is began somewhere else then that isn't good practice because this StoredProcedure won't work without whatever starts the TRANSACTION.

Knowing more about what you're trying to do with the TRANSACTION would be useful instead of me giving poor advice.
Ok let me try to explain...

Ive a little project to manage home finances, so the program stores everything you do with money, payments, money transferences, credit cards and so on...

So everytime you make a payment, a transaction happens but others tables will also be updated. For example a bank transference:

Account X sends money to Account Y

Insert into Transactions
Update Account X Balance
Update Account Y Balance
Commit or Rollback in case of error.

Thats where i am aiming, but as i told you ive been learning for not so long.

All your help is appreciated.
Reply With Quote
  #13  
Old 08-29-2012, 02:47 AM
DrPunk's Avatar
DrPunkSQL Stored Procedure problem DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

One thing to bear in mind is that database transactions can be done in the application's code as well as being done by the stored procedures themselves.

So the stored procedures themselves don't have to have transaction handling. You could leave that up to the program calling the stored procedures. So you just have 2 stored procedures UpdateBalance and InsertTransaction and the calling application creates a transaction to run those commands against and acts accordingly. There'd be no error handling in the stored procedures so that the main application can handle them if they occur.

The main benefit of doing that is that the caller application can use those stored procedures however they want to. Let's say that some new transfer is added to the application that requires 3 account balances to be updated and 2 TransferTransactions to be created. If that was the case then your InsertTransaction stored procedure would no longer work because it would commit the transaction before a second InsertTransaction could be run. Whereas UpdateBalance and InsertTransaction with no transactions or errorhandling could be used by the main application to do this new transfer by handling the errors and transaction itself.

But that's not to say you should never do error handling and transactions in stored procedures. But what I'd do is create a new Stored Procedure that used the other stored procedures to do its thing.

For example, you might have a stored procedure called TransferXtoY which calls those other stored procedures, with the errorhandling and transaction in this new stored procedure.

Note, this code ain't syntactically correct...
Code:
BEGIN TRY
     BEGIN TRANSACTION

     EXEC UpdateAccount(x, 1)
     EXEC UpdateAccount(y, -1)
     EXEC InsertTransaction(x, y, 1)

     COMMIT 
END TRY

BEGIN CATCH
    ROLLBACK

    -- Although you've handled everything you probably still want to tell the caller
    -- it failed. You could raise your own custom error if you wanted
    THROW;
END CATCH;
The point being that anyone can use the stored procedures how they want to, without there being any special requirements to run them (i.e. UpdateAccount must be run before InsertTransaction if InsertTransaction is commiting the transaction, and where is the transaction begun because it can't be begun in UpdateAccount because otherwise you'd begin 2 transactions).

In this example, the InsertTransaction stored procedure would become very basic...
Code:
CREATE PROCEDURE [dbo].[InsertTransaction]

 @id int,
 @Document VARCHAR(50),
 @Doc_Date DATE,
 @Origin VARCHAR(50),
 @Destiny VARCHAR(50),
 @Amount DECIMAL(9,2),
 @Info VARCHAR(100)

 AS

BEGIN 
   INSERT INTO Transactions(id, Document, Doc_Date, Origin, Destiny, Amount, Info)
   VALUES( @id, @Document, @Doc_Date, @Origin, @Destiny, @Amount, @Info)

END
__________________
There are no computers in heaven!

Last edited by DrPunk; 08-29-2012 at 03:14 AM.
Reply With Quote
  #14  
Old 08-29-2012, 04:57 AM
FabioRochaPT FabioRochaPT is offline
Newcomer
 
Join Date: Mar 2010
Posts: 22
Default

Thanks my friend. Right now i don't have time to try this but later today ill have something coded and ill post back the results.


Quote:
Originally Posted by DrPunk View Post
One thing to bear in mind is that database transactions can be done in the application's code as well as being done by the stored procedures themselves.

So the stored procedures themselves don't have to have transaction handling. You could leave that up to the program calling the stored procedures. So you just have 2 stored procedures UpdateBalance and InsertTransaction and the calling application creates a transaction to run those commands against and acts accordingly. There'd be no error handling in the stored procedures so that the main application can handle them if they occur.

The main benefit of doing that is that the caller application can use those stored procedures however they want to. Let's say that some new transfer is added to the application that requires 3 account balances to be updated and 2 TransferTransactions to be created. If that was the case then your InsertTransaction stored procedure would no longer work because it would commit the transaction before a second InsertTransaction could be run. Whereas UpdateBalance and InsertTransaction with no transactions or errorhandling could be used by the main application to do this new transfer by handling the errors and transaction itself.

But that's not to say you should never do error handling and transactions in stored procedures. But what I'd do is create a new Stored Procedure that used the other stored procedures to do its thing.

For example, you might have a stored procedure called TransferXtoY which calls those other stored procedures, with the errorhandling and transaction in this new stored procedure.

Note, this code ain't syntactically correct...
Code:
BEGIN TRY
     BEGIN TRANSACTION

     EXEC UpdateAccount(x, 1)
     EXEC UpdateAccount(y, -1)
     EXEC InsertTransaction(x, y, 1)

     COMMIT 
END TRY

BEGIN CATCH
    ROLLBACK

    -- Although you've handled everything you probably still want to tell the caller
    -- it failed. You could raise your own custom error if you wanted
    THROW;
END CATCH;
The point being that anyone can use the stored procedures how they want to, without there being any special requirements to run them (i.e. UpdateAccount must be run before InsertTransaction if InsertTransaction is commiting the transaction, and where is the transaction begun because it can't be begun in UpdateAccount because otherwise you'd begin 2 transactions).

In this example, the InsertTransaction stored procedure would become very basic...
Code:
CREATE PROCEDURE [dbo].[InsertTransaction]

 @id int,
 @Document VARCHAR(50),
 @Doc_Date DATE,
 @Origin VARCHAR(50),
 @Destiny VARCHAR(50),
 @Amount DECIMAL(9,2),
 @Info VARCHAR(100)

 AS

BEGIN 
   INSERT INTO Transactions(id, Document, Doc_Date, Origin, Destiny, Amount, Info)
   VALUES( @id, @Document, @Doc_Date, @Origin, @Destiny, @Amount, @Info)

END
Reply With Quote
  #15  
Old 08-29-2012, 12:20 PM
FabioRochaPT FabioRochaPT is offline
Newcomer
 
Join Date: Mar 2010
Posts: 22
Default

Hey again, working on this stored procedures again. Learning a bit of T-SQL not beeing so easy....

Anyway ive a little doubt:


Code:
CREATE PROCEDURE dbo.cctableto

	@Origin varchar(100),
	@Destiny varchar(100),
	@Amount Decimal(9,2)
	
AS
	
     BEGIN
		UPDATE ACCOUNT SET BALANCE = (SELECT Account.Balance FROM ACCOUNT, CC WHERE CC.NAME = @Destiny ) + @Amount
      END
    
     BEGIN
    	UPDATE CASH SET BALANCE = (SELECT Cash.Balance FROM Cash    WHERE Name = @Origin) - @Amount
     END
2 update statments inside same BEGIN/END or like in the code?
Reply With Quote
  #16  
Old 08-29-2012, 12:30 PM
DrPunk's Avatar
DrPunkSQL Stored Procedure problem DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

Code:
CREATE PROCEDURE dbo.cctableto

	@Origin varchar(100),
	@Destiny varchar(100),
	@Amount Decimal(9,2)
	
AS
	
BEGIN
		UPDATE ACCOUNT SET BALANCE = (SELECT Account.Balance FROM ACCOUNT, CC WHERE CC.NAME = @Destiny ) + @Amount

		UPDATE CASH SET BALANCE = (SELECT Cash.Balance FROM Cash    WHERE Name = @Origin) - @Amount
END
Note that the BEGIN and END here are part of the CREATE PROCEDURE AS, not part of the running SQL.
__________________
There are no computers in heaven!
Reply With Quote
  #17  
Old 08-29-2012, 12:36 PM
FabioRochaPT FabioRochaPT is offline
Newcomer
 
Join Date: Mar 2010
Posts: 22
Default

Quote:
Originally Posted by DrPunk View Post
Code:
CREATE PROCEDURE dbo.cctableto

	@Origin varchar(100),
	@Destiny varchar(100),
	@Amount Decimal(9,2)
	
AS
	
BEGIN
		UPDATE ACCOUNT SET BALANCE = (SELECT Account.Balance FROM ACCOUNT, CC WHERE CC.NAME = @Destiny ) + @Amount

		UPDATE CASH SET BALANCE = (SELECT Cash.Balance FROM Cash    WHERE Name = @Origin) - @Amount
END
Note that the BEGIN and END here are part of the CREATE PROCEDURE AS, not part of the running SQL.
Oke, thank you.

Now i've run into a problem:

Code:
CREATE PROCEDURE dbo.[BankTransfer]

	@Tabelafrom varchar(10),
    @Tabelato varchar(10),
	@Origin varchar(100),
	@Destiny varchar(100),
	@Amount Decimal(9,2)
AS
	
	BEGIN
		UPDATE @tabelafrom	
        SET Account.Balance = (SELECT Balance FROM @Tabelafrom WHERE Name = @Origin ) - @Amount 
        WHERE Name = @Origin
 	
    	UPDATE @Tabelato
        SET BALANCE = (SELECT Balance FROM @Tabelato WHERE Name = @Destiny) + @Amount
        WHERE Name = @Destiny
    END
Tabelafrom means table from meaning its the table sql is gonna update from where the money is gonna be sent. Tableto is gonna receive the money.

But i can only know those tables by seeing what option do they choose on the software. Example:

When user presses "Bank Deposit" i know its "Cash -> Bank Account"

I cant use arguments inside the select statment. Any work around?

Thanks for all the help


Edit: Error i get: Must declare the table variable "@Tabelafrom".
Must declare the table variable "@Tabelafrom".
Must declare the table variable "@Tabelato".
Must declare the table variable "@Tabelato".
Reply With Quote
  #18  
Old 08-29-2012, 12:44 PM
DrPunk's Avatar
DrPunkSQL Stored Procedure problem DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

Code:
UPDATE @TableAFrom SET Balance = Balance - @Amount WHERE Name = @origin
Is the kind of thing you want, I think, but your queries seem very convoluted to me so I might be wrong.
__________________
There are no computers in heaven!
Reply With Quote
  #19  
Old 08-29-2012, 12:54 PM
FabioRochaPT FabioRochaPT is offline
Newcomer
 
Join Date: Mar 2010
Posts: 22
Default

Quote:
Originally Posted by DrPunk View Post
Code:
UPDATE @TableAFrom SET Balance = Balance - @Amount WHERE Name = @origin
Is the kind of thing you want, I think, but your queries seem very convoluted to me so I might be wrong.
You're right, your query works, BUT i still cannot user arguments as tables.

Im ready about dynamic SQL that it should work

Set @query = 'Update blah blah blah'
exec (@query)

Does this seem doable to you?

I just tried and it didn't work, still the same error, "Must declare the table variable "@tablefrom""
Thanks for the tip on the sql query.

Last edited by FabioRochaPT; 08-29-2012 at 01:00 PM.
Reply With Quote
  #20  
Old 08-29-2012, 01:07 PM
DrPunk's Avatar
DrPunkSQL Stored Procedure problem DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

Good point, I didn't even spot that.

Yeah, you'd have to go dynamic for that. You don't necessarily need to declare the @query variable and set it though. You could just have whatever you set @query to in the Exec instead.

Just be aware that you lose the benefits of using stored procedures by using dynamic SQL. Also it's quite a large security risk, what you're doing. It's probably worth reading up on SQL injection.
__________________
There are no computers in heaven!
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
SQL Stored Procedure problem
SQL Stored Procedure problem
SQL Stored Procedure problem SQL Stored Procedure problem
SQL Stored Procedure problem
SQL Stored Procedure problem
SQL Stored Procedure problem SQL Stored Procedure problem SQL Stored Procedure problem SQL Stored Procedure problem SQL Stored Procedure problem SQL Stored Procedure problem SQL Stored Procedure problem
SQL Stored Procedure problem
SQL Stored Procedure problem
 
SQL Stored Procedure problem
SQL Stored Procedure problem
 
-->