I hope somebody can help me here, I have a table that has the name of the Stored Procedure that I would like to run, it also has a parameter that I would like to pass through into the Stored procedure. declare @RowCnt int declare @MaxRows int declare @ExecSql nvarchar(255) select @RowCnt = 1 These next two rows are specific to sourc...
Hi Assuming Asp.Net app calls procedure dbo.ApprovePost and @@ERROR contains a value greater than 0, then the following code will be executed: CREATE PROCEDURE dbo.ApprovePost … IF @@ERROR > 0 BEGIN RAISERROR(‘Approval of post failed’, 16, 1) ROLLBACK TRANSACTION ApprovePost RETURN 99 END COMMIT TRANSACT...
Hi all, I need some opinions. I'm going to develop a POS and inventory software for a friend. This is a one man small scale project so I want to make the architecture as simple as possible. I'm using Winform to develop the GUI (web interface doesn't make sense for POS software). For the database, I am using Postgresql. The program wil...
There are approx 500 sprocs in my SQLSERVER 2000 database; each sproc has a typical Grant Execute statement similar to the following. GRANT EXECUTE ON [dbo].[sproc_name] TO [role1], [role2], [role3], [role4], etc... How to view the names of the sprocs which have grant to a particular role and only that particular role exclusively...
I have a stored procedure that returns a collection of my entity class objects. Since I want my navigation properties populated as well I'm using EF Extensions and I've written my own Materializer class. But. My entity class has a navigation property of Type that points to a different entity. Stored procedure of course returns an ID fro...
I am creating a stored proc that selects a value from a table and uses it in another procedure. If the first value that is searched doesn’t exist I need it to use a default value. I’m new to stored procs so I’m not sure of the best practices. Here is the first select statement which may or may not return a value. If it doesn’t return a ...
I'm trying to understand MySQL Stored Procedures, I want to check if a users login credentials are valid and if so, update the users online status: -- DROP PROCEDURE IF EXISTS checkUser; DELIMITER // CREATE PROCEDURE checkUser(IN in_email VARCHAR(80), IN in_password VARCHAR(50)) BEGIN SELECT id, name FROM users WHERE email = in_emai...
I am often having a very hard time to create a stored procedure in mysql. Syntax which should really be fine just get not parsed within a stored procedure. Here is a simplified version that still doesn't get parsed. I am not able to turn this code into something that can be parsed. The update..set clause gives problems UPDATE I've sim...
Group, I am trying to create a stored procedure using one variable @Customer. What I want to do is put something in my WHERE clause that says if it is a number search the CustomerID field where the number entered is LIKE CustomerID... If a char is entered search the CustomerName field where the text entered is LIKE CustomerName. Below ...
I was just trying this knowing that my select would return one row. Is something like this possible or will I need a temporary variable? lets say my stored procedure took one parameter: exec dbo.GetUserData @UserName = UserName from @MyTempTable where UserId= @UserId Or what if the parameter expected was XML? Is there a way I can do ...
I have a stored procedure which returns an Integer as well as an Out Parameter which is of type VARCHAR. I am using Spring 2.5.6 and unable to find a way to read the return value as well as Out Parameter at the same time. SimpleJdbcCall.executeFunction(..) have a facility to read the stored procedure return value but no facility for Ou...
For typical 3-tiered application, I have seen that in many cases they use a lot of complex stored procedures in the database. I cannot quite get the benefit of this approach. In my personal understanding, there are following disadvantages on this approach: Transactions become coarse. Business logic goes into database. Lots of computat...
Over the last couple of days I have tried to write an Stored procedure in MySQL and I have some truble getting it to work. Hope someone here can give me some input :) The example I post is for asp.Net Membership provider to create a new user. I expect to send email and password to the DB and get an int return to verify that the userdeat...
i want to write a generic stored procedure in oracle .For example i want to take table name as input and then do maipulations on it. I want to learn some sample generic codes and the basica of writing generic stored procedures in oracle. Can any one provie code snippets/ links to websites or other material for this? ...
Hi, I would like to call this stored procedure with jdbc: sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" JDBC thinks the ? is a placeholder for an argument. In fact it is used by the SP to put in the table name. How can I call the stored procedure? I tried this: CallableStatement call = jdbcConnection.prepareCall("call sp_m...
How to find the maximum of two explicit values in MySQL? Something like MAXIMUM(1, @foo). There are group functions like MAX, MIN, AVG, etc that take column name as an argument and work with result sets. Is it possible to convert two explicit values to a result set and use those functions? Some other ways? P.S.: I need a max function f...
Below cursor declaration works with 5.0 but not with 5.1. DECLARE CUR_GBLVAR CURSOR FOR SHOW GLOBAL VARIABLES; ...
If you have a stored procedure that deletes record in a table, do you have to put a return statement and why? I have always never put a return statement, but I just saw a snippet that has a return statement. Sample: DELETE FROM TableName WHERE TableId = @Id RETURN ...
I have a table with columns - id, x1, x2, x3,x4 if i have values 1,y,y,y,y 2,y,null,n,null 3,y,null,null,null then say, i want to calculate (1)/(number of y's) and display rows as 1,.25,.25,.25,.25 2,1,0,0,0 3,1,0,0,0 Is it possible to do this using sql query? ...
In my apps I typically use the Enterprise Library Data Block to simplify my database interactions. C# code such as: public static IDataReader AdminNavigation_Insert(int iGroupId, string sText, string sRelativeUrl, int iSortOrder) { return DatabaseFactory.CreateDatabase("database").ExecuteReader( "cms_uspAdminNavigation_Inser...