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 would SQL complain about the SELECT clause here? I am trying to run the proc with the sub query getting the data.

Answer 1

Try

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

Answer 2

try this:

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 can not have the query within the EXECUTE of the procedure, parameters to stored procedures do not allow this. first, select the value into a local variable first and then pass that local variable in to the stored procedure.

stored procedure parameters may only be values, @variables or the keyword DEFAULT, that means that queries and expressions ore not permitted.

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 ]
    }
[;]