Is this a viable stored procedure?
-
ALTER PROCEDURE dbo.SelectData2 ( @naam1 varchar(30), @naam2 varchar(30), @leeftijd int, @numberfound int OUTPUT ) AS SET NOCOUNT OFF SELECT ID FROM vrienden WHERE Voornaam = @naam1 AND Achternaam = @naam2 SET @numberfound = @@ROWCOUNT IF (@numberfound = 0) BEGIN INSERT INTO vrienden (Voornaam, Achternaam, Leeftijd) VALUES (@naam1, @naam2, @leeftijd) END IF (@numberfound > 0) BEGIN UPDATE vrienden SET Leeftijd = @leeftijd WHERE Voornaam = @naam1 AND Achternaam = @naam2 END RETURNI entered this Stored Procedure in VWD and it accepted it, however I wonder if it is possible to do a SELECT and an INSERT or UPDATE in the same procedure. My question is, would this work? Or should I make two stored procedures one to find if a certain entry is already in the database and a second stored procedure to change the database? Also I am not sure yet how to process an OUTPUT variable. Please some advice. Thanks! Ranger49 -
ALTER PROCEDURE dbo.SelectData2 ( @naam1 varchar(30), @naam2 varchar(30), @leeftijd int, @numberfound int OUTPUT ) AS SET NOCOUNT OFF SELECT ID FROM vrienden WHERE Voornaam = @naam1 AND Achternaam = @naam2 SET @numberfound = @@ROWCOUNT IF (@numberfound = 0) BEGIN INSERT INTO vrienden (Voornaam, Achternaam, Leeftijd) VALUES (@naam1, @naam2, @leeftijd) END IF (@numberfound > 0) BEGIN UPDATE vrienden SET Leeftijd = @leeftijd WHERE Voornaam = @naam1 AND Achternaam = @naam2 END RETURNI entered this Stored Procedure in VWD and it accepted it, however I wonder if it is possible to do a SELECT and an INSERT or UPDATE in the same procedure. My question is, would this work? Or should I make two stored procedures one to find if a certain entry is already in the database and a second stored procedure to change the database? Also I am not sure yet how to process an OUTPUT variable. Please some advice. Thanks! Ranger49A stored procedure can contain as many CRUD operations as you like. However you should consider giving your stored procedures more meaningful names. For instance, your SP is called SelectData2 but it performs select, insert and update operations. Ideally, the name of the stored procedure should indicate its purpose.
Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush
-
A stored procedure can contain as many CRUD operations as you like. However you should consider giving your stored procedures more meaningful names. For instance, your SP is called SelectData2 but it performs select, insert and update operations. Ideally, the name of the stored procedure should indicate its purpose.
Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush
pmarfleet wrote:
A stored procedure can contain as many CRUD operations as you like. However you should consider giving your stored procedures more meaningful names. For instance, your SP is called SelectData2 but it performs select, insert and update operations. Ideally, the name of the stored procedure should indicate its purpose.
You are right, but this was only my second attempt ever, so I called it SelectData2. Would you know of an example where a stored procedure is called with OUTPUT parameter? I figure my attempt failed due to an error in the calling cs file, and that the Stored Procedure itself is fine. I Googled it, and got some examples, but they weren't answering my question. Ranger49
-
pmarfleet wrote:
A stored procedure can contain as many CRUD operations as you like. However you should consider giving your stored procedures more meaningful names. For instance, your SP is called SelectData2 but it performs select, insert and update operations. Ideally, the name of the stored procedure should indicate its purpose.
You are right, but this was only my second attempt ever, so I called it SelectData2. Would you know of an example where a stored procedure is called with OUTPUT parameter? I figure my attempt failed due to an error in the calling cs file, and that the Stored Procedure itself is fine. I Googled it, and got some examples, but they weren't answering my question. Ranger49
When you create the Sql parameter for the output parameter, you set its direction to Output or InputOutput. When you say it failed, what do you mean? Did you get an exception or did it not behave as you would expect?
Deja View - the feeling that you've seen this post before.