Return EXECUTE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Return EXECUTE

Author

Topic

shahab_ksh
Starting Member

3 Posts

Posted - 2007-09-07 : 05:26:51

How i can return SELECT SCOPE_IDENTITY() as fld_id to @par_out Int OUTPUT

at this code

ALTER PROCEDURE dbo.Form_fld_insert

(
@par_id Int,
@par_out Int OUTPUT
)

AS

EXECUTE ('
Declare @temp Nvarchar(100)
Select @temp=''-''
INSERT INTO tbl_form_data_'+@par_id+' (fld_temp) VALUES (@temp)
(SELECT SCOPE_IDENTITY() as fld_id)
RETURN
') @par_out OUTPUT

RETURN

Kristen
Test

22859 Posts

Posted - 2007-09-07 : 05:51:17

Use sp_Execute_SQL instead of EXECUTE, with an output parameter

Kristen

Kristen
Test

22859 Posts

Posted - 2007-09-07 : 05:55:18

e.g.


ALTER PROCEDURE dbo.Form_fld_insert
(
    @par_id Int,
    @par_out Int OUTPUT
)
AS 

DECLARE @strSQL nvarchar(4000)

SELECT @strSQL = 
'Declare @temp Nvarchar(100)
Select @temp=''-''
INSERT INTO tbl_form_data_'+@par_id+' (fld_temp) VALUES (@temp)
SELECT @par_out = SCOPE_IDENTITY()
--RETURN
'

EXEC sp_executesql @strSQL,
     N'@par_out Int',
     @par_out Int OUTPUT

RETURN

Kristen

Kristen
Test

22859 Posts

Posted - 2007-09-07 : 05:56:45

Actually:

INSERT INTO tbl_form_data_'+@par_id+' (fld_temp) VALUES (@temp)

will give you a syntax error, change that to

INSERT INTO tbl_form_data_' + CONVERT(varchar(20), @par_id) + ' (fld_temp) VALUES (@temp)

Kristen

shahab_ksh
Starting Member

3 Posts

Posted - 2007-09-07 : 07:33:14

Tanx But I have this error

"Incorrect syntax near 'int' "

Kristen
Test

22859 Posts

Posted - 2007-09-07 : 14:47:44

Sorry, this is the incorrect bit:


EXEC sp_executesql @strSQL,
     N'@par_out Int',
     @par_out Int OUTPUT

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources