Using Delete With Sp_executesql And Getting Return Value
May 19, 2008
The basic syntax for the sp_executesql with a return value is:
Code Snippet
DECLARE @count int
DECLARE @ParmStr nvarchar(256)
set @count=0
set @ParmStr = N' @lvl tinyint, @cnt int OUTPUT'
execute sp_executesql
N'select @cnt=count(*) from pubs.dbo.employee where job_lvl = @lvl',
@ParmStr, @lvl = 35, @cnt=@count OUTPUT
print 'count: ' + cast(@count as nvarchar(4))
This returns a value of 3.
I need to change this to return the numbers of rows that were deleted, such as:
Code Snippet
DECLARE @count int
DECLARE @ParmStr nvarchar(256)
set @count=0
set @ParmStr = N' @lvl tinyint'
execute sp_executesql
N'delete from pubs.dbo.employee where job_lvl = @lvl',
@ParmStr, @lvl = 35
-- Need count
print 'count: ' + cast(@count as nvarchar(4))
Any ideas on how to modify this so that I can report on number of rows deleted?
I am trying to use dynamic sql with a return parameter, but with limited success. I am using WebMatrix, vb.net and MSDE to perform this routine. Can someone please clue me in. I have read two fine articles by <a href='http://www.algonet.se/~sommar/dyn-search.html>Erland Sommarskog</a> on dynamic sql using sp_executesql, as well as the somewhat opaque article by Microsoft (262499) on the subject.
While there may be other ways to accomplish this task, I am interested in making it work with dynamic SQL. In production, there will be over 20 parameters coming from the vb.net to the SQL, being driven from user input. Then those same variables will be used to actually retrieve the records to a datagrid.
So with a tip of the cap to Rod Serling, I submit this small code and SQL for your consideration from my Twilight Zone:
Public Function totalrecordsbysql(list as arraylist) as integer dim RetVal as new integer dim querystring as string
Dim cn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("Indiafriend")) Dim cmd As SqlCommand = New SqlCommand("SimpleDynProfileCount", cn) cmd.commandtype = commandtype.storedprocedure
try mydr=cmd.executereader() catch e as sqlexception dim err as sqlerror dim strErrorString as string
for each err in e.Errors strErrorString += "SqlError: #" & err.Number.ToString () & vbCRLF + err.Message trace.write("sqlexception",strErrorString) Next
Please note the commented RAISERROR statement. If I uncomment this statement, I will get a return value of 11 records. If I leave it out, I get zero records. The data is the database should return 11 records, based on the criteria of age > 11
This is a odd problem where a bad plan was chosen again and again, butthen not.Using the profiler, I identified an application-issued statement thatperformed poorly. It took this form:exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @Parm1)',N'@Parm1 int', @Parm1 = 8609t2 is a foreign key column, and is indexed.I took the statement into query analyzer and executed it there. Thequery plan showed that it was doing a scan of the primary key index,which is clustered. That's a bad choice.I then fiddled with it to see what would result in a good plan.1) I changed it to hard code the query value (but with the parmdefinition still in place. )It performed well, using the correct index.Here's how it looked.exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROMcbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@Parm1 int',@Parm1 = 8609After doing this, re-executing the original form still gave badresults.2) I restored the use of the parm, but removed the 'exec' from thestart.It performed well.After that (surprise!) it also performed well in the original form.What's going on here?
Hello, I created a DELETE Stored Procedure in SQL 2005. When calling this procedure from my server code (VB.NET in my case) I need to know if the record was deleted or not. How should I do this? Should I make the procedure to return True or False? If yes, how can I do this? My Stored Procedure is as follows (I think it is ok): SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Content_DeleteContent] -- Define the procedure parameters @ContentName NVARCHAR(100), @ContentCulture NVARCHAR(5) AS -- Prevent extra result sets from interfering with SELECT statements. SET NOCOUNT ON; -- Declare and define ContentId DECLARE @ContentId UNIQUEIDENTIFIER; SELECT @ContentId = ContentId FROM dbo.Content WHERE ContentName = @ContentName -- Check if ContentId is Not Null IF @ContentId IS NOT NULL BEGIN -- Check if ContentId is Null IF @ContentCulture IS NULL BEGIN -- Delete all localized contents from dbo.ContentLocalized DELETE FROM dbo.ContentLocalized WHERE ContentId = @ContentId -- Delete content from dbo.Content DELETE FROM dbo.Content WHERE ContentName = @ContentName; END ELSE -- Delete localized content from dbo.ContentLocalized DELETE FROM dbo.ContentLocalized WHERE (ContentID = @ContentID AND ContentCulture = @ContentCulture) END GO Thanks, Miguel
I have been trying to get my dynamic query to work with sp_executesql and I cant seem to figure out this one issue.DECLARE @SQL NVARCHAR(1000)SET @SQL = N'WITH Data AS(SELECT Id, Username, FirstName, LastName, Email, LastLogin, ROW_NUMBER() OVER(ORDER BY @SortExpression) AS RowNumber FROM Users) SELECT * FROM Data WHERE RowNumber BETWEEN @Between1 AND @Between2'EXECUTE sp_executesql @SQL, N'@SortExpression VARCHAR(50), @Between1 INT, @Between2 INT', @SortExpression = 'Email', @Between1 = 1, @Between2 = 10As you can see, the data should get sorted by the value of @SortExpression. However thats not the case. The Data does not get sorted at all no matter that i pass in as the value of @SortExpression.I can't seem to figure out why its not working.
I'm having trouble working out why the sp_executesql procedure is not replacing my place holders with the value assigned to it.
Some quick info: I'm running the routine from the commandline through OSQL on a box that has MSSQL2000 enterprise installed. The code is sent to a MSSQL2005 box.
I've noticed one dumb thing I've done and that is making the nvarchar variable @db_name a different size to the one declared in the sp_executesql command. But I'm not sure if that is the problem. It throws a @db_name is not a database error etc.
Snippet that is not working:
declare @db_name varchar(80)
declare @sql_command nvarchar(1500)-- for our dynamic sql command within the cursor loop.
Hi I am trying to execute sp_executesql dynamically. What I am trying to do is read all the user tables using a cursor build sql statement and using EXEC sp_execute sqlstmt. Here is piece of code.
DECLARE C1 CURSOR FOR SELECT NAME FROM SYSOBJECTS WHERE XTYPE='U' OPEN c1 FETCH NEXT FROM C1 INTO @v_TableName WHILE @@FETCH_STATUS = 0 BEGIN SELECT @v_SQL= 'DROP TABLE ' + @v_TableName --EXEC @v_SQL PRINT @v_SQL IF @v_Error<>0 BEGIN SELECT @ErrorCount=@ErrorCount+1 PRINT 'ERROR OCCURED WHILE DROPING TABLE ' + @v_TableName --GOTO ErrorHandler END FETCH NEXT FROM C1 INTO @v_TableName END CLOSE c1 DEALLOCATE C1
hi everybody How can we execute a string of sql statements in Oracle ,similar to sp_executesql in sql server. ie a string can contain insert into a table statement,delete a row from a table statement, update etc. Thanks all of You
Is there anything that will allow you to execute a line of sql code if it is longer than 4000 Unicode characters? The line of code is stored in a NVARCHAR Variable.
I'm using sp_ExecuteSQL and have hit the 4000 character wall
Hi all,Can sp_executesql used inside a user defined function, itried but it has compiled well, but when i call the functio it showsOnly functions and extended stored procedures can be executed fromwithin a function.What i have went wrongThanks in advancethomson
I have a full sql statement which was generated dynamicly, and need toexecute that string and then take the output and generate aspreadsheet document based on the output. I'm new to sql and the bookI have doesn't really explain much. Anyone with an example of theirwork would be appreaciated.thank you.
(RECEIVE message_body, conversation_handle, message_type_name, message_sequence_number, conversation_group_id FROM ' + @callingQueue + ' INTO @msgTable WHERE conversation_group_id = '
+ CAST(@conversationGroup AS char) + '), TIMEOUT 2000'
EXEC sp_executesql @SQL, N'@msgTable TABLE output', @msgTable out
I get the following message:
Msg 137, Level 15, State 2, Procedure CENTRAL_Queue_Processor, Line 92
Must declare the scalar variable "@msgTable".
I have decalred the variable but it is a table variable, this leadds me to believe sp_execute sql only supports scalar varibles not table variables, BOL does not say yes or no in this respect.
I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.
I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT) The sql task rowset is set to NONE. It is a OLEB connection.
I have two parameters mapped:
tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO. returnvalue output long 1
I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.
The first part of the sp is below and I set the value @i and return.
Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.
I am using sp_executesql this to pass parameter to sql string and I am seeing deadlock between sp_prepexec which does UPDATE with another UPDATE done by another process. When it comes to speed and deadlock, would you recomand not using sp_executesql?
In out web application it happens very rarely that same query gets executed more than once meaning that sp_executesql is degrading performance. Does anyone know a way to tell ADO.NET to stop encapsulating queries in sp_executesql? Thank you.
Hi all, I just wanted to know why this doesn't work: if @1's values is computer---------------------------------------------------------------------------------------------------------- BEGIN FETCH NEXT FROM keyword_cursor into @1 SELECT @sql = @sql + 'where title LIKE ' + '''%@x1%''' + ' OR notes like ' + '''%@x1%''' SELECT @paramlist = '@x1 nvarchar(200)' print @sql EXEC sp_executesql @sql, @paramlist, @1 RETURN 0 ENDThe @sql string evaluates to:select title, notes from pubs..titles where title LIKE '%@x1%' OR notes like '%@x1%'-----------------------------------------------------------------------------------------------------------But this works: BEGIN FETCH NEXT FROM keyword_cursor into @1 SELECT @sql = @sql + 'where title LIKE ''%''+ @x1 + ''%'' OR notes like ''%'' + @x1 + ''%''' SELECT @paramlist = '@x1 nvarchar(200)' print @sql EXEC sp_executesql @sql, @paramlist, @1 RETURN 0 ENDThe @sql string evaluates to:select title, notes from pubs..titles where title LIKE '%'+ @x1 + '%' OR notes like '%' + @x1 + '%'---------------------------------------------------------------------------------------------------------------I just don't get it ?? Doesn't sp_executesql just replaces the @x1 with @1?
I try to write query that use sp_executesql to query data by Like operation with 1 parameter like below: execute sp_executesql N'SELECT DISTINCT au_id, au_lname,au_fname FROM authors WHERE au_lname LIKE @au_lname ', N'@au_lname nVarChar', @au_lname = N'%Cas%'
but It return all rows regardless of changing condition to any value.
But if i don't use sp_executesql like below:
SELECT DISTINCT au_id, au_lname,au_fname FROM authors WHERE au_lname LIKE N'%Cas%'
I try to write query that use sp_executesql to query data by Like operation with 1 parameter like below: execute sp_executesql N'SELECT DISTINCT au_id, au_lname,au_fname FROM authors WHERE au_lname LIKE @au_lname ', N'@au_lname nVarChar', @au_lname = N'%Cas%'
but It return all rows regardless of changing condition to any value.
But if i don't use sp_executesql like below:
SELECT DISTINCT au_id, au_lname,au_fname FROM authors WHERE au_lname LIKE N'%Cas%'
Hi, I want to use the output of the sp_executesql to update a coulmn in the table. example -first i run the below to get output execute sp_executesql @Query, @returnedCount output
-then I want to use that output to update another coulmn in the table update tableName set coulmn=@returnedCount
I am new to this and cannot figure out how. Can someone please guide me? thank you!!
Hi all i have Function and in the context of this function i need to build a Dynamic Query String according to input parameters and execute it with sp_executesql. BUT until now i didn't know that SQL doesn't allow to have Exec command within a function,am i right? Apparently this is true because for example create the following Function..
Create Function Test(@Input int) Returns int AS Begin Exec sp_who -- only for Test purpose Return @Input End
Now Execute this --> Select dbo.test(12)..... Sql Server will return the following Error
Server: Msg 557, Level 16, State 2, Procedure Test, Line 6 Only functions and extended stored procedures can be executed from within a function.
Could Any one help me? i need function with dynamic Sql execution because i can only use function in SELECT statements !!!
Any help greatly would be appreciated. Kind Regards.
IF OBJECT_ID(N'aquery') is not null DROP FUNCTION aquery GO
CREATE FUNCTION aquery() Returns nvarchar(500) as Begin Declare @var nvarchar(500); Set @var = 'Select Distinct Description from dbo.tblScanners'; Return @var end GO
exec sp_executesql aquery;
I created that code to prove if it works. But the result doesn´t appear, and the message is:
I have not used this sp. We have a dynamic SQL statement generated by a sp.For performance reasons I would like to use it to reduce the number ofexplain plans created. I would like to understand its usage and pitfalls(if any) to its use. Any comments from the user community?
I'm trying to build a stored procedure with parameters and sp_executesql. I can't seem to get the types correct. I have two parameters I want to pass: @ADDIVNumber which will be a bigint and @Where which can be varchar(500). I can't seem to figure out how to get the varible types right.
ALTER PROCEDURE [dbo].[AMTRANHDRPaidTranHistAP]
@CharVariable varchar(500),
@IntVariable bigint
as
Declare
@SQLHolder nvarchar(4000)
set @SQLHolder = 'SELECT T1.SMBNKNumber, T1.AMACTNumber, T1.AMALTNumber,
