Dynamic SQL

[apollo11]

I am trying to run the following dynamic SQL:

declare @ebodbname varchar(255), @SQL varchar(1000)
Select @ebodbname = host_db_name from host_db_name
set @SQL = "declare @EBOID integer" + CHAR(13)
set @SQL = @SQL + "select @EBOID = EBOID from " + @ebodbname + "..EBO where ebodatabase = db_name()" + CHAR(13)
set @SQL = @SQL + "select @EBOID"
execute(@SQL)
I have two databases. The name of the 2nd database is stored in a table in the first database.
What the SQL does is retrieve the name of a database from the table and then query a table that exists in that database using the variable retrieved.
My problem is I cannot read the variable outside of the dynamic SQL. I need to be able to access the variable @EBOID OUTSIDE of the DSQL. So that I can use it in a normal query outside.
For e.g:
insert into table values(@EBOID, ....etc.

Thanks in advance

 

[Adita]

I know that it is not documented by Microsoft but you can declare in among the parameters used in the dynamic SQL Output parameters as well as input.
You just have to declare a parameter which will store the value returned by the dynamic SQl

@Myparam = @MyparamLocal OUTPUT

You will be able to use the @MyParamLocal farther in the Stored Proc with te values returned by the dynamic SQL

Good luck

 

[apollo11]

I changed my sql to the one below:

declare @ebodbname varchar(255), @SQL varchar(1000)
Select @ebodbname = host_db_name from host_db_name
set @SQL = "declare @EBOID integer,@EBOIDout integer" + CHAR(13)
set @SQL = @SQL + "select @EBOID = EBOID from " + @ebodbname + "..EBO where ebodatabase = db_name()" + CHAR(13)
set @SQL = @SQL + "@EBOID = @EBOIDout OUTPUT"
set @SQL = @SQL + "select @EBOID"
execute(@SQL)


Notice the BOLD text, but still gives me the Incorrect syntax near '@EBOID'. error

Have I done the step correctly?
Thanks

 

[Adita]

No

It should be like this
I did not attach the SQL string because i don't know what exactly you are trying to do.
This is just the call for the string with the respective parametyers

declare @EBOID int

sp_executesql @SQLstring,@params='@EBOID int',@EBOID=@EBOID OUTPUT