sql

if else Statement in stored procedure?

I have this stored procedure which get data from excell and then update status: ALTER PROCEDURE [dbo].[sp_AllocateSerial] @Limit int, @Part varchar (50), @Status varchar(50) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here ...

HQL version of a SQL join with group by

I have two tables, Band and Votes. Band has a name and an id, and Votes has a total_votes column and a foreign key called band_id pointing to band.id. I have lots of votes, saved at various dates. What I want to do is find the maximum value of the total_votes column for each band. The following SQL query works: select b.name,max(v.t...

List search results in a combobox.

I want to change our search as it is a bit hacky now. Currently it works like this: User enter a text in a textbox, for example Volvo and start searching. This search with SQL all posts containing Volvo. The result is a list with 2 columns, BoldID and DisplayValue. BoldID is just an unique number to identify the object in datab...

how to insert data into db as a serialized object

my basic question is how to insert data into DB as a serialized object and how to extract and use it then ... any suggestion !!? e.g : {id:1, userId:1, type:PHOTO, time:2008-10-15 12:00:00, data:{photoId:2089, photoName:A trip to the beach}} as you see how could I insert data into column Data and then to use it !? another question i...

Create view based on hierarchy / used of CTE

Hi everyone, Is it possible to create a view based on hierarchy / cte ? I've seen an example on how to generate a resultset based on the link recursive query. I've attached the ddl and the statement. Thank you, Elmer CREATE TABLE [dbo].[XHR_PERSON]( [PERSON_ID] [bigint] NOT NULL, [LAST_NAME] [varchar](100) NOT NULL, [FIRST_NAME]...

How to use the result of a complicated query in self-join?

I'm writing a stored procedure on SQL Server 2000. I've written a complicated select statement that takes 18 seconds to run. The question is, how can I self-join the result correctly and efficiently? Is it possible to manage it in a single select statement without repeating the big query statement that I currently have? Or should I sa...

SQL CASE and local varaibles

Hi, I am studying SQL, I would like to know how can I use local variables in CASE statements in SQL. Thanks guys for your help. This script gives me error: DECLARE @Test int; DECLARE @Result char(10); SET @Test = 10; CASE @Test WHEN @Test = 10 THEN SET @Result='OK test' END Print @Result; I USE MS...

Should stored procedures check for potential issues when executing?

Let me explain this question a bit :) I'm writing a bunch of stored procedures for a new product. They will only ever be called by the c# application, written by the developers who are following the same tech spec I've been given. I cant go into the real tech spec, so I'll give an close enough example: In the tech spec, we're having to...

Using LIKE in CASE statements

Hi, how to use LIKE in CASE statement, i have got this example script but does not work. I use MS SQL 2008 DECLARE @DataBaseName sysname, @Message char(255) SET @DataBaseName = 'DBa'; SET @Message = CASE @DataBaseName WHEN LIKE 'DBa' THEN 'Valid name' ELSE 'INVALID name' END Print @Message; ...

pratical example of Postgres regex substring or regexp_matches

I have been trying to figure the following for a couple days. Please HELP PostgreSQL table : locations Id State -------------------- 1 New York 2 Texas input = 'Greetings from Texas to all Cowboys' output: row containing Texas SELECT id, state FROM locations WHERE state ~* substring(input from state) ...

How to get the parameter declarations as well as the SQL calls of NHibernate?

Hi, When I am working with NHibernate (I am a beginner of it btw) I am able to obtain the SQL calls to Db in my output. But I am not able to make them work after I copy and paste into Management Studio. Because it is missing the parameter declarations. I get something like: SELECT this_.PK_Product as PK1_1_0_, this_.ProductCode as P...

What is the best practice to work in unmanaged programming pattern

I have a query UPDATE dbo.M_Room SET //do something WHERE PK_RoomId= @RoomId AND IsActive=1 AND FK_DepartmentId =@DepartmentId Now suppose PK_RoomId is my Pk of M_Room and is autoincremented field. So according to this I could have used WHERE PK_RoomId= @RoomId rather than WHERE PK_RoomId= @RoomId AND IsActive=1 AND FK_Departme...

remove time from sql

Hi Guys, Please can any ove suggest me how i can remove time part from sql dates. I have already read 100s of articles and most of them remove the time but leaves 00:00:00 behine which i don't want also i know that i can remove these ZERO's by doing a convert to varchar but unfortunately i cannot change the type of the date column it ha...

How to calculate the local datetime from a utc datetime in tsql (sql 2005)?

Hi, i want to loop over a period of time in tsql, and print the utc datetimes and our local variant. We live in UTC +1, so i could easily add 1 hour, but in the summertime we live in UTC +2. In C# i can create a datetime and use a method to ask for the UTC variant and vice versa. Till now i have this: declare @counter int declare @l...

Question: how to use the current time in a where clause

Hello all, there was a question coming up to my mind. Is there any possibility to use the current timestamp instead of a selected date in the Where Clause? SELECT this, that FROM here WHERE start>='2010-07-01' I thought it would be sth. like: start='now()' or curdate() or curtime(). Anything I found was that they're used in the Selec...

How can I search the string X times in the DB? (MYSQL + PHP)

Hey, first I use SQL + PHP the type of DB is MYSQL. I have a column with the many strings, but I want to search the string 08/08/10 if it exists 5 times for example in the column, how do I do it? ** If I will do: SELECT * FROM x WHERE y LIKE '%08/08/10%' Maybe it exists, but I don't know if 5 times.. Thank you very much! ...

What type of Transaction IsolationLevel should be used to ignore inserts but lock the selected row?

I have a process that starts a transaction, inserts a record into Table1, and then calls a long running web service (up to 30 seconds). If the web service call fails then the insert is rolled back (which is what we want). Here is an example of the insert (it is actually multiple inserts into multiple tables but I am simplifying for thi...

SQL add tablename as field

I have seen this done before but i can't remember where i saw it. What i need to do is have the table name as a field in a query Say i have a table called users, how would i add the table name 'users' as a column like SELECT username, password, **table** as mytable FROM users ...

Convert C# classes (class library) to SQL DDL (tables)

I have to convert a set of C# classes (class library) to SQL tables to be used by SQL Server, so that the data can be stored in a database and manipulated through the database. The problem is that the number of these classes is big (more than 1000 classes), and it would take a long time to setup manually such a database schema (tables,...

Problem with SSIS 2005 flat file source - partial row which isn't actually a partial row

I'm currently working on an SSIS package to load mainframe logs from multiple server/file sources into a database. As it stands at the moment I'm using a foreach loop container to loop through a recordset containing filenames and load the files using a Data Flow task from a Flat File Source and File connection to an OLE DB Destination t...