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

Question

Executing dynamic SQL as follows in Stored Procedure   DECLARE  sqlCommand nvarchar 1000  DECLARE  city varchar 75  SET  city    London  SET  sqlCommand    SELECT COUNT    FROM customers WHERE City    city  EXECUTE sp executesql  sqlCommand  N  city nvarchar 75     city    city   How do I use the count    column value as return value in the SP

User · Answer

vMYQUERY     SELECT COUNT    FROM ALL OBJECTS WHERE OWNER   UPPER   MFI IDBI2LIVE    AND OBJECT TYPE     TABLE        AND OBJECT NAME          vTBL CLIENT MASTER              PRINT STRING VMYQUERY       EXECUTE IMMEDIATE  vMYQUERY INTO VCOUNTTEMP

User · Answer

DECLARE  sqlCommand nvarchar 1000  DECLARE  city varchar 75  declare  counts int 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  counts OUTPUT select  counts as Counts

User · Answer

You ve probably tried this  but are your specifications such that you can do this   DECLARE  city varchar 75  DECLARE  count INT SET  city    London  SELECT  count   COUNT    FROM customers WHERE City    city

User · Answer

dynamic version      ALTER PROCEDURE  dbo   ReseedTableIdentityCol   p table varchar max  -- RETURNS int     AS     BEGIN         -- Declare the return variable here        DECLARE  sqlCommand nvarchar 1000         DECLARE  maxVal INT        set  sqlCommand    SELECT  maxVal   ISNULL max ID  0  1 from    p table        EXECUTE sp executesql  sqlCommand  N  maxVal int OUTPUT   maxVal  maxVal OUTPUT        DBCC CHECKIDENT  p table  RESEED   maxVal      END   exec dbo ReseedTableIdentityCol  p table  Junk

User · Answer

this could be a solution   declare  step2cmd nvarchar 200  DECLARE  rcount NUMERIC 18 0     set  step2cmd    select count    from uat ap ztscm protocollo  --   nometab EXECUTE  rcount sp executesql  step2cmd select  rcount

User · Answer

DECLARE  sqlCommand nvarchar 1000  DECLARE  city varchar 75  DECLARE  cnt int SET  city    London  SET  sqlCommand    SELECT  cnt COUNT    FROM customers WHERE City    city  EXECUTE sp executesql  sqlCommand  N  city nvarchar 75     city    city RETURN  cnt

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql

Examples related to dynamic-sql