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 |
|
Hani
Starting Member
3 Posts |
 Posted - 2008-12-07 : 06:28:56
|
| Please I am having an error Incorrect syntax near 'INSERT @t SELECT id,result FROM @name'When I am writing :CREATE Function [dbo].[testString](@name NVARCHAR(20))RETURNS @t TABLE(id int ,res int)ASBEGINEXEC('INSERT @t SELECT id,result FROM '+ @name)RETURN ENDSo please how can i make this function works to be such dynamic because i have more complex issues than this, but i wrote this simple one as an explanation.Thank you very much..... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-07 : 07:46:06
|
| you cant have dynamic sql inside a function. can you let us know what you're trying to achieve here? |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2008-12-07 : 09:26:43
|
| Not only can't you do that in a function, you definitely don't want to do that at all. Passing table names as parameters for SELECT (or other) operations is poor practice. |
|
|
|
Hani
Starting Member
3 Posts |
Posted - 2008-12-08 : 08:08:46
|
| Thanks for your interest but is there a solution to do that?I think using stored Procedure(sp) will be good but the problem is that i when want to return a table from SqlServer sp to use it in C# It will return all the select query with it, suppose that i have 10 select statement and i am intested in the last one (to use in C#)then when i execute ExecuteReader() I have to use ReadNext() 9 times to reach the last result which is so bad.Thanks...........Hani |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-08 : 08:31:33
|
quote:
Originally posted by Hani
Thanks for your interest but is there a solution to do that?I think using stored Procedure(sp) will be good but the problem is that i when want to return a table from SqlServer sp to use it in C# It will return all the select query with it, suppose that i have 10 select statement and i am intested in the last one (to use in C#)then when i execute ExecuteReader() I have to use ReadNext() 9 times to reach the last result which is so bad.Thanks...........Hani
cant you write a table valued user defined function which gives you the table as resultset. you can use either IF ELSE statements to return varied number of columns as per your requirement or use single SELECT and vary number of column by using CASE expression (to return values when you want and else return NULL)http://www.sqlteam.com/article/user-defined-functions |
|
|
|
|
|
|
dynamic execution in a function