Dynamic SQL - EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL)
The title says it all... What are the real world pros and cons of executing a dynamic SQL command in a stored procedure in SQL Server with EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL) ? ...
sql
best way to generate mass insert statement in c#?
I am needing to insert a bunch of similar records into a table, and was wondering if there was a nice/easy way to do this. I know it would be easy to just build a big string, but I was hoping I could find a more graceful way to do this. ...
Retrieving All records - Inner join
Hi, I'm a bit of a database novice, so pardon my naivety here. I have following sql statement: SELECT DISTINCT dbo.tb_user.familyName, dbo.user_email.email FROM dbo.tb_user INNER JOIN dbo.user_email ON (dbo.tb_user.id = dbo.user_email.userID) This returns records where an email address exists. What I would like to do is retrieve a...
What's the best approach for reading in XML data and building a query to insert the values into a SQL Server DB?
What's the best approach for reading in XML data and building a query to insert the values into a SQL Server DB? Anyone have a best approach for this type of scenario? ...
How to be a better database developer in 12 months
Hi Stackers After reading Bil Simser blog post Being a Better Developer... in 6 months I agree with his headlines which are: • Reading • Writing • Speaking • Community • Learning • Code, Code, Code and more code But I’ve a specific question "How to be a better database developer (specially Sql-Server developer)" and ...
When should I be concerned about transaction size?
I have a feature where we need to merge user records. The user chooses a user to keep and and a user to discard. We have 59 relations to user records in our database so for each one I need to do: UPDATE (Table) set UserNo=(userToKeep) WHERE UserNo=(userToDiscard) and then DELETE the userToDiscard and their user prefs (118). Should ...
How to do a multiple keyword search?
I have 2 tables to search. Searching photos for keywords, title and description. The keywords have been split off into a separate table. My advanced search will allow searching on all 3 but the basic will just be the keyword table. Basic table setup: PHOTO Table PhotoID Name Title Description WORD2PHOTO Table WordID PhotoID Word ...
sql join query to linq syntax
how do i change this to linq to sql? select * from ratesSchedule as rs inner join userdetails as ud on rs.sid = ud.sid and rs.tabletype = 'd' i got this far var results = from rs in db.ratesSchedule join ud in db.userdetails on rs.sid equals ud.sid but i can't figure out how to add the "and rs.tabletype='d'" ...
HTML text not formatting
so I have HTML formatted Sql text stored in my database, I populate the contents of a <p></p> tag with it on page load, but though HTML tags are showing up in the text they are not formatting the text. Is there a step I am missing? ...
Any recommended websites for SQL puzzles?
Is there any website that contains SQL puzzles, tips/tricks etc? Any version of SQL (Oracle, MSSQL etc) will do. Just want to solve some general SQL puzzles and want to learn more about SQL. Thanks in advance! ...
php sql using odbc
Hi Im making a website in php with a microsoft access database through odbc and i have come across a small problem let me show you my codes first. <form method ="POST" action="maxtimestamplog.php"> Longitude <input type="text" name="longitude" /><br/> Latitude <input type ="text" name = "latitude"/> <input name="ok" type="submit" val...
Splitting a string of text from textbox using VB.NET
I want to add a text box on a web page in order for users to add space delimited tags. When the user submits the page how do I take the tags out of the text box and add each as a record in a SQL database? I want to use VB.NET. ...
problem with asp.net gridview
Hello all, I have problem with gridview deleting.I have table name Doctor with Id,Name,Address,Phone.Id is auto generated field.After adding data when i am displaying in gridview then if delete any id from gridview Again then if i add any new details from the form its starting from the new number.I mean if i delete the last id no 5 then ...
Quickest way to prevent SQL returning data older than Given
Hey there I have a pretty complex database with loads of different tables for different things, every thing of which has a timestamp (Y-M-D h:m:s format) Is there a way I can limit my SQL query to just results from a certain timespan, for example a week ago? If needbe I have a function to convert these timestamps into unix as sqlToUn...
Performance hit using ' where id IN [table] ' with only 1 row in [table]?
I have a stored procedure that takes a comma-delimited string of IDs. I split them and put them into a temporary table and pull out records from another table using where id IN [table] Is it ok to use this same procedure when only one id is passed in for the param? I could write a second stored procedure that would do exactly the sameth...
Crossed foreign keys in SQL
Hi, I am trying to have 2 tables with crossed foreign keys, but I am not allowed to reference a table that doesn't exist when I am creating them. Any way of creating tables like this for mysql, something like declare both tables at the same time or delay evaluation of foreign keys? Error is 1005: Can't create table blocks.frm (errno 15...
What exactly does the "Over" Clause in T-Sql do?
I'm starting to mess with ASP.NET MVC and came across the issue of wanting to do some basic Paging. So I used this simple Linq statement (using the Northwind Database) to get 10 items for a specific page: var q = (from p in db.Orders orderby p.OrderDate descending select p).Skip(currentPage * pa...
SQL temp table sharing accross different SQL readers
I am trying to do a many different queries on a result set which has a very large creation time. To get performance gains I wish to use a temp table and just do many queries on this temp table. Seems pretty standard. Yet I am struggling to share this temp table in dynamic sql. As I understand it, each SqlCommand object executes in its o...
How to sort and display mixed lists of alphas and numbers as the users expect?
Our application has a CustomerNumber field. We have hundreds of different people using the system (each has their own login and their own list of CustomerNumbers). An individual user might have at most 100,000 customers. Many have less than 100. Some people only put actual numbers into their customer number fields, while others use ...
Should I use an ENUM for primary and foreign keys?
An associate has created a schema that uses an ENUM() column for the primary key on a lookup table. The table turns a product code "FB" into it's name "Foo Bar". This primary key is then used as a foreign key elsewhere. And at the moment, the FK is also an ENUM(). I think this is not a good idea. This means that to join these two table...