Tag: stored-procedures

Using multiple IF statements in stored procedure with different parameters call on Linq C#

Is it possible to use a stored procedure in c# because I’ve tried it on VB.net when I can setup its parameters that only needed let’s say I have a stored procedure like this: ALTER PROC [dbo].[StoreProcedure_Test01] ( @Param01 nvarchar(35) = NULL, @Param02 smallint = NULL, @Param03 char(21) = NULL, @Param04 datetime = NULL, @Type […]

SQL Server : Dynamic Query in Stored Procedure in

I tried to write dynamic query in SQL Server. However I get warning like below, The Selected stored procedure or function returns no columns I have been trying to achieve this however it still does not run. Where did I miss ? ALTER PROCEDURE [dbo].[S_ProjeGetir1] @ID int=0, @AktifMi int, @ProjeFirma int, @Icinde int, @AramaText Varchar(500), […]

SQL Try Catch for specific severity only

Hi I have a long SQL script which runs as a single transaction to bulk import from files. When running the script I am finding that I get some informational messages, some warnings and then some errors that immediately halt the running of the script – however it doesn’t rollback the transaction. To get around […]

SQL Server 2008 Orphaned Stored Procedure?

I am using SQL Server 2008 through SQL Server Management Studio I have never seen this before: Often, when refactoring a database as I have been doing most of my day, I use the following to find references to columns, tables and other procedures in my existing procedures: select * from sys.sql_modules where [definition] like […]

Dapper: Can I query two stored procedures into one object

I have this: public class Item1 { public string Value1; public string Value2; } public class Item2: Item1 { public string Value3; } I want to query two stored procedures, one returns Value1 and Value2, and the other returns Value3. I’m doing it but what I get is a Item1 object and an Item2 object. […]

How to get OUTPUT parameter of stored procedure sp_setapprole in SQLServer

I’m using the following JDBC driver (the one from Microsoft): http://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx. I want to retrieve the OUTPUT parameter (@cookie) of the stored procedure sp_setapprole. I can call the procedure fine like this, but I can’t figure out how to retrieve the OUTPUT parameter. statement = connection.createStatement(); statement.execute(“EXEC sp_setapprole ‘” + applicationRoleName + “‘, ‘” + […]

Get the first message of every conversation

I have one table to store all the messages sent inside of a xmpp service. I’m looking to create a query to get all the conversations and the first message of it (like whatsapp in chat logs). Here is my table. FromPersonId and ToPersonId are ids for people. What I do is, for example I […]

How to test synchronization between C# code and Sql Server stored procedure

In order to interact between SQL Server and C#, I’m using SqlCommand object, Parameters and stored procedure. My problem is that i have no idea, until i’m executing it, if it’s still synchronized with database. For example, if i have the following stored procedure : create procedure psSync ( @Argument VARCHAR(50) ) –do stuff i […]

automatically pivot data in table using sql server

I have store procedure like this: ALTER procedure [dbo].[performance] @startdate nvarchar(100), @enddate nvarchar(100) as begin declare @date1 nvarchar(100)=convert(varchar, @startdate+’00:00:00.000′,120) declare @date2 nvarchar(100)= convert(varchar, @enddate+’23:59:59.000′,120) set NOCOUNT on; select l.LocName,v.Vtype, SUM(convert(numeric(18, 2), DATEDIFF(MI,t.DelDate,t.Paydate))) as TotalDiff, [dbo].[testfunctionstacknew] (CONVERT(decimal(10,1), AVG( CONVERT(NUMERIC(18,2), DATEDIFF(SS,t.Paydate,t.DelDate) ) )) ) as Average from Transaction_tbl t left join VType_tbl v on t.vtid=v.vtid left join […]

Alternatives to SQL Where Condition with OR

What is a more efficient alternative to using the OR condition in the where clause? Update X set x.col1 = y.col1, x.col2 = y.col2, x.col3 = y.col3, x.col4 = y.col4 from x join y on x.ID = y.ID WHERE x.col1 <> y.col1 OR x.col2 <> y.col2 OR x.col3 <> y.col3 OR x.col4 <> y.col4

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