I partly agree. I do not quite get your "SP's are not faster" point. You make Command Objects with query strings? You pass to them Parameter Objects so you have a parameterized query? If you go at it that way I can imagine there is no speed penalty, since that approach simply executes the query...

stored-procedure

database

Why use functions instead of stored procedure in sql

19 Aug 2013 by Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Refer - 1. Function vs. Stored Procedure in SQL Server[^].Quote:The difference between SP and UDF is listed below:2. SQL SERVER – Question to You – When to use Function and When to use Stored Procedure[^].3. Differences Between Procedures and Functions[^].4. Difference Between...

SQL

stored-procedure

Procedure or function sp_creditnotegstinsert has too many arguments specified.

24 Apr 2016 by OriginalGriff

Well...if you...

SQL

stored-procedure

How to use loop in SQL server to get values through called no. And country

2 May 2016 by CHill60

You have a problem with your attempt - the called Number must be a varchar column so you can use SUBSTRING but you haven't surrounded the number with quotes.I've assumed the following data schemas:create table [Call Details]( OrigParty1 bigint, calledNo varchar(50), DateOfCall...

C#3.5

SQL

stored-procedure

How to optimize following type of query?

8 Jun 2020 by CHill60

Start here.. Top 10 steps to optimize data access in SQL Server: Part I (use indexing)[^] SQL Server Performance Tips and Guidelines[^] SQL Query Optimization FAQ Part 1 (With video explanation)[^] Improving the performance of queries using SQL...

SQL

stored-procedure

SQL stored procedure

12 Mar 2013 by Yvan Rodrigues

It's uncommon but it's possible.The magic beans you are looking for is called a prepared statement[^].You could query your table-table for a table name and assign that to a variable and then construct the SQL for the prepared statement on-the-fly.See The Curse and Blessing of Dynamic...

SQL

stored-procedure

Fire Insert and Update Stored procedure from One Button Click

30 Jul 2013 by Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

As far as I understand, you are populating the TextBox when LinkButton is clicked. So, you must be binding the Primary Key value of each row in GridView.Else bind that by using the DataKey property of GridView.Now, try to get the DataKey of the Row, when LinkButton of that Row is...

ASP.NET

stored-procedure

event-handler

multiple value insert ,delete and update in store procedure & trigger

28 Oct 2014 by PhilLenoir

It is all there in the T-SQL documentation:Update (http://msdn.microsoft.com/en-us/library/ms177523.aspx[^]):An update query is always effectively for multiple rows for a set of rows defined by the WHERE clause (or FROM clause join). UPDATE table set field = value will update every row in...

SQL-Server-2008R2

stored-procedure

DBMS

This is the error I'm getting- "Procedure or function 'submitrecord' expects parameter '@empcode', which was not supplied."

6 Jan 2015 by OriginalGriff

Um...SqlCommand cmd = new SqlCommand();cmd.Parameters.Add("@empcode", SqlDbType.VarChar).Value = Convert.ToString(TextBox1.Text);cmd.Parameters.Add("@empname", SqlDbType.VarChar).Value = TextBox2.Text;cmd = new SqlCommand("submitrecord",conn);cmd.CommandType =...

stored-procedure

Sql Exception was unhandled by user code

26 Feb 2015 by ZurdoDev

Right after you add all the parameters you then clear them out.Remove this line:cmd2.Parameters.Clear();

stored-procedure

execution of a stored procedure using multiple values for a single parameter

13 Nov 2015 by Maciej Los

You can pass comma separated list of parameters as single parameter. Then - inside a sp - you have to split values into several rows. How? Please, see: Using comma separated value parameter strings in SQL IN clauses[^]But i'd suggest to re-think your programme and create connection between...

SQL

stored-procedure

Subquery is not working

19 Jul 2016 by Asad Ali Mirza

You are missing the IN clause.select * from PLC_ClientContactPerson where PLC_ClientContactPersonID IN (select PLC_ClientCotnactPersonId from PLC_DealCOntact where PLC_DealID=@DealID);You can also useselect * from PLC_ClientContactPerson a, (select...

SQL

SQL-Server

stored-procedure

How to get return value from SQL stored procedure?

8 Dec 2016 by CHill60

Solution 1 shows you how to get an output parameter from a Stored Procedure. But you already know how to do this as you asked a very similar question earlier that dealt with output parameters.To get the return value you need still to add an appropriate parameter to the collection for cmd but...

C#3.5

SQL

stored-procedure

How to write loop below with best practice performance ?

12 Feb 2020 by MadMyche

The first thing that stood out to me in this was a poor choice in a variable declaration. I see NO valid reason for this as @NewID will always be the same size; you would be better off with using NCHAR(37)DECLARE @NewID nVARCHAR(4000) =newID()...

SQL

stored-procedure

How to export the stored procedure statement into a sql file?

9 Jun 2011 by prot0col

This command might help,select ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINESEvery row is a stored procedure

SQL-Server

stored-procedure

How to send multiple Id to the SQL Stored procedure

10 Jan 2012 by Ravi Sharma 2

Dear Developer,I want to send multiple Id into storedprocedure parameter using IN Query.CREATE procedure [dbo].[sp_proc_select] ( @bankId int)as begin select * from tbl_bankmaster where bank_id in (@bankId)endif I execute this procedure with sending multiple...

How to send multiple Id to the SQL Stored procedure

10 Jan 2012 by Om Prakash Pant

There are multiple ways of doing the same. one option is to use the dynamic sql query. Example:CREATE procedure [dbo].[sp_proc_select](@bankId varchar(4096))asbegin exec('select * from tbl_bankmaster where bank_id in (' + @bankId + ')')endexec sp_proc_select...

How to get Identity value whith help of scope_identity

18 Jan 2012 by RDBurmon

scope_identity...

SQL

stored-procedure

Stored Procedure is executing successfully from backend but not from frontend

7 May 2012 by Dhanamanikandan

You can set this value in web.config. For example, to change the timeout for one specific page: See...

sql stored procedure, subtract or add to value

19 May 2012 by OriginalGriff

Yes:DECLARE @OrigQuantity INTSELECT TOP 1 @OrigQuantity = curQuantity FROM Products WHERE Id=3UPDATE Products SET curQuantity = @OrigQuantity - 2 WHERE Id=3SELECT @OrigQuantity

SQL

stored-procedure

How to get return value from an insert stored procedure in code behind

1 Sep 2012 by Wendelius

Have a look at this blog. It has a very thorough example of returning a value from a stored procedure: http://blogs.msdn.com/b/smartclientdata/archive/2006/08/09/693113.aspx[^]

SQL

stored-procedure

how to check each row using stored procedure

15 Nov 2012 by Nandakishore G N

do you know ,what is the priority given for the admin in user_grp?..I think You will be knowing the priority.then initially in your sp check the presence of the username and password usingif exists(your query)then,declare a variable of your datatype and pass the usergrp for...

SQL

stored-procedure

Problem in Stored Procedure

4 Dec 2012 by MT_

I believe you need to replacecmd.Parameters.AddWithValue("@user_firstname", firstname);cmd.Parameters.AddWithValue("@user_lastname", lastname);withcmd.Parameters.AddWithValue("@firstname", firstname);cmd.Parameters.AddWithValue("@lastname", lastname);Hope that helps. If it...

SQL

stored-procedure

Transaction count after execute indicates a mismatching number of begin and commit

13 Dec 2012 by Rai Pawan

HiPlease be aware that you are calling the RETURN statement before the commit transaction instruction, so in a way your commit transaction statement never gets executed. You must know that inclusion of return statement implies that the it exits unconditionally from a query or...

stored-procedure

Execute Stored Procedure

27 Feb 2013 by CHill60

In your procedure your first parameter is @username nvarchar(50),but you haven't put it into your C# code ...cm.Parameters.Add("username", SqlDbType.NVarChar, 50).Value=...{Edit] - from the comments attached OP wants the username to be autogenerated by the line SET...

stored-procedure

Stored Proceedure Exception

28 Feb 2013 by OriginalGriff

Change your c# code: the SP is expecting @subitem_name and you are providing subitem_namecm1.Parameters.Add("@subitem_name", SqlDbType.NVarChar).Value = txt_subitem.Text;You will need to change the names of all parameters to match!

stored-procedure

Executing multiple stored procedure within single block

12 Mar 2013 by Pheonyx

You could repeat the same process one after the other for both stored procedures.However if you were going to do this I would wrap them around a "Transaction" object so that if one fails it will roll back both the changes.

SQL

stored-procedure

How to call a stored procedure in Silverlight Web Application

26 Mar 2013 by Dee_Bee

Hi all,How to call stored procedure in Silverlight Web Application.I'm working on Silverlight apllication and using WCF services for database connections.I'm writing queries to retrieve data from database. But I need to retrieve data using stored procedures. How to achieve...

How to call a stored procedure in Silverlight Web Application

27 Mar 2013 by Member 8196277

//Well, assuming you use ADO.Net in the service side to access the SQL Server://Remember, that SQL Service calls sits in the .Net service application - which is using //clean .Net (2.0 -> 4.5) and that, has ADO.Net access. You cannot access ADO.Net //directly from the Silverlight...

How to fetch the next records from a table in sql database one after other

12 Apr 2013 by Abhinav S

It will not be optimal for you to fetch records in groups of 30 from the database. To optimize this connection and network overhead, get all data using a single query. Put this data in a DataTable.Loop through groups of 30 in the DataTable, get the email ids and send mails before proceeding...

Stored procedure for Sql Command

24 Jul 2013 by Thomas Daniels

Hi,Have a look here:http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson07.aspx[^]http://msdn.microsoft.com/en-us/library/ms171921%28v=vs.110%29.aspx[^]I see that you use string concatenation to build a query. NEVER use string concatenation to build SQL queries! If you use it,...

SQL

stored-procedure

selection

How to pass Where Clause to Store Procedure as a parameter

29 Oct 2013 by Richard C Bishop

You want to do something like this:if @Mode= 'Searching'BEGINSelect [Hadith_Text] ,[Hadith_Urdu] ,[Hadith_English] ,[Chapter_English_Name] ,[Chapter_Urdu_Name] ,[Baab_English_Name] ,[Baab_Urdu_Name] ,[Baab_Id] ...

update with Case Conditions and Joins

26 Jan 2014 by Roja23

You can't have assignment operation inside a case statement, it can only return value which can be assigned to quantity in your above code. So replace attrited = 'true' with whatever value you want to assign to quantity. It will be something like below. update PD set quantity = ( ...

How to get rid of this SQL errot?

4 Apr 2014 by Guruprasad.K.Basavaraju

Please change the line that is underlined below.GOCREATE PROCEDURE SP_ADD_ENQUIRIES(@POL VARCHAR(50),@POD VARCHAR(50),@FPOD VARCHAR(50),@WEIGHT REAL,@SHIPPER VARCHAR(150),@CONSIGNEE VARCHAR(150))ASDECLARE @SUFFIX INT;DECLARE @PREFIX VARCHAR(3);DECLARE @RESULT...

SQL-Server-2008R2

stored-procedure

Is there any way to Optimize this Store Procedure or Queries inside Sp

10 May 2014 by Manas Bhardwaj

You could start something like this:Btw, the core of writing a program is logical structure than the actual syntax.CREATE PROC uspSetSubscriptionPayment @PayMethod varchar(50), @PackageType varchar(25), @Userid int, @ConstID int, @SubscriptionIdentity int...

Comparing data between two tables in SQL Server

23 Oct 2014 by CHill60

I ran your sql and swapped the EXECUTE for a PRINT and got thisSELECT DISTINCT [Projektdefinition DB] AS Zugänge FROM [xyz]EXCEPT SELECT DISTINCT [Projektdefinition DB] AS Zugänge FROMxyzso I think this will fix your problemSET @Dynamictb2 = N'SELECT DISTINCT [Projektdefinition DB] AS...

SQL

stored-procedure

store procedure for commit transaction

28 Oct 2014 by Er. Dinesh Sharma

HI Try thiscreate procedure demo1@input int as declare @transcounter intset @transcounter=@@TRANCOUNTIF @@TRANCOUNT>0save tran demoelsebegin trydelete emp where empid='1'commit tranEND TRYBEGIN CATCHEND CATCH;

SQL-Server-2008R2

stored-procedure

database

Stored procedure execution time increased by 50%

2 Nov 2014 by Manas Bhardwaj

Just by looking at the question, it's very difficult to pinpoint what is the real reason.However, you need to look at the following factors:1.) Has there been a substantial change in the dataset i.e. the amount of data your database/tables.2.) Usually, SQL Server keeps an optimal...

stored-procedure

How Can I Solve This Stored Procedure (4 Week Record Display)

2 Nov 2014 by Tomas Takac

In the query you produce you need to name the inner query like this:SELECT ...FROM ( ... ) x -- name it yourselfGROUP BY CourseName,GL_Course.StartDateThe inner query needs to have an alias even if you don't reference it explicitly in the outer query.EditIn your code...

SQL-Server

stored-procedure

how to get substring from string using storedprocedure in sql server

3 May 2015 by OriginalGriff

Don't store it like that.SQL is not good at string manipulations, and you need to find two numbers, extract them, validate them, convert them to an integer, and then compare them.Instead, change your database to use two integer columns (and possibly an NVARCHAR column for the prefix) and use...

How Do I Make Select Query With Variable Table Name?

25 May 2015 by Mehdi Gholam

In stored procedures all table names should be known at "compile time" meaning you can't use variable names.If you need dynamic queries then you should use exec() : http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/[^]However there are security issues...

SQL-Server-2008R2

stored-procedure

How to call c# function from stored procedure ?

16 Jun 2015 by Mathew Soji

No, you can't call a web page function from SQL Server. But you can call managed code written in .NET class library in your SQL Server 2005 and later version stored procedure.Please refer below links.Execute .NET Code under SQL Server...

C#5.0

ASP.NET

stored-procedure

how to avoid "Arithmetic overflow error converting varchar to data type numeric" Error

14 Aug 2015 by Wendelius

In my opinion the first problem is that you're using varchar for numeric data. You should always use native types like bigint or float or similar when storing data.The limit of 30 numbers comes from your conversionCAST(PrivilegeID AS NUMERIC(30, 0))You could try with CAST(PrivilegeID...

stored-procedure

How to get count in output variable by store procedure?

24 Apr 2016 by Tomas Takac

To assign each result to a different variable do it one by one like this:SELECT @pending=COUNT(*) FROM tblRequest WHERE RequestStatusID='pending'SELECT @sent=COUNT(*) FROM tblRequest WHERE RequestStatusID='sent'Alternatively you can store the results in a table variable a query...

stored-procedure

Category type grouping SQL

4 May 2016 by Suvendu Shekhar Giri

You can sort the result you are getting by executing the query you have shared using ORDER BY clause. And yes, if you want this sort to happen in a customized fashion, you can use CASE WHEN.. clause accordingly.Something like following may be-select...

SQL-Server-2008R2

stored-procedure

Nested while loop in stored procedures

12 Jul 2016 by abinash panda

Declare @cSex varchar Set @cSex = (Select cSex from StaffMaster where iSMID = @iSMID) While(@iSMID

An exception of type 'system.data.sqlclient.sqlexception' occurred in system.data.dll but was not handled in user code additional information: incorrect syntax near the keyword 'by'.

16 Feb 2017 by Garth J Lancaster

well, first off, the exception actually says there's something wrong with your sql - do you have a column 'Id' for example - what happens if you remove the 'order by...' clause ?secondly, thats a horrible way to write SQL statements - you should use parameterised queries, along the lines of...

How to pass date value in stored procedure using VB.NET

2 Apr 2017 by CHill60

Use a parameterized query and it should overcome the problem. OleDbCommand.Parameters Property (System.Data.OleDb)[^] Exampe: (untested) cmd = New OleDbCommand("SP_ExpEntry", con) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.AddWithValue("@OperationId", 1)...

SQL

stored-procedure

Is STORED PROCEDURE the best way to use in large database?

8 Jul 2017 by Wendelius

I wouldn't say that the size of the database should be the driving force to use or not to use procedures. After all a database which someone thinks large may be small to another. The factors that affect the usage of the procedures include: Eliminate round-trips, with procedures you may be...

SQL

.NET

stored-procedure

Sql stored-procedures-DO-NOT work please help

19 Feb 2018 by Dave Kreskowiak

You code does indeed delete every record from TBL_sale_invdet where any record has the saleinvid of @saleinvid. It then inserts a single record with the same specified @saleinvid. What did you think was going to happen? If every record in your TBL_sale_invdet has the same saleinvid value,...

SQL

SQL-Server-2008R2

stored-procedure

Getting a null in inserting data using scope_identity

20 Jan 2019 by OriginalGriff

Yes, you can. But ... not like that. Your code is trying to insert the value to the DB: INSERT INTO employeeAttritions(EmployeeID, WaveNumber, ...) VALUES (@EmployeeID, @WaveNumber, ...) And then read the identity value from the system immediately after: SET @EmployeeID = SCOPE_IDENTITY() But if...

SQL

stored-procedure

How to solve subquery returned more than 1 value in strored procedure?

26 May 2019 by phil.o

You could try:UPDATE dbo.AD_Staff_Attandance SET TotHours = dbo.GetTotalWorkingHours(DateOut, DateIn) As there is no WHERE clause, this request will update all rows in the table; the request will last proportionnaly to the number of rows.

stored-procedure

How to execute stored procedure with every row of temp table ?

9 Nov 2019 by MadMyche

While it is not something I generally recommend; it may just be easiest to use a CURSOR for this application. This is a rough sample of what it could look like, it is up to use appropriate variable types/sizes and to work out what the SELECT statement actually will be; so consider this a...

SQL

stored-procedure

transaction

How to write this statment by using join without using (not in) statment ?

24 Jan 2020 by MadMyche

There are a couple of ways to achieve this. Here is your original query, along with two other versions based on the query you provided (based on what I authored a day ago).SELECT PartID, SupplierId FROM #parts WHERE PartID NOT IN ( SELECT t.PartID FROM #tempsupplier AS s INNER JOIN...

SQL

stored-procedure

How to execute query below without using exec statement ?

3 Feb 2020 by Richard Deeming

You can't generate a dynamic pivot without using dynamic SQL. Which means you have to use EXEC. You could potentially use sp_executesql[^] if you had parameters to pass in. But you can't pass the pivot column names as parameters. The only other option would be for your query to return the raw...

SQL

stored-procedure

How to write loop below with best practice performance ?

13 Feb 2020 by Jörgen Andersson

One way of doing batch inserts:DECLARE @Batchsize INT = 5000; DECLARE @RowCount INT = @Batchsize; DECLARE @LastID INT; WHILE @RowCount = @Batchsize BEGIN SET @LastID = ( SELECT ISNULL(max(MyTableID), - 1) FROM MyTable...

SQL

stored-procedure

How to sort by month in pivot table?

27 Feb 2020 by Maciej Los

You need to use ORDER BY clause[^]. SELECT * FROM ( SELECT CurrCode, YEARNO, Monthno, TOTALAMOUNT FROM [dbo].[ML_tbl_Analaysis] where Currcode =@CURRCODE ) tbl PIVOT (sum(TOTALAMOUNT) FOR [yearno] IN...

stored-procedure

pivot-table

How to separate field partdone to text yes or no or null based on position ?

10 Mar 2020 by Maciej Los

You can use CTE[^] togethe with PIVOT[^] for such of requirement. See: DECLARE @tmp TABLE(partId int, PartDone nvarchar(50)) INSERT INTO @tmp(partId, partDone) VALUES(555444, '0012'), (911877, '0221'); ;WITH CTE AS ( SELECT partId, PartDone,...

SQL

stored-procedure

When update data on temparts table mass value changed after float point ?

7 Nov 2020 by Daniel Pfeffer

In table #TempPc, you define Mass as a float. A float typically has a range of about 10-38 to 1038, and a precision of about 6 digits. This means that a value such as 580.28613 will be rounded to 508.286. If you use the double type, your value...

Sql: group by, sum, update

17 Sep 2021 by CHill60

When summarising data in a SELECT statement you would never use UPDATE. You are doing one or the other not both (although you can update a table based on the results of a SELECT, but the image does not imply that that is what you are trying to...

How to validate user input date formate as yyyy-MM-dd for created date ?

10 May 2022 by Dave Kreskowiak

Your problem isn't necessarily the validation. It's the fact that you're storing dates as a string in the database. That's NEVER a good idea.

T-SQL

SQL

stored-procedure

Paid and remain amount always saved null in database

10 Jul 2023 by Graeme_Grant

This is your 5th question posted. At least put some effort in and learn to format your code so we can at least read it. I have done that for you. If you want help, and for us to read your code, take pride in what you post, it will show. Click on...

Any advantages when using an ORM Tool (Framework)?

5 Jun 2011 by Sander Rossel

I have been using the Entity Framework for a while now. A few advantages for me are automatic Class creation from the DB metadata and using LINQ. Also, using an ORM tool does not mean you cannot use Stored Procedures. I do not think one method is better than the other. It depends on requirements...

entity-framework

stored-procedure

NHibernate

Any advantages when using an ORM Tool (Framework)?

5 Jun 2011 by #realJSOP

One advantage is that you learn how to not expect a 3rd-party library to be a magic bullet, and that no matter what you select, there will be issues to overcome.

entity-framework

stored-procedure

NHibernate

What am i doing wrong?

3 Aug 2011 by sudeshchandram

I have corrected your code, Please try this,string sCon = ConfigurationManager.AppSettings["ConString"]; SqlConnection con = new SqlConnection(sCon); SqlParameter p1 = new SqlParameter("@Username", SqlDbType.NVarChar,50); SqlParameter p2 = new...

SQL

stored-procedure

Do we need to promote stored procedures anymore?

24 Sep 2011 by Mehdi Gholam

Once upon a time I was under the influence of vendor marketing and used stored procedures extensively, now I am against them altogether, so that's where I stand.The vendors have you believe that :1) They are more secure. Implying that you access your data through them nstead of direct...

stored-procedure

database

increasing value without using cursor

23 Jan 2012 by Swapnil3

There is a one way that u can use Update statement after insert statement in your stored procedure.

stored-procedure

sql server collation

6 Feb 2012 by Anuja Pawar Indore

Refer this, might help youhttp://www.sqlhub.com/2009/07/deal-with-indian-regional-language-in.html[^]

how to merge more than two pdf file into one pdf file using gridview wise selected checkbox.

15 Mar 2012 by BobJanova

PDF files are self-contained documents with internal cross referencing, object IDs and so on. You can't just run them together. You either need a PDF reading/writing library or, if the PDFs are simple and you can follow the format, you need to read in all the objects, renumber the IDs of the...

SQL-Server-2008

SQL-Server

stored-procedure

How to populate ListBox with stored procedure results

12 Apr 2012 by Wes Aday

You need to call reader.Read() before trying to access the data. In your example you are calling it after. Change your "do" loop to a "while" should fix it.while(reader.Read()){ tempVal = reader[0].ToString(); }

Stored Procedure is executing successfully from backend but not from frontend

7 May 2012 by Ashish Sehajpal

try this :SqlCommand.Timeout = 1000;try changing your time out values for your command object depending upon the requirements. But this is not a good idea to rely on timeout. rather change your SP.

creating stored procedure for 'th' highest marks

17 May 2012 by Wendelius

The procedure itself is quite straightforward, modify the statement as per requirements and then include inside a procedure, see CREATE PROCEDURE [^]About the query, I would advice to separate the first column to two separate columns if the data describes different things. Concatenated...

creating stored procedure for 'th' highest marks

17 May 2012 by VJ Reddy

If the requirement is to retrieve nth row from the Table then, the ROW_NUMBER function of Sql Server can be used for this purpose as shown belowCREATE PROCEDURE GetnthRow @nthRow nvarchar(30)ASWITH OrderedMarks AS( SELECT ROW_NUMBER() OVER (ORDER BY stdName) as RowNo,...

Simple Login Page in asp.net using stored procedures

31 Jul 2012 by sauravbanthia

If you want to check through a stored procedure that whether the username and password used in the login process is correct or not, then you may use an output parameter in the stored procedure. Declare an output parameter as follows:Declare @result as int outSuppose table name is...

Access multiple columns Mysql Stored Procedure

15 Aug 2012 by Wendelius

The likeliest reason is that the col parameter in your stored procedure is too small in length. So try increasing the length of that parameter in your procedure definition.

MySQL

ASP.NET

stored-procedure

Access multiple columns Mysql Stored Procedure

15 Aug 2012 by pradiprenushe

Check your @col data type. I think you have used less character in varchar(20).But you input is hvaing more than 20 chracter.'country,IPRStart,IPREnd' = 23 characterSo your input is get shrink so it is taking first 20 chracter. Try this varchar(max); or some big value like 100

MySQL

ASP.NET

stored-procedure

Updating a table through stored procedure in asp.net

16 Aug 2012 by Aritra Nath

Hi guys,I'm trying to update a table through stored procedure. I am having a page from which on button click a popup window appears. i'm sending a ID from the main page to the popup window using querystring, where the details from the table by ID is displayed in textbox. Here we can change the...

How to create a stored procedure having a value and array input

16 Aug 2012 by __TR__

Hi, The below article might be helpful.Sending a DataTable to a Stored Procedure[^]

stored-procedure

How to Get the Primary Key in stored Procedure

21 Aug 2012 by Wendelius

While SCOPE_IDENTITY works fine to get the primary key, I typically prefer the OUTPUT clause. The main reason is that the OUTPUT clause can return much more information from the inserted row and also may reduce roundtrips especially if the statement is called from outside the SQL Server.For...

SQL

stored-procedure

Store proce not working

15 Sep 2012 by skydger

I think it is a problem in this string:if (result.Rows.Count>0)Try to set following code in your procedure:SET NOCOUNT OFF;

stored-procedure

DataTable

Reportviewer error when linking stored procedure to report

16 Sep 2012 by Sandeep Mewara

Have a look at these on how to pass on parameters for SSRS: Reporting Services (SSRS) OR Filter Logic[^]MSDN: Add a Filter (Report Builder and SSRS)[^]Adding Filter Parameters to SQL Server 2008 Reporting Services Reports[^]

SQL

stored-procedure

reading a parameter in store proc

19 Sep 2012 by WebMaster

change your code by tis way:if (dr.Read()){ object[] values = new object[dr.FieldCount]; Int32 ob = dr.GetInt32(0); ob = dr.GetInt32(0)}

SQL

stored-procedure

How to extract date fields from string/text field in sql server 2005

22 Oct 2012 by Zoltán Zörgő

The easiest way is to use regular expressions. As SQL server does not support regular expressions by default, you have to extend it using .net integrated user function. It is really not complicated. See these two articles: http://msdn.microsoft.com/en-us/magazine/cc163473.aspx[^], Regular...

How to insert multiple records in multiple tables with stored procedure

27 Oct 2012 by Zoltán Zörgő

No, MAX and such things might mislead you in production environments since there is also something called transaction. If your primary keys are identities[^], you can use SCOPE_IDENTITY[^] and IDENT_CURRENT[^] to get the latest identity generated on a table or in a scope. You probably will need...

SQL

SQL-Server

stored-procedure

Conversion failed from varchar to int

7 Nov 2012 by OriginalGriff

It's not possible to be sure without the tabl;e definitions, but I would suspect this line:SELECT cast(VendorId as Int), cast(VendorName as int), cast(AccountId as int),cast(AccountName as Int),APIName ,SUM(TransactionCount) I doubt that VendorName and an AccountName are integer values - the...

SQL-Server

stored-procedure

Conversion failed from varchar to int

7 Nov 2012 by Shanalal Kasim

Please change below sqlSELECT cast(VendorId as Int), cast(VendorName as int), cast(AccountId as int),cast(AccountName as Int),APIName ,SUM(TransactionCount) toSELECT convert(Int, VendorId ), convert(int , VendorName), convert(int , AccountId),convert(int, AccountName),APIName...

SQL-Server

stored-procedure

how to create a temp table in sql server

18 Nov 2012 by solanki.net

For creating Temporary table :EX :create table #tempTable(Name varchar(30)) // #TableName for creating temp table

SQL-Server

stored-procedure

Need of SQL Procedure for update,delete dynamic Columns and rows and Bind to asp.net gridview in the Front End for update and delete operations

28 Nov 2012 by Mycroft Holmes

While I did not go through the whole thing, looking at the desired output you need a pivot query!blatant plug for article Pivot two or more columns in SQL Server 2005[^]. This may help it is perfectly valid for 2008 and later!

SQL

stored-procedure

Gridview

Problem in Stored Procedure

4 Dec 2012 by Krunal Rohit

I'm gettin' this error : Procedure or function 'RegistrationProcedure' expects parameter '@firstname', which was not supplied.Stored Procedure : ALTER PROCEDURE dbo.RegistrationProcedure @user_id varchar (10), @firstname varchar (50), @lastname varchar (50), @password varchar...

SQL

stored-procedure

Problem in Stored Procedure

4 Dec 2012 by azizulhoque.bd

Hi dear,Check this solution.cmd.Parameters.AddWithValue("@user_id", 10);cmd.Parameters.AddWithValue("@firstname", firstname);cmd.Parameters.AddWithValue("@lastname", lastname);cmd.Parameters.AddWithValue("@password", password);cmd.Parameters.AddWithValue("@email",...

SQL

stored-procedure

Cannot pass string parameter to stored procedure C#

14 Dec 2012 by Aelion

I found problem solution!I should pass whole command parameter like this SqlDataAdapter ad = new SqlDataAdapter(com);not just com.CommandText ... it was stupid.... but I lost so much time...

SQL

stored-procedure

this is my stored procedure for insert. how to write the same for update.

9 Jan 2013 by Sumit_Kumar_Sinha

create procedure [dbo].[Resource_Update_sp] @ID int, //ID for updating a specific records @FirstName varchar(50),@LastName varchar(50),@PrimarySkill varchar(50),@SecondarySkill varchar(50),@Email varchar(50),@Phone int=0,@ResourceManager varchar(50),@Customer...

stored-procedure

Update storedprocedure not updating table data

25 Jan 2013 by MT_

Well..well,You are saying you have data in the table so naturally the first condition in your SP will be trueYour update statement is in ELSE, so it will never execute.Second and very important point, you do not have "where" clause in the UPDATE statement, this will update ALLThe...

SQL-Server

SQL-Server-2005

stored-procedure

Stored Procedure to count varying no of items

6 Feb 2013 by Rob Branaghan

Based on just the code you have above... You dont need the select * from Library. You also dont need to have so many sets and selects.try just Select Count(*) As TotalCount FROM bookissue WHERE bookid = @book_id AND stuid = @stud_idAt a guess that will only tell you the number of times...

SQL-Server

stored-procedure

Stored Procedure to count varying no of items

6 Feb 2013 by Irbaz Haider Hashmi

What i am getting hereyou need to have the count for books available in the library?select count(1) from librarywhere bookid not in (select bookid from bookissue)There must be some relation in both the tables. What i did here is that i counted the books in the library that does...

SQL-Server

stored-procedure

C# Business Class and Stored procedures (insert, update, delete)

15 Feb 2013 by Chris Reynolds (UK)

Posted as a solution so we can close this down:It looks like the OP was passing null values into his function and needs to check for this as Fred says before attempting to insert into SQL.

C#4.0

stored-procedure

passing arguments problem in store procedure

2 Mar 2013 by Karthik Harve

Hi,LINQ To SQL, will always convert the stored procedure definition into a Named Parameter method signature. So, you must pass the default values. Anyway, you can avoid this. you just have change the Named parameter signature to optional method signature. when you drag and drop the...

LINQ

ASP.NET

stored-procedure

Dynamically Linkbutton Click Event

6 Mar 2013 by Ankit_Sharma1987

Hello Friends, I have code, to pick up the, values from database in Session, By Clicking on Dynamically Generated Linkbutton. I want to redirect those values to another page, my code is here..using System;using System.Collections.Generic;using System.Linq;using System.Web;using...

SQL

ASP.NET

stored-procedure

Dynamically Linkbutton Click Event

10 Mar 2013 by Maciej Los

Please, check how to add parameters to SqlCommand: SqlParameterCollection.AddWithValue[^].Hint: You need to pass 5 parameters ;)

SQL

ASP.NET

stored-procedure