Hi,

I would like to know how to view the results of a dynamic sql query in
Management Studio.  This is from my stored procedure:

select @sql = 'SELECT IR.IDInspectionResult
FROM dbo.MeasurePosition MPos
INNER JOIN dbo.MeasurePoint MP ON MP.FKMeasurePosition =
MPos.IDMeasurePosition
INNER JOIN dbo.InspectionResult IR (INDEX = IX_InspectionResult) ON
IR.FKMeasurePoint =MP.IDMeasurePoint
WHERE     MPos.IDMeasurePosition = '

if @IDInspectionResult is not null
    select @SQL = @SQL + '(SELECT     IDMeasurePosition
    FROM       dbo.InspectionResult IR
    INNER JOIN v_MeasurePointInfo_not MP ON MP.IDMeasurePoint =
IR.FKMeasurePoint
                WHERE      IDInspectionResult = @xIDInspectionResult '
else
    select @SQL = @SQL + '@xIDMeasurePosition '
set @paramlist = '@xIDInspectionResult INT,  @xIDMeasurePosition INT'
exec sp_executesql @SQL, @paramlist, @IDInspectionResult,
@IDMeasurePosition

I only recieve the return value for the stored procedure.  However,
when I remove the dynamic sql, I can view the results of the query in
Management Studio. This works:

SELECT     IDMeasurePosition
FROM       dbo.InspectionResult IR
INNER JOIN v_MeasurePointInfo_not MP ON MP.IDMeasurePoint =
IR.FKMeasurePoint WHERE      IDInspectionResult =3045

Thanks, Amanda

amanda (amandadeb***@mweb.co.za) writes:
There's nothing special to that. You see it just like any other result set.

Show quoteHide quoteI don't see any statement to execute the SQL string? Could you post the
complete stored procedure?

The stored proc is quite long.  Though I'll send a simpler one with the
same problem:

ALTER PROCEDURE [dbo].[sp_GetMeasurePoints]
@IDInspection integer, @Unit integer, @IDWF integer, @ViewPerOutage
char(1)
AS
declare @sql nvarchar(1000)
Select @sql = 'Select * from v_MeasurePointInfo V where V.IDUnit = ' +
str(@Unit)

if @ViewPerOutage = 'Y'
    SELECT @SQL = @SQL + ' and V.IDInspection = ' + str(@IDInspection)
if @IDWF <> 0
    SELECT @SQL = @SQL + ' and V.IDWhereFailure = ' + str(@IDWF)
SELECT @SQL = @SQL + ' order by V.IDMeasurePosition'
EXEC sp_executesp @sql

The results of EXEC sp_executesp @SQL is not seen in Management Studio.

Thanks
Amanda

amanda (amandadeb***@mweb.co.za) writes:
Show quoteHide quoteThere is no sp_executesp in SQL Server. There is an sp_executesql. Maybe
that is what you meant, but then you posted an example that you obviously
not tested. Or you are simply not paying attention to the error message.

I'm sorry, but it is very difficult to assist when the input I get is
incomplete or inaccurate.

Here is a very simple example that you can test to see that getting
data back from sp_executesql is nothing special at all:

   CREATE PROCEDURE lantliv @objtype varchar(2) AS
   DECLARE @sql nvarchar(1200)
   SELECT @sql = 'SELECT COUNT(*) FROM sys.objects WHERE type = @type'
   EXEC sp_executesql @sql, N'@type varchar(2)', @objtype
   go
   EXEC lantliv 'U'

Sorry, I was trying a few things whilst posting my previous message.
Made a typo there, should be sp_executesql.

I found the cause of the problem in the last if statement.  Shouldn't
be '<> 0', but 'is not null'.  This caused a null value to be inserted
in the sql, which could therefore not execute.  Matter of fact, you
can't print it either.  PRINT @Variablename just prints an empty line
and it's up to you to find the mistake.

Thanks for your help anyhow.

Amanda

Anytime you might have a NULL value in a variable, you need to be careful if
concatenating that to a larger string if you mean to concatenate an empty
space, e.g.

DECLARE @foo VARCHAR(32), @bar VARCHAR(32);
SET @foo = 'abc';
SELECT @foo + '...' + @bar;
SELECT @foo + '...' + COALESCE(@bar, '');
SET @bar = 'xyz';
SELECT @foo + '...' + @bar;







Show quoteHide quote

SQL 2005 Management Studio can't edit text rows with > 4000 charac
backup again
SQL 2205, Profiler and Deadlocks
Utility for Table Schemas
Table Changes