asked
How to assign Stored Procedure having dynamic query to a variable in SQL Server
**actual Oracle Code** function USF_CONFIG(p_config in varchar2, p_valType in varchar) return varchar AS config_value varchar(500); BEGIN EXECUTE IMMEDIATE ' select '||p_valType||'_VALUE from TBL_CONFIGS where CONFIG=''' || p_config || '''' into config_value; RETURN config_value; END; **Migrated SQL Server code** CREATE PROCEDURE usp_GetConfig (@p_Config varchar(4000), @p_ValType varchar(500)) AS BEGIN declare @v_SqlStr Nvarchar(100) set @v_SqlStr = ' select '+@p_ValType+'_Value from tbl_Configs where Config =''' +@p_Config+ '''' execute sp_executesql @v_SqlStr end **Testing code** declare @op varchar(100)='' exec usp_GetConfig 10,'na' 1]print @op 2]select @op 3]if @op='kka' print 'hi' else print 'bye' **here 1 2 and 3 not giving expected output.. is there any other way to write code**
You are not setting the value of @siteId to anything in the executed sql: set @v_SqlStr = ' select @siteId ='+@p_ValType+'me from dev_common.dbo.ok where id=''' +@p_Config+ ''''
There's no need to change that part - you are simply not setting the variable to anything
Yes you can capture the output of a procedure into a table - using the insert into...exec... See the first example here :
https://msdn.microsoft.com/en-us/library/ms174335.aspx#OtherTables
14 People are following this question.
