How to set value to variable using execute in t-sql

Question

DECLARE  dbName nvarchar 128     myDb  DECLARE  siteId int  exec   SELECT TOP 1  siteId   Id FROM      dbName      myTbl     select  siteId   When I run the script above I get the following error  Msg 137  Level 15  State 1  Line 1 Must declare the scalar variable   siteId     1 row s  affected    Why and how to fix it   Thank you

User · Answer

The dynamic SQL is a different scope to the outer, calling SQL: so @siteid is not recognised

You'll have to use a temp table/table variable outside of the dynamic SQL:

DECLARE @dbName nvarchar(128) = 'myDb'
DECLARE @siteId TABLE (siteid int)

INSERT @siteId
exec ('SELECT TOP 1 Id FROM ' + @dbName + '..myTbl')  

select * FROM @siteId

Note: TOP without an ORDER BY is meaningless. There is no natural, implied or intrinsic ordering to a table. Any order is only guaranteed by the outermost ORDER BY

User · Answer

You can try like below DECLARE  sqlCommand NVARCHAR 4000  DECLARE  ID INT DECLARE  Name NVARCHAR 100  SET  ID   4 SET  sqlCommand    SELECT  Name    Name  FROM  AdventureWorks2014   HumanResources   Department  WHERE DepartmentID    ID  EXEC sp executesql  sqlCommand  N  ID INT   Name NVARCHAR 100  OUTPUT    ID    ID   Name    Name OUTPUT SELECT  Name ReturnedName  Source   blog sqlauthority com

User · Answer

SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO --                                               -- Author       Andrew Foster -- Create date  28 Mar 2013 -- Description  Allows the dynamic pull of any column value up to 255 chars from regUsers table --                                               ALTER PROCEDURE dbo PullTableColumn        columnName varchar 255        id int   AS BEGIN     -- SET NOCOUNT ON added to prevent extra result sets from     -- interfering with SELECT statements      SET NOCOUNT ON       DECLARE  columnVal TABLE  columnVal nvarchar 255         DECLARE  sql nvarchar max       SET  sql    SELECT      columnName     FROM regUsers WHERE id     CAST  id AS varchar 10        INSERT  columnVal EXEC sp executesql  sql       SELECT   FROM  columnVal  END GO

User · Answer

You can use output parameters with sp executesql   DECLARE  dbName nvarchar 128     myDb  DECLARE  siteId int  DECLARE  SQL nvarchar max    N SELECT TOP 1  siteId   Id FROM     quotename  dbName    N   myTbl  exec sp executesql  SQL  N  siteId int out    siteId out select  siteId

User · Answer

A slight change in the execute query will solve the problem  DECLARE  dbName nvarchar 128     myDb  DECLARE  siteId int  exec   SELECT TOP 1      siteId       Id FROM      dbName      myTbl     select  siteId

[sql-server] How to set value to variable using 'execute' in t-sql?

Examples related to sql-server

Examples related to tsql