?
Solved

Change Function to dynamic SQL

Posted on 2008-10-30
3
Medium Priority
?
966 Views
Last Modified: 2010-05-19
I need to replace the section where it reads FROM MYDATABASE... to use the @tablename parameter being passed into the function. @tablename is a Linked Server name so I'm imagining I have to somehow use dynamic sql, but can't figure out the syntax

ALTER FUNCTION [dbo].[SF_PickLists](@tablename varchar(50))
RETURNS @PickListTable TABLE
      (ObjectName nvarchar(48),
       FieldName nvarchar(48),
       PickListValue nvarchar(256),
       PickListLabel nvarchar(256))
AS
BEGIN
      INSERT @PickListTable
      SELECT ObjectName, FieldName, PickListValue, PickListLabel
      FROM MYDATABASE...sys_sfpicklists
      RETURN
END
0
Comment
Question by:advlgx
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22844032
can't do dynamic sql in a function
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22844040
You can't do that inside of a function.

You will get:

Invalid use of side-effecting or time-dependent operator in 'INSERT EXEC' within a function.


create function up_IDontWork (@b char(1))
returns @t table (a char(1))
as
begin
insert into @t
exec('select ''' + @b + '''')
 
return
end
 
go

Open in new window

0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 22844063
I realize that you want to be able to "SELECT * FROM dbo.Your_function(@Param)" but you can't.  You would have to do it inside of a stored procedure and populate your temp table as such:



create procedure [dbo].[SF_PickLists](@tablename varchar(50))
 
AS
      exec('INSERT #PickListTable SELECT ObjectName, FieldName, PickListValue, PickListLabel
      FROM MYDATABASE...sys_sfpicklists')
END
 
go
 
create table #PickListTable 
      (ObjectName nvarchar(48), 
       FieldName nvarchar(48),
       PickListValue nvarchar(256),
       PickListLabel nvarchar(256))
 
exec [dbo].[SF_PickLists]
 
select * from #PickListTable 
 
drop table #PickListTable 

Open in new window

0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question