Databases
StackOverflow2013
Postsdbo
POHow to grab the value of the output parameter in execute sp_executesql?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to grab the value of the output parameter in execute sp_executesql?
    primarykey
    data
    text
    <p>Please forgive newbie's ignorance!</p> <p>How do I grab the value of the output parameter in execute sp_executesql? </p> <p>I can see the output but cannot get to it:</p> <pre><code> DECLARE @LastActivity nvarchar(100) DECLARE @LastActivityDate datetime DECLARE @sql nvarchar(MAX) DECLARE @RowsToProcess int DECLARE @CurrentRow int DECLARE @SelectCol1 nvarchar(100) DECLARE @SelectCol2 nvarchar(100) DECLARE @SelectCol3 nvarchar(100) DECLARE @LastDate TABLE (RowID int not null primary key identity(1,1), col4 nvarchar(MAX), col5 sql_variant) DECLARE @table1 TABLE (RowID int not null primary key identity(1,1), col1 nvarchar(100),col2 nvarchar(100),col3 nvarchar(100)) INSERT into @table1 (col1,col2,col3)(SELECT t.name AS col1, c.name AS col2, m.Field1 as col3 FROM sys.columns c INNER JOIN sys.tables t ON c.object_id = t.object_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id INNER JOIN dbo.MERGE_TABLES m ON m.Table_Name=t.name WHERE c.name LIKE '%[_]DATE%' and m.[Enabled]='Y') SET @RowsToProcess=@@ROWCOUNT SET @CurrentRow=0 WHILE @CurrentRow&lt;@RowsToProcess BEGIN SET @CurrentRow=@CurrentRow+1 SELECT @SelectCol1=col1,@SelectCol2=col2,@SelectCol3=col3 FROM @table1 WHERE RowID=@CurrentRow SET @sql='SELECT ' + '[dbo].[ConvertToDatetime](MAX(' + @SelectCol2 + '))' + ' FROM ' + @SelectCol1 + ' Where ' + @SelectCol3 + ' = ' + '''0722607QZ''' Declare @params as nvarchar(MAX) Set @params = '@date sql_variant output' Declare @date as sql_variant; execute sp_executesql @sql ,@params ,@date output Select @date INSERT into @LastDate VALUES (@sql, @date) end select col4,col5 from @LastDate </code></pre> <p>select col4,col5 from @LastDate gives me the SQL script in clo4 but col5 is empty! I need to store the @date as I still need to get the Max(@date)</p> <p>Thanx a million.</p> <blockquote> <p>SET @sql='set @date =('SELECT ' + '[dbo].[ConvertToDatetime](MAX(' + @SelectCol2 + '))' + ' FROM ' + @SelectCol1 + ' Where ' + @SelectCol3 + ' = ' + '''0722607QZ''' ) '</p> </blockquote> <p>the above sql gives me error: Incorrect syntax near '.' </p> <blockquote> <p>SET @sql='set @date =(SELECT [dbo].[ConvertToDatetime](MAX( + @SelectCol2 + ))<br> FROM @SelectCol1 Where @SelectCol3 ''=0722607QZ'' ) '</p> </blockquote> <p>The above sql gives the error: Must declare the scalar variable "@SelectCol2"</p> <pre><code>SET @sql='SELECT ' + @date + '=convert(nvarchar(100), [dbo].[ConvertToDatetime](MAX(' + @SelectCol2 + ')))' + ' FROM ' + @SelectCol1 + ' Where ' + @SelectCol3 + ' = ' + '''0722607QZ''' </code></pre> <blockquote> <p>the above produces the error : Implicit conversion from data type sql_variant to nvarchar is not allowed. Use the CONVERT function to run this query.</p> </blockquote> <pre><code>SET @sql='SELECT ' + @date + '=convert(nvarchar(MAX),(MAX(' + @SelectCol2 + '))' + ' FROM ' + @SelectCol1 + ' Where ' + @SelectCol3 + ' = ' + '''0722607QZ''' </code></pre> <blockquote> <p>the above produces no error but all output is NULL, no values.</p> </blockquote>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USFet
    1. USFet
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to grab the value of the output parameter in execute sp_executesql?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to grab the value of the output parameter in execute sp_executesql?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload