sp_ExecuteSQL problem (HELP) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

sp_ExecuteSQL problem (HELP)

Author

Topic

LopakaB
Starting Member

22 Posts

Posted - 2014-06-25 : 13:18:32

Any help would be greatly appreciated…

With dynamic query, I try and run it using the sp_executesql and returns nothing… I run it using EXEC and it returns 4 records, I modify the @sql to add the “declare” statements and run it with sp_executesql, it returns 4 records. I run it the way it should with sp_executesql, it returns 0 records…

I added the try/catch in the hopes it was throwing an error, but runs successfully but with 0 records…

I have had several database developers, co-workers, look at it, they were puzzled…

Here is the code in question, @SQL is dynamic built query:
-- build the query to runs in exec and sp_executesql by adding the declares...
SET @SQLParams = N'';
SET @SQLParams = @SQLParams + N'DECLARE @Start_Date DATETIME = ''' + CONVERT(VARCHAR(10), @Start_Date, 101) + ''';' + CHAR(13);
SET @SQLParams = @SQLParams + N'DECLARE @End_Date DATETIME = ''' + CONVERT(VARCHAR(10), @End_Date, 101) + ''';' + CHAR(13);
SET @SQLParams = @SQLParams + N'DECLARE @Site_Code INT = ' + CONVERT(VARCHAR(5), @Site_Code) + ';' + CHAR(13);
SET @SQLParams = @SQLParams + N'DECLARE @BillStatus VARCHAR(3) = ' + @BillStatus + ';' + CHAR(13);
SET @SQLParams = @SQLParams + CHAR(13) + @SQL;

EXEC(@SQLParams);

EXECUTE sp_executesql @SQLParams;

-- Put the query back to the way it should run
SET @SQLParams = N' @Start_Date datetime,
@End_Date datetime,
@Site_Code int,
@BillStatus varchar(8000)';

DECLARE @i int
BEGIN TRY
EXECUTE @i = sp_executesql @SQL, @SQLParams, @Start_Date, @End_Date, @Site_Code, @BillStatus;
SELECT CompletedI = @i;
END TRY
BEGIN CATCH
SELECT FailedI = @i;
SELECT ERROR_NUMBER(), ERROR_MESSAGE();
IF @i <> 0
PRINT 'Error: ' + CONVERT(nvarchar(10), ERROR_NUMBER()) + ' ' + ERROR_MESSAGE();
END CATCH

Thank you in advance… ?

Lopaka

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-25 : 13:37:12

The declares should not be part of the dynamic query. Please show us @SQL.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-06-26 : 12:18:27

You didn't provide @SQL, so it's a little hard to debug fully.

But you have an error in this line:
SET @SQLParams = @SQLParams + N'DECLARE @BillStatus VARCHAR(3) = ' + @BillStatus + ';' + CHAR(13);

It should be:
SET @SQLParams = @SQLParams + N'DECLARE @BillStatus VARCHAR(3) = ''' + @BillStatus + ''';' + CHAR(13);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources