This is sort of an on going thing I've been working out (with help from EE). I have a SP that needs to work with a dynamic table name which is passed in as a parameter. I've got this working in most cases but am stuck trying to return a variable from the executed string to the rest of the stored procedure. Previously I have managed to define a global cursor which is created in the EXEC statement but available to the rest of the SP.
How do you specify a global @variable?
I have code some thing like:
CREATE PROCEDURE someName
@Date as dateTime,
@needToGetThisOut nvarchar(50) output
SET @sSQL = '
SET @needToGetThisOut = (SELECT someColumn FROM [' + @tablePrefix + 'Bookings]
WHERE Date = @Date'
EXEC sp_executesql @sSQL, N'@Date datetime', @Date = @Date
With this code the @needToGetThisOut is null, it does hold the value it was assigned in the sSQL string.
I know that to use the @Date var dynamically (which is required in the real SP) in the sSQL string, it needed to be defined and passed in on the execute line.
I guess I need away to pass out the variable from the execute string, or make it global? How do I do either of these?
Thanks in Advance,