Good afternoon everyone, I am having an issue with a stored procedure inserting an incorrect value. Below is a summarization of my stored procedure ... set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go CREATE PROCEDURE [dbo].[InsertDifferential] @differential int = null AS BEGIN TRY BEGIN TRANSACTION UPDATE Dif...
I have a stored procedure that returns a valueI call it from other stored procedures that need to retrieve this value. The calling stored procedure is inside a transaction, the stored procedure that returns the value (and actually creates the value and stores it in a table that no other proc touches) is not inside its own transaction, bu...
Are the following select statements SQL92 compliant? SELECT table1.id, table2.id,* FROM table1, table2 WHERE table1.id = table2.id SELECT table1.Num, table2.id,* FROM table1, table2 WHERE table1.Num = table2.id ...
I have the following function: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[IP4toBIGINT]( @ip4 varchar(15) ) RETURNS bigint WITH SCHEMABINDING AS BEGIN -- oc3 oc2 oc1 oc0 -- 255.255.255.255 -- Declared as BIGINTs to avoid overflows when multiplying later on DECLARE @oct0 bigint, @oct1 b...
I want to write a code that transfers data from on server to my SQL Server. Before I put the data in, I want to delete the current data. Then put the data from one to the other. How do I do that. This is snippets from the code I have so far. string SQL = ConfigurationManager.ConnectionStrings["SQLServer"].ToString(); string OLD = Co...
1I'm building a little forum for practice. I see that forums like phpBB store the thread text in a separate table. Why? Why not store it all in the same table? Some thing like:thread_id, thread_date, thread_text, thread_author Why is it done this way? How would you do it? ...
it is very easy to use the following SQL to get value for a specific primary key: ID from a specific table: myTale: DECLARE @v_maxID bigint; SELECT @v_maxID = MAX(ID) FROM myTable; What I need is a generic SQL codes to get the max value for a key from a table, where both key and table are specified as varchar(max) types as parameters:...
Let's say I have this table: id colorName 1 red 2 blue 3 red 4 blue How to select one representative of each color? Result: 1 red 2 blue Thanks. ...
Hi everyone, this is my first time using this site. OK, i need to use a cursor to call a stored procedure that has 2 parameters that i need to pass into from Customers table. Here is what i mean; My goal is to pass all the CustomerID and CustomerName from Customers table into my stored procedure called AddCustomers which has 2 paramet...
Hi, I have to create a entire application in an Oracle RDBMS with SQL. The Design includes to complete full entity-relationship and also design the relevant Schema with object creations.Important is-When creating schema and objects..we should fully apply normalisation. Also, writing all the relevant SQLs in each and every step in this p...
Consider following schema: Customers: Col | Type | -------------------| id | INTEGER | name | VARCHAR | Orders: Col | Type | ----------------------| id | INTEGER | customer_id | INTEGER | date | DATE | Items Col | Type | ----------------------| id | INTEGER | order_i...
I remember seeing online a (sql?) query designer tool that looked like the "point and click GUI" that you get in MSFT access (and other similar apps). It allowed the user to do simple joins and where clauses and select clauses simply by toggling checkboxes, dragging table icons into view and connecting joins by drawing lines. This lin...
I have following query: Select diary_id, (select count(*) from `comments` as c where c.d_id = d.diary_id) as diary_comments From `diaries` as d It takes very many time (near 0.119415 in my case). How I could solve this problem? I see only one way: doing additional query for comment number for each row from my main que...
I am in charge of a database. It has around 126 sprocs, some 20 views, some UDFs. There are some tables that saves fixed configuration data for our various applications. I have been using a one-big text file that contained IF EXIST ...DELETE GO CREATE PROCEDURE... for all the sprocs, udfs, views and all the insert/updates for the confi...
Can anybody help me to optimize this code? At present it takes 17 seconds. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO --SpResumeSearch NULL,null,null,null,null,null,null,null,null,null,null,NULL,null,null,null,null,null,null,null,0,10,NULL ALTER PROCEDURE [dbo].[SpResumeSearch] @Keyword varchar(50) = NULL, @JobCategoryId in...
I am having trouble with a stored proc (SQL 2005). I have a table called tbrm_Tags with two columns, TagID and TagName. I want to pass a TagName value to the stored proc and then I want to : 1) Check if the Tagname exists and if it does return the TagID 2) If the Tagname does not exist I want it to insert into the table and return th...
Hi, I've asked few people why using xml as a parameter in stored procedure doesn't work and everyone said , that's just the way it is. I can't belive that. command.Parameters.Add("@xmldoc", SqlDbType.Xml); That's where compiler returns error and I can't use NVarChar beacouse it's limiteed to 4k sings. XML would be perfect as it can b...
Hi, How can I group result of a LINQ to SQL query by hours considering that the column type is DateTime? ...
I am trying to determine what the best way is to find variations of a first name in a database. For example, I search for Bill Smith. I would like it return "Bill Smith", obviously, but I would also like it to return "William Smith", or "Billy Smith", or even "Willy Smith". My initial thought was to build a first name hierarchy, but I...
I am inserting records through a query similar to this one: insert into tbl_xyz select field1 from tbl_abc Now I would like to retreive the newly generated IDENTITY Values of the inserted records. How do I do this with minimum amount of locking and maximum reliability? ...