Tag: stored-procedures

Insert into temp table from exec call stored procedure

I have a stored procedure where I insert a table into a temporary table and then I read that temp table row by row using a cursor: USE [TEST_DB] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[returnValidationFailures] AS BEGIN SET NOCOUNT ON; SELECT TOP 0 * INTO TempTbl FROM USER DECLARE […]

UniDac doesnt find stored procedure schema

I have used UniDac VCL components for Delphi for a year now but recently started quite a big project with SQL Server 2012 and I want to separate the tables and stored procedures in different schemes my whole logic is in stored procedures so no query’s in the program only the stored procedure names and […]

Error in store procedure execution

When I am trying t execute a stored procedure, It is showing this error Msg 8152, Level 16, State 14, Procedure MA01003_SUM1, Line 12 String or binary data would be truncated. The statement has been terminated. Following is the stored procedure ALTER procedure [dbo].[MA01003_SUM1] as Begin declare @desc as varchar(50) set @desc = dbo.sdescription(1,0,3) declare […]

SQL – How to determine why a stored procedures sometimes takes many times longer than usual

We have a recently implemented data warehouse and reporting solution. Generally, the process of pulling the information together takes around 8 minutes. About once per day, one particular stored procedure takes much longer (~2 hours) than expected. There is no rhyme or reason to the time of day, and usually the next run will proceed […]

Execute a stored procedure and get single out parameter in Hibernate

Suppose I have a simple MS SQL Server procedure that takes 2 int arguments and returns the sum CREATE PROC add_them @param1 int, @param2 int, @result int OUTPUT AS BEGIN SET @result = @param1 + @param2; END Can I call this procedure from Hibernate (version 4.3.1)? So far I used a session.createSQLQuery(“exec add_them :param1, :param2”) […]

Unable to delete right to left language columns using stored procedure

I’m using stored procedure to delete a row from MSSQL database based on a column that uses nvarchar(100) and Persian language. when i want to insert into this column, i use the word N before the record to be able to perform the insert operation : insert into materialPrice values( N’persian word’,1000,100,0,0,0,0) the problem is […]

SQL Transaction Handling in stored procedure

I have written a stored procedure to check the how transaction working in stored procedure. Is this correct? How can I check this is correct or not? What I want to do is if second table data not deleted ; both the table data should not be delete. CREATE PROCEDURE DeleteDepartment ( @DepartmentID int ) […]

Using Log4Net and the AdoNetAppender to write an XMLLayout parameter to a DbType XML field

Pretty straightforward question. I have a table on a SQL Server Database that has an field with a DbType of XML. I would like to write to that field using the format of Log4Net’s XML layout WITHOUT writing a custom appender; using the in-the-box AdoNetAppender. Below is my current code, however, when it attempts to […]

After adding a function to a stored procedure, the execution time is way too long

I have a stored procedure like this. ALTER procedure [dbo].[IBS_fetchreqVehicleinPodium] @locid integer=null as begin SET NOCOUNT ON SELECT t.TBarcode, t.PlateNo, t.DelEcode FROM transaction_tbl t WHERE [status] IN (3,4) AND locid = @locid AND dtime >= getdate()-7 OR ([status] = 5 AND DATEDIFF(n, CAST(DelDate AS DATETIME), GETDATE()) <= 3 AND locid = @locid AND dtime >= […]

t-sql stored procedure pass FIELD name as a parameter

Stored procedure works fine with SELECT Value1 from Table1…, but I want to change column name on the fly… like Value1 , Value2 so I want to pass (@Field) as a parameter. What am I doing wrong guys? Thanks in advance. Table1: ID | Location | Value1 | Value2 ————————————— 1 L01 123.00 234.00 2 […]

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