Stored Procedure if Exist with dynamically table

0
votes

I have a problem here, I'm searching in stackoverflow but still not find the best way

i have a stored procedure (SP) like this

DECLARE @table NVARCHAR(max), @SQLQuery NVARCHAR(max) SET @table = @meta+'_prize4winner'

SET @SQLQuery = 'if exists (Select * from [dbo].' + @table + ' where idCampaignLog =''' + convert(nvarchar, @ID) +''') exec InsertC2CWinner''' + convert(nvarchar, @meta) +''','''+ convert(nvarchar, @ID)+''',null else select ''you lose ''as winStatus'

execute SP_EXECUTESQL @SQLQuery

need help how and where to set the output if I want the output if exist 'YOU WIN' else 'You Lose' thx

1
sql-serverstored-procedures
Please refer this.stackoverflow.com/questions/3105600/… add the next line after your exixts condition and make use of output parameter to get return data. – Recursive

1 Answers

0
votes

The general syntax is like this 

DECLARE @retval int   
DECLARE @sSQL nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

DECLARE @tablename nvarchar(50)  
SELECT @tablename = N'products'  

SELECT @sSQL = N'SELECT @retvalOUT = MAX(ID) FROM ' + @tablename;  
SET @ParmDefinition = N'@retvalOUT int OUTPUT';

EXEC sp_executesql @sSQL, @ParmDefinition, @retvalOUT=@retval OUTPUT;

SELECT @retval;