A few months back, I started using a CRUD script generator for SQL Server. The default insert statement that this generator produces, SELECTs the inserted row at the end of the stored procedure. It does the same for the UPDATE too. The previous way (and the only other way I have seen online) is to just return the newly inserted Id back ...
We have some problems with the ANSI_NULLS setting and computed columns and we have a ton of stored procedures that have SET ANSI_NULLS OFF We want to change them all to SET ANSI_NULLS ON Is there an easy way to do that or must I extract all the SPs to a script, change it and run it again to drop and recreate all the SPa ...
Ok, here is the thing, I have a stored proc which returns three sets of data results, i can use the results in code behind to databind a repeater or whatever, something like so... Using cmd As New SqlCommand("TICKET_TECH_S", oConn) cmd.CommandType = CommandType.StoredProcedure Using da As New SqlDataAdap...
How do I use the value returned by a query in the same procedure? (sorry for noob-ism) I'm trying to get a number of users with an initial beginning with whatever as a list - i.e. A(4) B(2) c(5) etc SELECT DISTINCT LEFT(last_name, 1) AS initial FROM users ORDER BY initial How do I then go on to ask: select COUNT(*) as NumTea...
how to assign the integer value to nvarchar or varchar datatype in stored procedure DECLARE @SQLString nvarchar(max) SET @SQLString = N'declare @Identifier int; SELECT COUNT(*) FROM ' + @batch+' where Identifier = @Identifier' i need to check whether the @SQLString is 0 or not. i.e i want to check -----> if(@SQL...
Hi, I am using Spring's JdbcTemplate and StoredProcedure classes. I am having trouble getting the stored procedure class to work for me. I have a stored procedure on an oracle database. Its signature is CREATE OR REPLACE PROCEDURE PRC_GET_USERS_BY_SECTION (user_cursor OUT Pkg_Types.cursor_type , section_option_in IN Varchar2 , se...
i have an SP that executes 1 SP at the moment EXEC mpSPAccess.PostIdSelect @PostDate = @TodaysDate The SP does something like this (very simplyfied :)) SELECT id FROM Post WHERE DateCreated = @PostDate After this SP is Executed i want to use the id i got from PostIdSelect as an parameter for more SPs Like this: EXEC mpSPAccess.Ge...
The company I work for is using MacolaES for an ERP system. The SQL Server database is structured such that when things are no longer considered active, they are moved from a set of "active" tables to a set of "history" tables. This helps to keep the "active" tables small enough that queries return quickly. On the flip side, the history ...
Hi does any one know how to check if a given DB object (Table/View/SP/Function) is used inside Oracle. For example to check if the table "A" is used in any SP/Function or View definitions. I am trying to cleanup unused objects in the database. I tried the query select * from all_source WHERE TEXT like '%A%' (A is the table name). Do y...
hi , I'm using an existing stored procedure in my C code. The stored procedure in question has been compiled and is proven to work without any errors. However, when I use the same in my C code, its failing with the above error. The Store procedure definition looks like : CREATE OR REPLACE FUNCTION SP( srq_id integer , ...
I'm using PHP + Oracle and was wondering if there are any recommendations on where to commit my transactions. I call stored procedures to do all my inserts/updates/deletes, and currently am committing at the end of my stored procedures. I was wondering: Is there any difference between calling commit/rollback in my stored procedure vs...
Hi, I have a stored procedure that returns a scalar value, I mapped it in the entity model, created an imported function, however it wasn't add into the context class. So I was not able to found how to call it from C# code. Thanks in advance, Carlos Loth. ...
i am trying to grant execute privs in stored proc in more than one database. The problem is this stored proc might not be in some of the databases. So how can i write a script which checks if stored proc exists in database and if does then provide execute privs for user? ...
ORA-20118 is a custom exception from the stored procedure. The stored procedure runs just fine from PL-SQL developer, so the problem is in Spring. What I need to do is to get Spring to rollback the SP when it gets the ORA-20118 exception back from the SP. How do I do that? or maybe just get spring to correctly handle the 20118 code c...
I cannot store the date data type variables using stored procedure. My code is: ALTER PROCEDURE [dbo].[Access1Register] -- Add the parameters for the stored procedure here @MobileNumber int, @CitizenName varchar(50), @Dob char(8), @VerificationCode int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- inter...
I know my title isn't exactly worded well, so let me clarify. I'm using SQL Server 2005 Express. I have a table that basically stores a "template," if you will. Using a car as an example, the fields would be something like: TemplateID Color Make Model Now, I have another table that represents an "instance" of the template. It cont...
Hi: We have the ability to execute stored procs from the middle-tier. Basically, in a database table called "SQLJobStep" we have -- among other things -- a varchar(300) column called "StoredProcedure" which holds something like this: usp_SendReminderEmails @Debug=0, @LoginID Via the middle-tier, a user clicks on a link to run the pro...
I have just started a new web application with asp.net mvc and sql server 2005... Thus far in my webform applications i used ADO.NET... I want the same in my asp.net MVC apllication... I have a database in sql server 2005 with all the stored procedures... Now i want to use those stored procedures in my MVC application... I dont use any...
Hi, I am curious whether I can drop multiple procedures by simple using "%"? Like: DROP constantName% When I use DROP in the Management studio, it looks like that: /****** Object: StoredProcedure [dbo].[x] Script Date: 02/02/2010 09:36:25 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[x]') AND t...
I have been using inline query statements within my model of my application... As of now many people said that stored procedures would be a good practise... I have the following question How to use MySql stored procedures in PHP Codeigniter? What are the advantages of using Mysql stored procedures? ...