May 29, 2002 at 1:05 am
Hi.I want to Execute a String with Exec and want to get result of that Sql String, e.g. Exec 'Select Emp_Id from tbl_employee where emp_Id=1'
How to get the return value of String in Variable. I know one method.Put it in Temporary table which i dun want to use
May 29, 2002 at 5:05 am
Unfortunately you cannot set a variable equal directly to the output of the execute process and sp_executesql will only return 0 if success. You will have to use a temp table.
Ex.
DECLARE @val VARCHAR(10)
CREATE TABLE #tt (
val varchar(10)
)
INSERT INTO #tt (val) EXEC ('Select Emp_Id from tbl_employee where emp_Id=1')
SELECT @val = val FROM #tt
PRINT @val
DROP TABLE #tt
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 29, 2002 at 6:04 am
It is possible to do output params if you use sp_executesql, the syntax is a little weird but doable. Try this thread:
Andy
May 29, 2002 at 7:27 am
Wow, thanks Andy, something new for me to play with.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 29, 2002 at 7:29 am
It's damn handy and much cleaner than the global temp thing (which I've used a few times!).
Andy
May 30, 2002 at 5:34 am
Hi
does sp_executeSql returns value, My query like this EXEC/Sp_executesql 'select emp_id form employee where emp_id=1'
I need That Emp_Id without using Temporary table
May 30, 2002 at 5:37 am
Thanks a ton for reply, but i dun wanna use temp table. i knew it already
May 30, 2002 at 8:03 am
In case you missed it or maybe I missunderstood your last statements, but the link Andy gave shows sp_ExecuteSql with the syntax doing the work. FOr you case something like this should do the job.
Ex.
DECLARE @sql Nvarchar(500),
@Var NVarchar(100),
@rc int
SET @Var = '@RC int output'
SET @sql = 'SELECT @rc = EmpID FROM Employee WHERE EmpID = 1'
EXEC sp_ExecuteSql @sql , @Var , @rc OutPut
PRINT @rc
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply