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 […]

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 […]

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 […]

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 […]

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 […]

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

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 […]

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 […]

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 […]

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 .. […]