Tag: stored-procedures

Repeating “in” statement in T-SQL – can it be put centrally somewhere?

I have a large number of stored procs that all execute similar bits of selection logic: WHERE @CustGroup = ‘X’ AND CUST_TYPE in (‘X1’, ‘X2’, ‘B1’, ‘C1’) OR @CustGroup = ‘Y’ AND CUST_TYPE in (‘Y1’, ‘A1’, ‘B2’, ‘C2’) OR @CustGroup = ‘Z’ AND CUST_TYPE in (‘X2’, ‘Y2’, ‘D1’, ‘D2’) OR @CustGroup = ‘MAIN’ AND CUST_TYPE […]

How to create a list of dates from a daterange without using a CTE

The following link explains how to turn a date range into a list of dates. I used this approach and it works fine but the query is not performing (I used Maxrecursion 0 to unlimit). http://blog.justinstolle.com/sql-turn-a-date-range-into-a-list-of-dates/ Is there any other solution to get this done? (using subquery or declare table?)

How to update two join tables?

I intend to write a procedure in SqlServer that can update the joined tables,in my case i have two tables (HowzeEducation & HowzeDegree) so i have written the following query but it has errors and does not work properly.Here is my code: declare @HowzeEducationId int, @DegreeId int, @FieldName nvarchar(50), @FinishLevelDate date, @Average decimal(4,2), @SchoolName nvarchar(50), […]

Should my view be much slower than my stored proc?

I have a view that I can’t seem to fully tune, so I tried converting it to a proc, moving one of my WHERE conditions that I use when querying into a parameter and using the parameter in my derived tables. View CREATE VIEW myView AS SELECT i.ItemCode , s.StoreID , ISNULL(SUM(s.TotalSales)) AS Sales FROM […]

Arithmetic overflow error converting decimal to data type numeric

ALTER PROCEDURE [dbo].[Update_MCR] @xmlString ntext ,@Message nvarchar(500) output AS BEGIN SET NOCOUNT ON; declare @SL int,@Basic float, @Grad_pay float, @DA float, @HRA float, @MA float, @Ptax float, @Itax float, @pf float, @LIC float, @Month_Of datetime Declare @intDoc1 as int BEGIN TRANSACTION print @xmlString exec sp_xml_preparedocument @intDoc1 OUTPUT, @xmlString declare Generate_Rq CURSOR FOR SELECT SL,Basic, Grad_pay, […]

Aggregating error messages in SQL Server procedures

Stored procedures allow for better abstraction and separation of concerns from an application point of view. At my company, there is a tradition of having DBAs write procedures and do all the DB work. And, it is a model that works, mostly. As a .NET developer, I use Resource files to aggregate all my error/success/validation […]

Get Last six rows from a select query

I have written a stored procedure which gives me the raw data I require for Data Analysis. This SP, at the minute populates a temporary table with the following structure: CREATE TABLE #Analysis ( AgreementID int, DueDate datetime, DateFrom datetime, DateTo datetime, AmountDue decimal(9,2), AmountPaid decimal(9,2), DueTD decimal(9,2), PaidTD decimal (9,2), ArrearsScore int ) AgreementID […]

Using Stored Procedure to Insert parameters from DataTable

I am trying to insert data using a stored procedure that has two tables. This first table is data is through text boxes the second data is through a grid which I stored in the database and passed to be inserted. The problem is when reading datatable and inserting it says there are too many […]

Dropping Constraints and then dropping table in SQL Server

This might sound simple to you, but not for first time when you write I have a query to drop all the constraints in a table using the below query SELECT @sql += N’ ALTER TABLE ‘ + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + ‘.’ + QUOTENAME(OBJECT_NAME(parent_object_id)) + ‘ DROP CONSTRAINT ‘ + QUOTENAME(name) + ‘;’ FROM sys.foreign_keys; EXEC […]

sql multiple updates in stored procedure

What is the best way to handle return values when using multiple update statements in a stored procedure. I am using this stored procedure in a data adapter and am trying to get the update result total. I’m using SQL Server 2008 and .NET 4.5 For example: CREATE PROCEDURE [dbo].[usrsp_MessageConversation_delete] ( @fromMbrID int, @toMbrID int […]

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