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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-04-08 : 11:47:00
|
Brandon McLamb writes "CREATE FUNCTION fn_Test()
AS
BEGIN
DECLARE @rc INT
DECLARE @SQL nvarchar(1000)
SELECT @SQL = 'SELECT @rc = 1'
EXEC sp_executesql @SQL
RETURN (@rc)
END
If you can't EXEC in a function, how do you build dynamic SQL within a FUNCTION and have it return something. Specifically declare a function to return a table and build sql based on parameters you pass to that function? I've seen this everywhere, but no answer." |
|
|
treehuggerpal
Starting Member
1 Post |
Posted - 2003-10-14 : 09:21:38
|
| I needed to interrogate the results of some dynamic SQL also, and ran into issues. I found this solution worked for me: 1. Create a temporary table in the proc2. run the dynamic SQL putting the results in the temporary table3. then interrogate the results of the temporary table, like this:DECLARE @v_select varchar(255) ,@v_from varchar(255) ,@v_where varchar(255) ,@count intCREATE TABLE #datasrc_t (data_src_cde int)SELECT @v_select = " INSERT INTO #datasrc_t SELECT DISTINCT data_src_cde) " SELECT @v_from = " FROM someother_table_t " SELECT @v_where = " WHERE acct_id = " + @acct_id + " AND lin_cde IN ('" + @lin_cde + "') AND fcst_num IN (" + @fcst_nums + ") AND row_stts_flag = 'A'"EXECUTE( @v_select + @v_from + @v_where) SELECT @count = COUNT(*) FROM #datasrc_tIF @count > 1 /* DO SOMETHING HERE */Else /* DO SOMETHING ELSE HERE */ |
 |
|
|
|
|
|
The one everyone wants to know