asked on

Set sql variable via select

I am trying to execute a store procedure. When I place the results of the query in it will run, however trying to set the variable in the execute statement I get .
Incorrect syntax near the keyword 'From'.

I'm not sure what the issue is please help.

select PRJG_RecordID From ProjectGroup where PRJG_ProjectID ='4360'

EXEC [dbo].[Ideal_ProjectCostReport]
@ParentProjectRecordID = PRJG_RecordID From ProjectGroup where PRJG_ProjectID ='4360'

ASKER CERTIFIED SOLUTION

Leo Torres

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Deepak Chauhan

You can not execute stored procedure like this. You have to declare one more variable accepting the value.
like @PRJG_ProjectID

EXEC [dbo].[Ideal_ProjectCostReport]
@ParentProjectRecordID = <PRJG_RecordID> any int value,
@PRJG_ProjectID ='4360'

if table ProjectGroup is using in this SP.

can you share your stored procedure script we can suggest you.

Jim Horn

A scalar function can be set to a single value..

SELECT @something = dbo.fn_whatever('Sally')

Open in new window

If by 'Executing a SP' you mean pass a variable as a parameter, then the above is correct.
If by 'Executing a SP' you mean execute the SP and set the resulting single value into a parameter, then SP's return a set, so you'll have to handle it as such..

CREATE TABLE #tmp (no int) 

INSERT INTO #tmp (no) 
exec sp_whatever ('Sally')  -- I'm a little fuzzy on what your needs are here

SELECT @no = no FROM #tmp WHERE-- ??

Open in new window