insert a multiple rows and a common value using stored procedures?

I have custom type (Table type) in sql server 2008. CREATE TYPE RequestingDatesType AS TABLE ( LeaveDate datetime, IsHoliday bit ) I have a store procedure CREATE PROCEDURE CreateLeaveRequest @EmployeeId int, @LeaveTypeId int, @LeaveDescription varchar(50) @TableVariable RequestingDatesType READONLY AS DECLARE @LeaveId INT //1st insert command INSERT INTO tblLeaveMaster(EmployeeId,LeaveTypeId,LeaveDescription) VALUES(@EmployeeId,@LeaveTypeId,@LeaveDescription) SET @LeaveId = SCOPE_IDENTITY() //2nd insert […]

query execution performance issues with a NOT IN in where clause

I have a query such SELECT SOME_FILEDS FROM MY_VIEW MY_VIEW is a view retrieving data from other views, it takes 45 minutes to execute a query from MY_VIEW on a production server. By trying to invesetigate the problem i realized that one sub-view is slow. I know that creating many views is not the best […]

Dynamically joining tables that may not exist

What’s a good way to dynamically create tables, then join together all the tables that I have created? Background I’m trying to write a stored procedure that collects data from various parts of the database and presents it for use. For example if I wanted to send invoices to both Alice and Bob, this stored […]

Parsing xml file from url using sql server

I am parsings XML file from url, when I parse first time it take correct response , but when in url xml file changed , sql server parse old response (for example in first time something=5 sql server parse 5, then when in url something = 6 sql server parse 5 again). But I think […]

SQL Server 2012 XML Type Preserve Significant Whitespace

I am trying to store some data in an XML-Typed Column which, when the data is a string, will preserve the exact text and not replace or adjust embedded line-endings specifically CR & LF. So a C# string like “A\r\nB\rC\nD” needs to come back exactly so but it would appear that the XML conversion insists […]

mysql error inner join

SELECT bp.project_id, bp.project_name, bp.project_costing, bp.project_borrower_id, bp.member_userid, bp.project_staus, SUM(pb.payment_amount) as total FROM borrower_project_master as bp INNER JOIN payment_invest_master as pb ON bp.project_borrower_id=pb.payment_borrowerid WHERE ( (SUM(pb.payment_amount)/bp.project_costing)*100 < 100 AND bp.project_staus=’Y’ ) ORDER BY RAND() LIMIT 0,3 this query show the error Invalid use of group function. Can you help me to solve this problem

razor set value of variable created by .query() method

I have used the following code to get one more than the biggest ID in a table using razor @foreach (var top in db.Query(“SELECT MAX(ID)+1 as ID FROM mytable”)) { if (@top.ID == null) {@top.ID = 1; } } if the table is empty, @top.ID returns null. I am trying to set its value to […]

Show average execution time

I have a table in sql which looks like below: ID JOB_NAME START_DATE_TIME END_DATE_TIME 748425 HOURLY_PLAZA_MISSING 2013-10-30 13:15:01.040 2013-10-30 13:15:14.420 748424 HOURLY_AUTOREPL 2013-10-30 13:05:00.950 2013-10-30 13:05:02.390 748423 HOURLY_LANE 2013-10-30 13:00:01.500 2013-10-30 13:00:10.080 748422 HOURLY_EMAIL 2013-10-30 13:00:01.460 2013-10-30 13:02:00.650 748421 HOURLY_EASYPAY 2013-10-30 13:00:01.410 2013-10-30 13:00:04.820 748420 HOURLY_PROCESS 2013-10-30 13:00:01.290 2013-10-30 13:25:11.450 748419 HOURLY_COPY_SUSPENSE 2013-10-30 13:00:01.240 2013-10-30 […]

Table Naming Conventions – Audit or Audits

I like to call my database table names the plural form of the row’s data. For example say each row represented a user then I would call the table name “Users”. I think this best describes the table as it stores all “users” and not a single “user”. I now need to add an audit […]

one sql trigger insert,update,delete for all tables in the database

is it possible to declare one Sql Trigger Insert, Update, Delete for all tables in the database instead of creating a separate trigger for each table? I just want a simple history of what actions have been taken e.g. TABLE A deletes a row ,TABLE B updates a row ,TABLE C Add new row .. […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.