SQL Server 2005: Subquery in EXEC?

Question

EXEC [dbo].[pr_cfgAddFact] 
@SettingName = 'TransferBatch', 
@RoleFK = SELECT TOP 1 rolepk FROM cfgRole WHERE cfgRole.Name = 'SuperAdmin'

Why is the SQL complaint about the SELECT clause here? I am trying to run proc with a subquery receiving data.

Answer 1

try the following:

DECLARE @RoleFK_value    {datatype here}
SELECT TOP 1 @RoleFK_value=rolepk FROM cfgRole WHERE cfgRole.Name = 'SuperAdmin'

EXEC [dbo].[pr_cfgAddFact] 
@SettingName = 'TransferBatch', 
@RoleFK = @RoleFK_value

you cannot have a query in EXECUTE procedures; parameters for stored procedures do not allow this. first select the value in the local variable first, and then pass that local variable to the stored procedure.

stored procedure parameters can only be values, @variables or a keyword DEFAULT, which means that queries and expressions are not allowed.

EXECUTE (Transact-SQL)

Execute a stored procedure or function
[ { EXEC | EXECUTE } ]
    { 
      [ @return_status = ]
      { module_name [ ;number ] | @module_name_var } 
        [ [ @parameter = ] { value 
                           | @variable [ OUTPUT ] 
                           | [ DEFAULT ] 
                           }
        ]
      [ ,...n ]
      [ WITH RECOMPILE ]
    }
[;]

Answer 2

Try

SELECT TOP 1 @RoleFK=rolepk FROM cfgRole WHERE cfgRole.Name