Return value from exec sql

Question

I want get the value from Exec  sql  and assign to  Rowcount int   Here is my query    SET  RowCount    select count                         FROM dbo Comm Services                     WHERE CompanyId     cast  CompanyId as char    and    condition

User · Answer

declare  nReturn int   0 EXEC  nReturn   Stored Procedures

User · Answer

that s my procedure   CREATE PROC sp count      CompanyId sysname       codition sysname     AS     SET NOCOUNT ON     CREATE TABLE  ctr       NumRows int        DECLARE  intCount int             vcSQL varchar 255       SELECT     vcSQL     INSERT  ctr FROM dbo Comm Services                         WHERE CompanyId      CompanyId   and    condition         EXEC        vcSQL       IF   ERROR   0     BEGIN          SELECT     intCount   NumRows          FROM  ctr           DROP TABLE  ctr          RETURN  intCount     END     ELSE     BEGIN          DROP TABLE  ctr          RETURN -1     END     GO

User · Answer

If i understand you correctly   i probably don t    SELECT  RowCount   COUNT                       FROM dbo Comm Services                    WHERE CompanyId       CAST  CompanyId AS CHAR                         AND      condition

User · Answer

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

User · Answer

Was playing with this today    I beleive you can also use   ROWCOUNT  like this   DECLARE  SQL VARCHAR 50  DECLARE  Rowcount INT SET  SQL    SELECT 1 UNION SELECT 2  EXEC  SQL  SET  Rowcount     ROWCOUNT SELECT  Rowcount   Then replace the  SELECT 1 UNION SELECT 2  with your actual select without the count  I d suggest just putting 1 in your select  like this   SELECT 1 FROM dbo Comm Services WHERE             as opposed to putting SELECT     Hope that helps

[sql] Return value from exec(@sql)

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql