Return Value from Exec(@Sql)

Return value from exec(@sql)

On the one hand you could use sp_executesql:

exec sp_executesql N'select @rowcount=count(*) from anytable', 
                    N'@rowcount int output', @rowcount output;

On the other hand you could use a temporary table:

declare @result table ([rowcount] int);
insert into @result ([rowcount])
exec (N'select count(*) from anytable');
declare @rowcount int = (select top (1) [rowcount] from @result);

How to get the return value from EXEC(Query) inside stored procedure

If you update table inside SP, you can try the following query.It is better not to use sp in procedure name.

ALTER PROCEDURE USP_Invert 
    (@Id INT, @column VARCHAR(100))
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Query VARCHAR(1000)

    SET @Query ='UPDATE TEST5 SET ' + @column +' = CASE WHEN (SELECT Top 1 ' + @column + ' FROM TEST5 where Id = ' + Convert(VARCHAR(10),@Id) + ') = 0 THEN 1
                                                    WHEN (SELECT Top 1 ' + @column + ' FROM TEST5 where Id = ' + Convert(VARCHAR(10),@Id) + ') = 1 THEN 0 END '

    EXEC(@Query); -- here I need to get the column value
END

Thanks

How to get return value from EXEC sp_executesql

you just need put the variable that you want receive de return value before sp_executesql

try tis code and let me know if works

DECLARE @TableName VARCHAR(MAX)
DECLARE @TableNameToDrop VARCHAR(MAX) --NEW
DECLARE @TD_QUERY NVARCHAR(MAX)
DECLARE @OpenQry NVARCHAR(MAX) -- Dont forget to declare this variable
declare @sqldrop nvarchar(max)

DECLARE CUR_QRY CURSOR FOR
SELECT TABLENAME FROM dbo.tbl_table

OPEN CUR_QRY
FETCH NEXT FROM CUR_QRY INTO @TableName

WHILE @@FETCH_STATUS = 0 
BEGIN

SET  @OpenQry = 'SELECT * FROM OPENQUERY(linkedserver,''SELECT TABLENAME FROM DBC.TABLES WHERE TABLEKIND=''''T'''' AND DATABASENAME=''''dbname'''' AND TABLENAME=''''' + @TableName + ''''''')'

EXEC TableNameToDrop  = sp_executesql @OpenQry

/*
IF EXISTS (SELECT @OpenQry)
AND EXISTS (SELECT TableName FROM dbo.table WHERE TableName=@TableName )
*/
--maybe you dont need check if the table name existis, jut try this

IF EXISTS (SELECT TableName FROM dbo.table WHERE TableName=@TableNameToDrop )
BEGIN
    set @sqldrop = 'drop table '+  @TableNameToDrop 
    EXEC sp_executesql @sqldrop
END

FETCH NEXT FROM CUR_QRY INTO @TableName
END
CLOSE CUR_QRY
DEALLOCATE CUR_QRY

Regards

How to assign an exec result to a sql variable?

I always use the return value to pass back error status. If you need to pass back one value I'd use an output parameter.

sample stored procedure, with an OUTPUT parameter:

CREATE PROCEDURE YourStoredProcedure 
(
    @Param1    int
   ,@Param2    varchar(5)
   ,@Param3    datetime OUTPUT
)
AS
IF ISNULL(@Param1,0)>5
BEGIN
    SET @Param3=GETDATE()
END
ELSE
BEGIN
    SET @Param3='1/1/2010'
END
RETURN 0
GO

call to the stored procedure, with an OUTPUT parameter:

DECLARE @OutputParameter  datetime
       ,@ReturnValue      int

EXEC @ReturnValue=YourStoredProcedure 1,null, @OutputParameter OUTPUT
PRINT @ReturnValue
PRINT CONVERT(char(23),@OutputParameter ,121)

OUTPUT:

0
2010-01-01 00:00:00.000

How to get the particular column value of the EXEC result in SQL?

If procedure is returning table type output then you must create a similar schema of that output table, you may create #temp table for that.
Stores output of procedure in table then retrieve your data from temp table.

Example is like this:

CREATE TABLE #TestTable ([col1] NVARCHAR(100), [col2] NVARCHAR(100));
INSERT INTO #TestTable
EXEC [dbo].[GET_ALL_RECORDS] @ProjectId   --- expecting @ProjectId is the variable in which you want to get the result
declare @output nvarchar(100)
set @output = (select top 1 col from #TestTable order by col )

Or if you can modify procedure then this will be your solution

  create proc myproc
  as 
  begin
      ----- do your code here
      declare @output nvarchar(100)
      set @output = (select top 1 col from #TestTable order by col )  ---- assuming #TestTable is the table you get as output.
      return @output 
  end
  go
  declare @op nvarchar(100)
  exec @op = myproc

Cannot get output variable from stored procedure when procedure written in dynamic sql

This removes SQL injection concerns by properly escaping the database name and also dynamically executing against that database instead of embedding the database name in the command. Also, you don't need @RwNum to be dynamic.

CREATE PROCEDURE dbo.test
  @Db      sysname,
  @RwNum   int,
  @AnlyNum int output
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @exec nvarchar(max) = QUOTENAME(@Db) + N'.sys.sp_executesql',
          @sql nvarchar(max) = N'SELECT @AnlyNum = AnlyId 
            From dbo.Test order by AnlyId desc 
            OFFSET @RwNum rows fetch next 1 rows only);';

  EXEC @exec @sql, N'@AnlyNum int output, @RwNum int',
    @AnlyNum output, @RwNum;
END

Return variable using OPENQUERY

As already mentioned, in this case you should use a procedure with an output parameter instead of a function. If you want to fully execute the query on the Oracle linked server side and return some value after that, I would suggest using dynamic as follows:

Create Or Alter Procedure dbo.get_cwpSeq
    @COIL nvarchar(100),
    @cwp_seq Int Output
As

Declare @QueryText nVarChar(max)

Select @QueryText = 'Select @cwp_seq=cwp_seq 
                     From Openquery(DEV, 
                     ''Select cwp_seq 
                       From apps.custom_wip_pieces 
                       Where lot_number= ''''' + @COIL + ''''''') As Ora'; 
Execute sp_executesql @QueryText, N'@COIL nvarchar(100), @cwp_seq Int Output', @COIL = @COIL, @cwp_seq = @cwp_seq Output

As far as I understand in your case:
Linked server is "DEV", Owner of the table is "apps".

Getting result of dynamic SQL into a variable for sql-server

DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @count INT
DECLARE @city VARCHAR(75)
SET @city = 'New York'

SET @sqlCommand = 'SELECT @cnt=COUNT(*) FROM customers WHERE City = @city'
EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75), @cnt int OUTPUT', @city = @city, @cnt = @count OUTPUT

SELECT @count

---

Related Topics