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

score 136 · Accepted Answer · edited Dec 09 '21 at 14:11

136

You can use output parameters with sp_executesql.

DECLARE @dbName nvarchar(128) = 'myDb'
DECLARE @siteId int 
DECLARE @SQL nvarchar(max) = N'SELECT TOP 1 @outputFromExec = Id FROM ' + quotename(@dbName) + N'..myTbl'
exec sp_executesql @SQL, N'@outputFromExec int out', @siteId out
select @siteId

edited Dec 09 '21 at 14:11

Brondahl

7,402
5
45
74

answered Aug 29 '11 at 10:55

Mikael Eriksson

136,425
22
210
281

2

+1 This is the nicer way. I saw a parameterised database name and dismissed sp_executesql... – gbn Aug 29 '11 at 11:00
Cool, I found what I was looking for ! – Aamol May 29 '18 at 04:26
5

I am not able to use @siteId afterwards... it returns NULL.The execute line works fine... – Paulo Henrique Jun 08 '18 at 03:49
3

@PauloHenrique Don't forget the final `OUT` in the `EXEC` line or it will fail silently. – Denziloe Feb 19 '19 at 11:10

score 28 · Answer 2 · answered Aug 29 '11 at 10:51

28

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

answered Aug 29 '11 at 10:51

gbn

422,506
82
585
676

4

"TOP without an ORDER BY is meaningless" - Meaningless is probably a bit strong here, maybe all I want is 1 record and I don't care which, i.e. what restaurant should I eat at tonight? – RyanfaeScotland Aug 13 '14 at 09:28
@RyanfaeScotland: maybe, but then there is no `ANY()` fucntion in SQL that gives arbitray rows. Otherwise I'd want my query to behave the same way every time instead of relying on some assumed order. – gbn Aug 13 '14 at 12:52
Your note just simply isn't true in most practical scenarios. A ***maintained***, clustered index on the table will ensure a consistent, predictable order to the table results. – Matt Mar 09 '17 at 02:43
@Matt: do you maintain the clustered index after *every* update or insert that would result in fragmentation? – gbn Mar 09 '17 at 11:59
2

Time-dependent variables or proxies ensure that your ordering get implicitly maintained chronologically. Your point can be a valid one, but the larger point of saying there is no "natural, implied or intrinsic ordering" is just not true, which was my point. There is predictability in the physical storage location of a record, which translates to a time-dependent predictability in the `TOP` ordering. Of course, this does not presuppose it's a useful order for a given query's purpose, but it is predictable. – Matt Mar 19 '17 at 14:58

score 2 · Answer 3 · answered Oct 02 '20 at 10:16

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

score 0 · Answer 4 · edited Mar 28 '13 at 21:25

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

score -1 · Answer 5 · edited Feb 02 '21 at 13:33

-1

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

edited Feb 02 '21 at 13:33

Braiam

1
11
47
78

answered Feb 02 '21 at 09:02

anuj sharma

1
1

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

5 Answers5

Linked