Stored procedure does not work correctly with dynamic sql text

Question

For some reason, my stored procedure runs without any error from the code in C #, but it does not delete anything that the stored procedure wrote. I have all the right parameters and that's it. I ran a query from SQL Server with all the same parameters from C # code, and it works fine. I don’t understand why this works when I run from SQL Server, but it does not work when I run it from my C # code in Visual Studio.

Here is my C # code that passes data to a stored procedure.

string reportType = "PostClaim"; string GRNBRs = "925','926','927"; string PUNBRs = "100','100','100"; string beginningDates = "20120401"; string endDates= "20120430"; try { conn = new SqlConnection(ConnectionInfo); conn.Open(); SqlDataAdapter da = new SqlDataAdapter("RemoveReport", conn); da.SelectCommand.CommandType = CommandType.StoredProcedure; da.SelectCommand.Parameters.AddWithValue("@ReportType", reportType); da.SelectCommand.Parameters.AddWithValue("@GRNBR", GRNBRs); da.SelectCommand.Parameters.AddWithValue("@PUNBR", PUNBRs); da.SelectCommand.Parameters.AddWithValue("@DATE1", beginningDates); da.SelectCommand.Parameters.AddWithValue("@DATE2", endDates); da.SelectCommand.CommandTimeout = 360; } catch (SqlException ex) { //something went wrong throw ex; } finally { if (conn.State == ConnectionState.Open) conn.Close(); } 

Here is my stored procedure. It executes dynamic SQL text.

 ALTER PROCEDURE [dbo].[RemoveReport] ( @ReportType NVARCHAR(20), @GRNBR VARCHAR(4000), @PUNBR VARCHAR(4000), @DATE1 DATETIME, @DATE2 DATETIME ) AS DECLARE @SQLTEXT VARCHAR(4000) BEGIN SET @SQLTEXT = 'DELETE FROM TestingTable WHERE Report=''' +@ReportType +''' AND PUNBR IN (''' +@PUNBR +''') AND [Group] IN (''' +@GRNBR +''') AND StartedAt BETWEEN '''+CONVERT(VARCHAR(10),@DATE1,121)+''' AND '''+CONVERT(VARCHAR(10),@DATE2,121)+'''' PRINT @SQLTEXT <---I'll print this out to show you what exactly it is executing. EXECUTE (@SQLTEXT) END 

Here's what PRINT @SQLTEXT :

 DELETE FROM MonthlyReportSchedule WHERE Report='PostClaim' AND PUNBR IN ('100','100','100') AND [Group] IN ('925','926','927') AND StartedAt BETWEEN '2012-04-01' AND '2012-04-30' 

When I am in SQL Server to run this query, it works fine. But why does this not work when executed from C # code. Any help?

Answer 1

Avoid concatenating parameters to your sql, use a parameterized query,

Try it...

Just noticed that you have comma delimited lists in options .....

 ALTER PROCEDURE [dbo].[RemoveReport] @ReportType NVARCHAR(20), @GRNBR VARCHAR(4000), @PUNBR VARCHAR(4000), @DATE1 DATETIME, @DATE2 DATETIME AS BEGIN SET NOCOUNT ON; DECLARE @SQLTEXT NVARCHAR(MAX); Declare @GRNBR_xml xml,@PUNBR_xml xml; SET @GRNBR_xml = N'<root><r>' + replace(@GRNBR, ',','</r><r>') + '</r></root>'; SET @PUNBR_xml = N'<root><r>' + replace(@PUNBR, ',','</r><r>') + '</r></root>'; SET @SQLTEXT = N'DELETE FROM TestingTable WHERE Report = @ReportType AND PUNBR IN (select r.value(''.'',''varchar(max)'') as item from @PUNBR_xml.nodes(''//root/r'') as records(r)) AND [Group] IN (select r.value(''.'',''varchar(max)'') as item from @GRNBR_xml.nodes(''//root/r'') as records(r)) AND StartedAt BETWEEN @DATE1 AND @DATE2' EXECUTE sp_executesql @SQLTEXT ,N'@ReportType NVARCHAR(20) , @GRNBR_xml xml, @PUNBR_xml xml,@DATE1 DATETIME,@DATE2 DATETIME' ,@ReportType ,@GRNBR_xml ,@PUNBR_xml ,@DATE1 ,@DATE2 END 

Note

Make sure you split the comma separated list as 925,926,927 , and not as '925','926','927'

Answer 2

Try adding this line to execute

 da.SelectCommand.ExecuteNonQuery(); 

This will result in a call to your stored procedure.

luck