SQL Server (dis)Functions
-
So, all I wanted to do was create a Function (in-line/scalar or table - doesn't really matter to me) that could build a t-sql statement from some parameters of the function, and exec[ute] the created string. I have tried many things, but without the ability to call a user defined SP, I seem to be stuck. Any ideas??
thanks
-this was supposed to be the easy part of the script I was writing, and then I found out how limited I was with the allowed commands within a SQL Server function.
Edited by - joshcsmith13 on 03/26/2003 11:52:05 PM
-
Why do you absolutely want to do this with a function? Is it not possible to do it within a stored procedure?
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
I use dynamic SQL to create code based on parameters. For example:
create procedure testsql @colname varchar(25), @tablename varchar(25) as
declare @sqlstring nvarchar(200)
set @sqlstring = N'select ' + @colname +N'
from ' + @tablename
/* print @sqlstring */
exec sp_executesql @sqlstring
go
If you call this procedure with a column and a parameter it will create and execute some SQL to retrieve a column from a table (both of which you supply)
The nvarchar variable limits you to 4000 bytes but you can create any TSQL statement you like including temporary stored procedures:
set @sqlstring = N'create procedure #proc_temp @var1 varchar(25) as
/* Rest of temporary stored procedure */
.......
'
exec sp_executesql @sqlstring
Once you have created the temp sp you can then call it with exec #proc_temp @var1.
Just be aware that the temporary stored procedure will be created every time you call the sp and SQL Server will have to create an execution plan each time rather than reusing existing plans i.e. an increased overhead. Also, the temporary sp is unique to the procedure call so if there are two calls to the procedure then each one will create their own version of the temporary procedure which is handy as it prevents them interferring with each other.
Jeremy
-
well, I hate to say it, but I wanted a function that could be used within a recursive Stored Procedure - returning a different value or table each iteration.
If you can tell me how to use a stored procedure as a field or table in a select statement, then I'll use a SP.thanks
-
You can run the stored procedure before running the 'real' select statement, storing the returned value/table in a variable/temp table/table variable, then use this in the 'real' statement.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
frustrated again.... So, I set out to store my SP results in a table variable (because of all the woderful things that BOL says about them), and have discovered that I can't use an "exec SPx" as the source for an "insert @TableVariable".
so now to figure out if I can use a table variable as a parameter.. ? -
Sorry, you can't pass a table variable as a parameter to a stored procedure.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
bummer. AND, I can't use an "Insert @TableVariable EXEC SP..." Why can't anything work the way I'd like it to? So, I guess I'll make the temp table(s) needed to store the results in my SP.
If you can tell me how to use a stored procedure as a field or table in a select statement, then I'll use a SP.
so now to figure out if I can use a table variable as a parameter.. ?Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply