Hi! When using prepared statements inside stored procedures, should they be deallocated at the end of the procedure or not, or does it not matter, and why? Some code to explain: CREATE PROCEDURE getCompanyByName (IN name VARCHAR(100)) NOT DETERMINISTIC BEGIN PREPARE gcbnStatement FROM 'SELECT * FROM Companies WHERE name=? LIMIT 1'; ...
how can i call the delete store procedure in linq query ...
I have a table with a number of fields in it. I am trying to create search filter in asp.net so the user can search by one or a combination of fields. So basically I want to create a single stored procedure that takes in 4 params and it will append the param to the WHERE clause if its not null... TableExample has 4 columns, Col1 Col2 Co...
Hi all I'm working on converting a stored procedure from SQL server to Oracle. This stored procedure provides a direct resultset. I mean that if you call the stored procedure in eg Management Studio you directly obtain the resultset. By converting to Oracle I walk against the problem that I in Oracle will not display the resultset ...
When should I be using stored procedures instead of just writing the logic directly in my application? I'd like to reap the benefits of stored procedures, but I'd also like to not have my application logic spread out over the database and the application. Are there any rules of thumb that you can think of in reference to this? ...
I was wondering if there was a way to not execute my subquery if my @ID1 is NULL? CREATE PROCEDURE [dbo].[TestTable_Search] @Col1 int, @Col2 uniqueidentifier, @Col3 datetime, @Col4 datetime, @ID1 varchar(10) AS SET TRANSACTION ISOLATION LEVEL READ COMMITTED SELECT * FROM [dbo].[TestTable] WHERE [Col1] = CO...
Here's the basics of the system we have in place today. We have a SQL 2005/2008 table defined as: CREATE TABLE [dbo].[Profiles] ( [Firm] [char] (4) NULL , [Account] [char] (10) NOT NULL , [UndSym] [char] (24) NOT NULL , [Updated] [timestamp] NOT NULL , [Data] [image] NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] ...
Hi. I'm pretty new to SQL world. Here are my questions: What are the benefits of stored procedured over normal SQL statement in applications? Does stored procedure help eliminates SQL injection? In Microsoft SQL Server it is called stored procedure. How about in Oracle, MySQL, DB2, etc.? Thanks for your explanation. ...
Ok, so my schema is this: Table: Timesheet_Hours Columns: Timesheet_Id (PK, int) Staff_Id (int) BookedHours (int) Posted_Flag (boolean) This is an extremely simplified version of the table, but it will serve for the purposes of this explaination. Assume that a person can only ever have one timesheet record. What I'm trying to do i...
Hi folks, I'm having trouble trying to rank each of our students, by the homegroup they are in. Example fake data. HomeGroup 1. Team RED 2. Team BLUE 3. Team Skeet 4. Team GREEN Students 1. John, Score - 34, Team RED 2. Jill, Score - 87.3, Team RED 3. Fred, Score - 41, Team GREEN 4. Jane, Score 93, Team BLUE ... etc. My output is ...
I am trying to generate random data for my sql server database. Ive created a stored procedure that takes length of string to be generated but the problem is that it wont allow me to call it inside the insert statement. insert into table1 (varchar_data,int_data) value ((sp_GenerateRandomString 4),CAST(RAND() * 10 AS INT) % 6 + 1 ) al...
i want to ensure that all stored procedures are still syntatically valid. (This can happen if someone renames/deletes a table/column). Right now my solution to check the syntax of all stored procedures is to go into Enterprise Manager, select the first stored procedure in the list, and use the procedure: Enter Alt+C Escape Escape Down...
Hi there, I've just started at a new job with sole responsibility for a large(ish) financial application. It's got about 800 stored procedures, 120 SQL server tables and 18 .net projects with approx 1200 code files. And no documentation other than line comments in the code. As I mend bugs and make ad hoc to the data I want to document w...
Let's have: $DB an SQL Server database $DBSP1 an SQL Server database containing stored procedures referencing $DB $DBSP2 is exactly like $DBSP1 $SP is a stored procedure Running $SP on $DBSP1 from C# code takes around 1.5s. Running $SP on $DBSP2 from C# code takes around 0.5s. The C# code is very simple and use SqlClient with defau...
Okay, what I'm trying to do is take the results from a stored procedure and put them into a temp table... Looks like this: DECLARE @Table TABLE( [ID] int, [CostA] real, [CostB] real, [CostC] real ) INSERT INTO @Table EXECUTE [dbo].[CostProcedure] @RootId = 123 @Rate = 20 --THEN: SELECT * FROM @Table -- Gives Me...
Hi, I've been learning Functions and Stored Procedure for quite a while but I don't know why and when exactly I should use functions or stored procedure instead of one another. They look same to me maybe because I am kinda newbie about that. Can some one tell me why ? Thanks in advance. ...
Say I've got stored proc 1, that returns some data. how can I execute that stored proc and set some parameters within another stored proc based on the results of that stored proc. For example: Table: UserInfo (UserID [int], Name [varchar], DateOfBirth [datetime] ) Stored Proc 1: CREATE Procedure StoredProc1 @Name [varchar] AS ...
I have multiple fields both asp:DropDownList's and asp:TextBox's. I also have a number of user roles that change the Visible property of certain controls so the user cannot edit them. All of this data is saved with a stored procedure call on PostBack. The problem is when I send in the parameters and the control was not on the page obviou...
I am trying to create a stored procedure in MYSQL. Below is my code which is giving syntax error. Can anyone please help me. CREATE PROCEDURE productpricing ( OUT pl DECIMAL(8,2), OUT ph DECIMAL(8,2), OUT pa DECIMAL(8,2) ) BEGIN SELECT Min(prod_price) INTO pl FROM products; SELECT Max(prod_price...
Hi, I want to define stored procedure parameters which do not allow null arguments. That @MailItemId int OUTPUT will be not nullable because when I import the stored procedure into the LINQ to SQL designer it says @MailIetmid is ref int? mailItemId. Thanks. ...