Tag: stored-procedures

Stored procedure with optional parameters

I have a C# application that sends the parameters to the following stored procedure: @Lot nchar (112)= NULL, @Oper nchar (112)= NULL SELECT [Lot].Lot, lot.Oper FROM DB where (@Lot is null or ( Lot= @Lot)) and (@Oper is null or ( Oper= @Oper)) I cannot get any results if I leave fields blank in my […]

Dynamic table pivot on store procedure

I’m doing a web interface to bind objects to users. I use SQL Server 2008 R2. It is object of a different kind. I have a desire to display a table dynamically in SQL to avoid doing it in PHP (which would be cumbersome and time to be displayed). I explain a bit of my […]

SQL Server: Intermittent Invalid object name 'dbo.computed_view1'. Could not use view or function 'dbo.view2' because of binding errors

I’m using SQL Server 2008 R2. I have a view; let’s call it view1. This view is complex and slow. It cannot be made into an indexed view because it uses left joins and various other trickery. As such, we created a stored procedure which basically: obtains an exclusive lock selects * into computed_view1_tmp from […]

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

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